Re: Creating constraint dynamically

On 8/22/22 13:09, Wim Bertels wrote:
> sivapostgres@yahoo.com 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))