Обсуждение: 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 )
);

On Wed, Sep 2, 2015 at 7:52 PM,  <p.buongiovanni@net-international.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