Subject: can't create table on new db/schema/user

From: [email-masked]
Date: Fri, 28 Aug 2020 12:32:02 +0200

What I have:

	debian 10 
	postgresql 11.7-0+deb10u1

What I did:

	sudo -s
    mkdir           /var/lib/postgresql/11/ts_chris
    chown postgres: /var/lib/postgresql/11/ts_chris
    chmod 700       /var/lib/postgresql/11/ts_chris

    su - postgres -c psql
    create user "chris" with login password '***' connection limit -1;
    create tablespace "ts_chris" owner    chris location '/var/lib/postgresql/11/ts_chris';
    alter  tablespace ts_chris   owner to chris;
    create database   db_chris   owner =  automicuser template = template0 encoding = "UTF-8" tablespace = ts_chris lc_collate = "C" lc_ctype = "C" connection limit = -1 ;
    create schema     s_chris    authorization "chris";
    alter  role       chris      in database db_chris set search_path to s_chris;
    grant usage  on schema   s_chris  to chris;
    grant create on schema   s_chris  to chris;

What I tried:

	psql -h lxc05 db_chris chris

	db_chris=> create table t1 (i int);
	ERROR:  no schema has been selected to create in
	LINE 1: create table t1 (i int);
	                     ^
Debugging attempts:

	postgres=# \dn+
	                               List of schemas
	  Name   |    Owner    |     Access privileges      |      Description       
	---------+-------------+----------------------------+------------------------
	 public  | postgres    | postgres=UC/postgres      +| standard public schema
	         |             | =UC/postgres               | 
	 s_chris | chris       | chris=UC/chris             | 
	(3 rows)
	
	postgres=# \dg+
	                                           List of roles
	  Role name  |                         Attributes                         | Member of | Description 
	-------------+------------------------------------------------------------+-----------+-------------
	 chris       |                                                            | {}        | 
	 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 

	db_chris=>     create table t1 (i int);
	ERROR:  no schema has been selected to create in
	LINE 1: create table t1 (i int);
	                     ^
	db_chris=> \dn+
	                          List of schemas
	  Name  |  Owner   |  Access privileges   |      Description       
	--------+----------+----------------------+------------------------
	 public | postgres | postgres=UC/postgres+| standard public schema
	        |          | =UC/postgres         | 
	(1 row)
	
	db_chris=> \dg+
	                                           List of roles
	  Role name  |                         Attributes                         | Member of | Description 
	-------------+------------------------------------------------------------+-----------+-------------
	 chris       |                                                            | {}        | 
	 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 

	db_chris=> show search_path;
	 search_path 
	-------------
	 s_chris
	(1 row)

So, what did I miss?

Kind regards, Chris

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Fri, 28 Aug 2020 12:56:12 +0200

> 	  Name   |    Owner    |     Access privileges      |      Description       

Schema s_chris is in database "postgres", but you try to make the table
in "db_chris".

So you have to make the schema in db_chris database.

Best regards,

depesz

---

From: [email-masked]
Date: Fri, 28 Aug 2020 13:28:35 +0200

> So you have to make the schema in db_chris database.

How would I do that?

I ran 

How do I specify the database?

According to 
	https://www.postgresql.org/docs/11/sql-createschema.html

there seems to be no such parameter.

---

From: [email-masked]
Date: Fri, 28 Aug 2020 13:47:40 +0200

> > So you have to make the schema in db_chris database.

You have to actually _connnect_ to the database the schema is created for:

	\connect db_chris
	\connect postgres

Yeah, that's why it's called pgsql-novice@...

---

From: Didier Gasser-Morlay <[email-masked]>
Date: Fri, 28 Aug 2020 13:47:56 +0200

You must be connected to the database in psql first

\c db_chris
Then create schema ....

---

From: [email-masked]
Date: Fri, 28 Aug 2020 14:02:53 +0200

> You must be connected to the database in psql first

Thanx :-)