Arkadiy Bodriy Arkadiy Bodriy - 5 months ago 17
SQL Question

Postgresql: syntax error at or near “.”

Why do I get syntax error at or near “.” ?

CREATE TABLE myschema.products (
product_no SERIAL PRIMARY KEY,
date date,
group_number INTEGER,
CHECK (myschema.products >1 AND myschema.products <1001)
);

CREATE TABLE myschema.orders (
order_id SERIAL PRIMARY KEY,
name varchar,
schedule integer[][]
);

CREATE TABLE myschema.tabletime (
id SERIAL,
products INTEGER,
orders INTEGER,
CONSTRAINT pkey PRIMARY KEY (id),
CONSTRAINT integrity CHECK (products IS NOT NULL
AND orders IS NOT NULL),
CONSTRAINT products_exists FOREIGN KEY(products)
REFERENCES myschema.products(product_no),
CONSTRAINT orders_exists FOREIGN KEY(orders)
REFERENCES myschema.orders(order_id)
);

Answer

The error is at the line:

CHECK (myschema.products >1 AND myschema.products <1001)

you cannot use the name of the relation inside a check, only the name of the columns. The manual says:

Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row.