Thread: ADD CHECK fails for parent table if column used in CHECK is fully-qualified

Hi everyone,

Consider the following case:

CREATE TABLE t0(c0 boolean);
CREATE TABLE t1(c0 boolean) INHERITS(t0);
ALTER TABLE t0 ADD CHECK(t0.c0); -- unexpected: ERROR:  missing
FROM-clause entry for table "t0"

Is it expected that this fails? Without a child table, this works as I
would expect. If this is expected, I think that the error message
could be misleading. It seems that the full-qualified c0 column (i.e.,
t0.c0) triggers this, because the following works:

CREATE TABLE t0(c0 boolean);
CREATE TABLE t1(c0 boolean) INHERITS(t0);
ALTER TABLE t0 ADD CHECK(c0); -- no error

Best,
Manuel

Manuel Rigger <rigger.manuel@gmail.com> writes:
> Consider the following case:

> CREATE TABLE t0(c0 boolean);
> CREATE TABLE t1(c0 boolean) INHERITS(t0);
> ALTER TABLE t0 ADD CHECK(t0.c0);
> -- unexpected: ERROR:  missing FROM-clause entry for table "t0"

> Is it expected that this fails?

I don't really have a problem with that.  You specified that the
check was on t0, and that would be an invalid constraint for t1.

Now it is a bit weird that you can do this:

regression=# CREATE TABLE t0(c0 boolean);
CREATE TABLE
regression=# ALTER TABLE t0 ADD CHECK(t0.c0);
ALTER TABLE
regression=# CREATE TABLE t1() INHERITS(t0);
CREATE TABLE

That happens because after the ALTER, the check constraint is
remembered as just c0:

regression=# \d t0
                 Table "public.t0"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 c0     | boolean |           |          | 
Check constraints:
    "t0_c0_check" CHECK (c0)

and that can be propagated down to t1.  So the system has forgotten
a syntactic detail that perhaps it shouldn't have.  But I can't
get excited about changing that.

            regards, tom lane