Обсуждение: can't create table on new db/schema/user

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

On Fri, Aug 28, 2020 at 12:32:02PM +0200, psql-contact@citecs.de wrote:
>     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?

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

On Fri, Aug 28, 2020 at 12:56:12PM +0200, hubert depesz lubaczewski wrote:
> 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.

How would I do that?

I ran 
    create schema     s_chris    authorization "chris";

How do I specify the database?

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

there seems to be no such parameter.

On Fri, Aug 28, 2020 at 01:28:35PM +0200, psql-contact@citecs.de wrote:
> On Fri, Aug 28, 2020 at 12:56:12PM +0200, hubert depesz lubaczewski wrote:
> > 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.
> 
> How would I do that?

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

    \connect db_chris
    create schema     s_chris    authorization "chris";
    \connect postgres

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

On Fri, Aug 28, 2020 at 01:47:56PM +0200, Didier Gasser-Morlay wrote:
> You must be connected to the database in psql first
> 
> \c db_chris
> Then create schema ....

Thanx :-)