Обсуждение: Re: Creating constraint dynamically
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;
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
BKR Sivaprakash
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
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))
On Mon, 22 Aug 2022 at 09:29, sivapostgres@yahoo.com <sivapostgres@yahoo.com> wrote: > We populate constraint string dynamically and add it to the table with alter table command. It gets added, but withoutthe required brackets. Brackets are NOT required in your example. You are doing a classic form, OR of ANDs, sometime called sum of products. And has greater precedence than OR ( amongst other things because this form of conditions is used a lot ), as it has been pointed, so brackets ( round brackets are usually called parentheses abbreviated to parens, in programming brackets is normaly nor used for those ) are not necessary. What the system is doing is parsing and normalizing your condition ( note how it changes bpchar to text and add types in some places ) and reconstructing the condition, without brackets because they are not required. > Since there are AND and OR conditions, without brackets the whole conditions becomes useless. Before stating these things, test, try to insert a condition violating row to see if the condition is really useless, it is very rare to find a bug ( changing the semantics of the condition would be one ) like these. > How to create a constraint like the above one, with braces in tact ? Or any other way that we can implement a check constraintas above? The condition IS correctly implemented, but nothing guarantees you the text returned by pgadmin will be the exact same condition you sent. In fact, AFAIK, nothing guarantees you can recover a condition set on a column. It is transformed to an equivalent. I'm not sure how it is exactly done, but it probably also does whitespace normalization and constant folding. Francisco Olarte.
Hello,Using PG 11.4We 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_stockADD 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 ]We have only one bracket, in the final updated one.ALTER TABLE public.tx_barcode_stockADD 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;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
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
BKR Sivaprakash
On Monday, 22 August, 2022 at 03:54:30 pm IST, jian he <jian.universality@gmail.com> wrote:
Hello,Using PG 11.4We 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_stockADD 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 ]We have only one bracket, in the final updated one.ALTER TABLE public.tx_barcode_stockADD 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;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