CODEWITHSUNDEEP
sqlpostgresqlschema
nskalis
nskalis

Reputation: 2382

postgres: schema does not exist when creating a table

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#

Upvotes: 2

Views: 12342

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51629

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

Upvotes: 2

Related Questions