postgres: schema does not exist when creating a table

Question

Could you please advise why when trying to create a table like this:

CREATE TABLE IF NOT EXISTS  doc.bgp_communities (

    i             SERIAL PRIMARY KEY,
    comm_name     TEXT NOT NULL,
    comm_value    TEXT NOT NULL,
    label         TEXT NOT NULL,
    used_as       TEXT NOT NULL,
        modified_on   TIMESTAMPZ DEFAULT NOW(),

    UNIQUE        ( comm_value ),
    CHECK         ( (comm_value SIMILAR TO '%:%') OR (comm_value SIMILAR TO '%:%:%') ),
    CHECK         ( used_as IN ('bgp-customer','destination','dtv-peer','free-peer','generic','infrastructure','origin','private-peer','public-peer','traffic-eng','voip-peer') )

) ;

Before that I have created the schemas as follows:

ip_spotlight-# CREATE SCHEMA doc
ip_spotlight-# CREATE SCHEMA net
ip_spotlight-# ALTER DATABASE ip_spotlight SET search_path TO doc,net

I am getting the following error message:

ERROR:  schema "doc" does not exist

Could you please advise what I am doing wrong ?

PS: my user "app" is configured like:

app          | Superuser, No inheritance, Create role, Create DB, Replication | {}

EDIT

app@[local]:5432 ip_spotlight# CREATE SCHEMA doc ;
CREATE SCHEMA
Time: 1.492 ms
app@[local]:5432* ip_spotlight# CREATE SCHEMA net ;
CREATE SCHEMA
Time: 0.641 ms
app@[local]:5432* ip_spotlight# ALTER DATABASE ip_spotlight SET search_path TO doc,net ;
ALTER DATABASE
Time: 1.036 ms

# CREATE TABLE IF NOT EXISTS  doc.bgp_communities (
ip_spotlight(# i             SERIAL PRIMARY KEY,
ip_spotlight(# comm_name     TEXT NOT NULL,
ip_spotlight(# comm_value    TEXT NOT NULL,
ip_spotlight(# label         TEXT NOT NULL,
ip_spotlight(# used_as       TEXT NOT NULL,
ip_spotlight(# modified_on   TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
ip_spotlight(# UNIQUE        ( comm_value ),
ip_spotlight(# CHECK         ( (comm_value SIMILAR TO '%:%') OR (comm_value SIMILAR TO '%:%:%') ),
ip_spotlight(# CHECK         ( used_as IN ('bgp-customer','destination','dtv-peer','free-peer','generic','infrastructure','origin','private-peer','public-peer','traffic-eng','voip-peer') )
ip_spotlight(# ) ;
ERROR:  schema "doc" does not exist
Time: 0.686 ms
app@[local]:5432! ip_spotlight# \dn
ERROR:  current transaction is aborted, commands ignored until end of transaction block
app@[local]:5432! ip_spotlight#

ip_spotlight-# CREATE SCHEMA doc ip_spotlight-# CREATE SCHEMA net ip_spotlight-# ALTER DATABASE ip_spotlight SET search_path TO doc,net need ';' at each command end, to be ip_spotlight-# CREATE SCHEMA doc; ip_spotlight-# CREATE SCHEMA net; ip_spotlight-# ALTER DATABASE ip_spotlight SET search_path TO doc,net; — Vao Tsun
– Vao Tsun, Commented May 15, 2017 at 12:32
Can't reproduce your error using superuser. BTW alter database ... set ...; does not affects current session. You need to reconnect to get it or execute set ...; just after alter database ... set ...; — Abelisto
– Abelisto, Commented May 15, 2017 at 13:07
Do you run the CREATE SCHEMA statement in a transaction? If yes, then you are expected to COMMIT that transaction, as in PostgreSQL, the DDL statements (also) take part in the transaction. (The space between your statements may imply that you are actually running 2 separate transactions, is it so?). — pozs
– pozs, Commented May 15, 2017 at 13:11

Vao Tsun · Accepted Answer · 2017-05-15 12:33:59Z

2

you missed semi colon at the end of commands, so you probably did not create any schema:

ip_spotlight-# CREATE SCHEMA doc
ip_spotlight-# CREATE SCHEMA net
ip_spotlight-# ALTER DATABASE ip_spotlight SET search_path TO doc,net

should look:

t=# CREATE SCHEMA doc;
CREATE SCHEMA
t=# CREATE SCHEMA net;
CREATE SCHEMA
t=# ALTER DATABASE ip_spotlight SET search_path TO doc,net;
ALTER DATABASE

answered May 15, 2017 at 12:33

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nskalis Over a year ago

Thanks @VaoTsun for looking into it. That is an improvement for sure. But still am getting the same error. Do you have a guess what could be wrong ?

nskalis Over a year ago

see "EDIT" above

nskalis Over a year ago

had to do it as 'postgres' user :)

Collectives™ on Stack Overflow

postgres: schema does not exist when creating a table

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related