public inbox for [email protected]
help / color / mirror / Atom feedcan't create table on new db/schema/user
6+ messages / 4 participants
[nested] [flat]
* can't create table on new db/schema/user
@ 2020-08-28 10:32 [email protected]
2020-08-28 10:56 ` Re: can't create table on new db/schema/user hubert depesz lubaczewski <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: [email protected] @ 2020-08-28 10:32 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can't create table on new db/schema/user
2020-08-28 10:32 can't create table on new db/schema/user [email protected]
@ 2020-08-28 10:56 ` hubert depesz lubaczewski <[email protected]>
2020-08-28 11:28 ` Re: can't create table on new db/schema/user [email protected]
0 siblings, 1 reply; 6+ messages in thread
From: hubert depesz lubaczewski @ 2020-08-28 10:56 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
On Fri, Aug 28, 2020 at 12:32:02PM +0200, [email protected] 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can't create table on new db/schema/user
2020-08-28 10:32 can't create table on new db/schema/user [email protected]
2020-08-28 10:56 ` Re: can't create table on new db/schema/user hubert depesz lubaczewski <[email protected]>
@ 2020-08-28 11:28 ` [email protected]
2020-08-28 11:47 ` SOLVED: can't create table on new db/schema/user [email protected]
2020-08-28 11:47 ` Re: can't create table on new db/schema/user Didier Gasser-Morlay <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: [email protected] @ 2020-08-28 11:28 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* SOLVED: can't create table on new db/schema/user
2020-08-28 10:32 can't create table on new db/schema/user [email protected]
2020-08-28 10:56 ` Re: can't create table on new db/schema/user hubert depesz lubaczewski <[email protected]>
2020-08-28 11:28 ` Re: can't create table on new db/schema/user [email protected]
@ 2020-08-28 11:47 ` [email protected]
1 sibling, 0 replies; 6+ messages in thread
From: [email protected] @ 2020-08-28 11:47 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
On Fri, Aug 28, 2020 at 01:28:35PM +0200, [email protected] 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@...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can't create table on new db/schema/user
2020-08-28 10:32 can't create table on new db/schema/user [email protected]
2020-08-28 10:56 ` Re: can't create table on new db/schema/user hubert depesz lubaczewski <[email protected]>
2020-08-28 11:28 ` Re: can't create table on new db/schema/user [email protected]
@ 2020-08-28 11:47 ` Didier Gasser-Morlay <[email protected]>
2020-08-28 12:02 ` [SOLVED] Re: can't create table on new db/schema/user [email protected]
1 sibling, 1 reply; 6+ messages in thread
From: Didier Gasser-Morlay @ 2020-08-28 11:47 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
You must be connected to the database in psql first
\c db_chris
Then create schema ....
Le ven. 28 août 2020 à 13:28, <[email protected]> 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.
>
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* [SOLVED] Re: can't create table on new db/schema/user
2020-08-28 10:32 can't create table on new db/schema/user [email protected]
2020-08-28 10:56 ` Re: can't create table on new db/schema/user hubert depesz lubaczewski <[email protected]>
2020-08-28 11:28 ` Re: can't create table on new db/schema/user [email protected]
2020-08-28 11:47 ` Re: can't create table on new db/schema/user Didier Gasser-Morlay <[email protected]>
@ 2020-08-28 12:02 ` [email protected]
0 siblings, 0 replies; 6+ messages in thread
From: [email protected] @ 2020-08-28 12:02 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
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 :-)
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-08-28 12:02 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-28 10:32 can't create table on new db/schema/user [email protected]
2020-08-28 10:56 ` hubert depesz lubaczewski <[email protected]>
2020-08-28 11:28 ` [email protected]
2020-08-28 11:47 ` SOLVED: can't create table on new db/schema/user [email protected]
2020-08-28 11:47 ` Didier Gasser-Morlay <[email protected]>
2020-08-28 12:02 ` [email protected]
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox