postgresql create table default schema

Question

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.

Answer 1

you create the testschema before creating the testdb, so it is not created in the testdb database. So, when testuser is logged in, it cannot set its current schema to testschema. You can check it using the following statement:

SELECT current_schema();

so, you just need to create testschema after you connect the testdb:

\c testdb;
CREATE SCHEMA testschema AUTHORIZATION testuser;