We populate constraint string dynamically and add it to the table with alter table command. It gets added, but without the required brackets.
What we build is
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.
How to create a constraint like the above one, with braces in tact ? Or any other way that we can implement a check constraint as above?
Happiness Always BKR Sivaprakash
I guess it's because pgadmin uses pg_get_constraintdef ( constraintoid [, prettyboolean ] ) pretty is true.
if you use pg_get_constraintdef ( constraintoid,false ) you will see more braces.
--
I recommend David Deutsch's <<The Beginning of Infinity>>