Re: Creating constraint dynamically

On Mon, Aug 22, 2022 at 12:59 PM sivapostgres(at)yahoo(dot)com <
sivapostgres(at)yahoo(dot)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