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.buongiovanni@net-international.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 )
);

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs