uxdb=# CREATE TABLE products (
product_no integer NOT NULL,
product_date timestamptz,
price numeric CHECK (price > 0) NOT NULL,
discounted_price numeric NOT NULL,
constraint my_check_of_table CHECK (discounted_price > 0 AND price > discounted_price)
);
CREATE TABLE
uxdb=# \d products
Table “public.products”
Column | Type | Collation | Nullable | Default
------------------±-------------------------±----------±---------±--------
product_no | integer | | not null |
product_date | timestamp with time zone | | |
price | numeric | | not null |
discounted_price | numeric | | not null |
Check constraints:
“my_check_of_table” CHECK (discounted_price > 0::numeric AND price > discounted_price)
“products_price_check” CHECK (price > 0::numeric)
uxdb=# insert into products values (1,now(),5,NULL);
ERROR: null value in column “discounted_price” violates not-null constraint
DETAIL: Failing row contains (1, 2020-06-03 11:34:54.845962+08, 5, null).
uxdb=# insert into products values (1,now(),5,6);
ERROR: new row for relation “products” violates check constraint “my_check_of_table”
DETAIL: Failing row contains (1, 2020-06-03 11:35:26.334107+08, 5, 6).
uxdb=#
