Thread
-
can't create table on new db/schema/user
chris <psql-contact@citecs.de> — 2020-08-28T10:32:02Z
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 -
Re: can't create table on new db/schema/user
hubert depesz lubaczewski <depesz@depesz.com> — 2020-08-28T10:56:12Z
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 -
Re: can't create table on new db/schema/user
chris <psql-contact@citecs.de> — 2020-08-28T11:28:35Z
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.
-
SOLVED: can't create table on new db/schema/user
pgsql-novice@lists.postgresql.org — 2020-08-28T11:47:40Z
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@...
-
Re: can't create table on new db/schema/user
Didier Gasser-Morlay <didiergm@gmail.com> — 2020-08-28T11:47:56Z
You must be connected to the database in psql first \c db_chris Then create schema .... Le ven. 28 août 2020 à 13:28, <psql-contact@citecs.de> a écrit : > 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. > > > > >
-
[SOLVED] Re: can't create table on new db/schema/user
chris <psql-contact@citecs.de> — 2020-08-28T12:02:53Z
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 :-)