Re: Creating constraint dynamically

On Mon, 22 Aug 2022 at 09:29, sivapostgres(at)yahoo(dot)com
<sivapostgres(at)yahoo(dot)com> wrote:
> We populate constraint string dynamically and add it to the table with alter table command. It gets added, but without the 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 constraint as 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.