On Mon, Aug 22, 2022 at 12:59 PM sivapostg...@yahoo.com < sivapostg...@yahoo.com> wrote:
> Hello, > Using PG 11.4 > > 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 ( *constraint* oid [, *pretty* boolean ] ) pretty is true. if you use pg_get_constraintdef ( *constraint* oid *,false* ) you will see more braces. -- I recommend David Deutsch's <<The Beginning of Infinity>> Jian
