From: | JITEN KUMAR SHAH <jitenshah78(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Creating constraint dynamically |
Date: | 2022-08-22 08:23:35 |
Message-ID: | 7af57a5d-459d-0533-2541-7a181fc9c274@GMAIL.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 8/22/22 13:09, Wim Bertels wrote:
> sivapostgres(at)yahoo(dot)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))
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2022-08-22 08:45:08 | Re: Creating constraint dynamically |
Previous Message | Wim Bertels | 2022-08-22 07:39:14 | Re: Creating constraint dynamically |