I'm new to postgresql and don't understand schemas and defaults are managed.
Now I'm creating my schema/user/tablespace with this scripts:
CREATE ROLE testuser LOGIN PASSWORD 'password';
CREATE TABLESPACE testtablespace OWNER testuser LOCATION '/pgdata/testdbtablespace';
CREATE SCHEMA testschema AUTHORIZATION testuser;
ALTER SCHEMA testschema OWNER TO testuser;
CREATE DATABASE testdb WITH ENCODING 'UNICODE' OWNER testuser;
ALTER DATABASE testdb SET search_path TO testschema;
ALTER ROLE testuser SET search_path TO testschema;
GRANT ALL ON DATABASE testdb TO testuser;
ALTER ROLE testuser SET default_tablespace = testtablespace;
ALTER DATABASE testdb SET default_tablespace = testtablespace;
Then I login to db using command psql -U testuser testdb
and execute command create table test (test varchar(10));
. With this I'm getting error ERROR: no schema has been selected to create in
When I set search_path to testschema, public then table is created but in schema public.
I'm expecting that with this setup I don't need to provide schema name. Is it possible to setup user/db/schema to automatically create table in default schema? In oracle table is created under user which is creating it when schema is not provided.