On 8/22/22 13:09, Wim Bertels wrote:
sivapostgres@xxxxxxxxx schreef op ma 22-08-2022 om 07:29 [+0000]:
ALTER TABLE public.tx_barcode_stock
ADD CONSTRAINT "tx_barcode_stock_CK1" CHECK
( (branchcode = '1'::bpchar and barcodeitem = 'Y'::bpchar and
closingstock >= 0::numeric) Or (branchcode = '1' and barcodeitem =
'N'::bpchar and closingstock >= 0::numeric ) Or (branchcode =
'2'::bpchar and barcodeitem = 'Y'::bpchar and closingstock >=
0::numeric) Or (branchcode = '2' and barcodeitem = 'N'::bpchar and
closingstock >= 0::numeric ) ) NOT VALID;
After creation, when we check what we find is [ in PgAdmin ]
ALTER TABLE public.tx_barcode_stock
ADD CONSTRAINT "tx_barcode_stock_CK1" CHECK (branchcode::bpchar =
'1'::bpchar AND barcodeitem = 'Y'::bpchar AND closingstock >=
0::numeric OR branchcode::text = '1'::text AND barcodeitem =
'N'::bpchar AND closingstock >= 0::numeric OR branchcode::bpchar =
'2'::bpchar AND barcodeitem = 'Y'::bpchar AND closingstock >=
0::numeric OR branchcode::text = '2'::text AND barcodeitem =
'N'::bpchar AND closingstock >= 0::numeric)
NOT VALID;
We have only one bracket, in the final updated one.
Since there are AND and OR conditions, without brackets the whole
conditions becomes useless.
did you see
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-PRECEDENCE
?
mvg,
Wim
You can use plpgsql function
alter table tale_name add constraint xyz check (func(table_name))
