Search Postgresql Archives

Re: Creating constraint dynamically

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Mon, Aug 22, 2022 at 12:59 PM sivapostgres@xxxxxxxxx <sivapostgres@xxxxxxxxx> 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux