Hello,
Sorry for delay... I had to go on leave..
Checked, it's our mistake here. The issue is not what I described, but in the
replicated database where the constraint was not updated. Correcting the
constraint there solved this issue.
Sorry for not checking properly before coming here.
Happiness Always
BKR Sivaprakash
On Monday, 22 August, 2022 at 03:54:30 pm IST, jian he
wrote:
On Mon, Aug 22, 2022 at 12:59 PM 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 isALTER 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 <>
Jian