BUG #13606: CHECK clause in CREATE TABLE command

The following bug has been logged on the website:

Bug reference: 13606
Logged by: Piergiorgio
Email address: p(dot)buongiovanni(at)net-international(dot)com
PostgreSQL version: 9.2.4
Operating system: CENTOS 6.2
Description:

As the CHECK clause specifies an expression producing a Boolean result, the
_Dummy1_CK2 constraint is valid when I write this CREATE TABLE command:

CREATE TABLE draft.Dummy1
(
cFlag char (1) NOT NULL DEFAULT 'N',
cField varchar DEFAULT NULL,
CONSTRAINT _Dummy1_CK1 CHECK (cFlag IN ('Y','N')),
CONSTRAINT _Dummy1_CK2 CHECK ( cFlag = 'N' OR ( cFlag = 'Y' AND cField IS
NOT NULL ) )
);

If now you inspect the SQL code of the table Dummy1 with pgAdmin, you see
that the above SQL code has been changed in the following one:

CREATE TABLE draft.dummy1
(
cflag character(1) NOT NULL DEFAULT 'N'::bpchar,
cfield character varying,
CONSTRAINT _dummy1_ck1 CHECK (cflag = ANY (ARRAY['Y'::bpchar,
'N'::bpchar])),
CONSTRAINT _dummy1_ck2 CHECK (cflag = 'N'::bpchar OR cflag = 'Y'::bpchar
AND cfield IS NOT NULL)
)
WITH (
OIDS=FALSE
);
ALTER TABLE draft.dummy1
OWNER TO netdw_owner;

As you can see brackets has been removed from the definition of the
constraint _Dummy1_CK2. Clearly this doesn't work as I want.
If I want to solve the problem I must write the CREATE TABLE command as
below:

CREATE TABLE draft.Dummy2
(
cFlag char (1) NOT NULL DEFAULT 'N',
cField varchar DEFAULT NULL,
CONSTRAINT _Dummy2_CK1 CHECK (cFlag IN ('Y','N')),
CONSTRAINT _Dummy2_CK2 CHECK ( CASE WHEN cFlag = 'N' THEN TRUE ELSE cField
IS NOT NULL END )
);

On Wed, Sep 2, 2015 at 7:52 PM, <p(dot)buongiovanni(at)net-international(dot)com> wrote:
> CREATE TABLE draft.dummy1
> (
> cflag character(1) NOT NULL DEFAULT 'N'::bpchar,
> cfield character varying,
> CONSTRAINT _dummy1_ck1 CHECK (cflag = ANY (ARRAY['Y'::bpchar,
> 'N'::bpchar])),
> CONSTRAINT _dummy1_ck2 CHECK (cflag = 'N'::bpchar OR cflag = 'Y'::bpchar
> AND cfield IS NOT NULL)
> )
> WITH (
> OIDS=FALSE
> );
> ALTER TABLE draft.dummy1
> OWNER TO netdw_owner;
>
> As you can see brackets has been removed from the definition of the
> constraint _Dummy1_CK2. Clearly this doesn't work as I want.

AND has precedence over OR in such expressions, so this definition is
not wrong. In any case this is not a bug, just a matter of style, and
you could as well use pg_get_constraintdef with its pretty format to
get more parenthesis around those expressions, though this would need
modifications at pgadmin level I imagine.
--
Michael