Re: LIKE INCLUDING CONSTRAINTS is broken - Mailing list pgsql-hackers

It seems CREATE TABLE ... (LIKE INCLUDING CONSTRAINTS)  doesn't work
cleanly when there's also regular inheritance; my guess is that attnums
get messed up at some point after the constraints are generated.

Here's a trivial test case:

create table b (b1 int unique check (b1 > 100));
CREATE TABLE c (c1 int not null references b (b1));
create table d (d1 int, d2 point not null);
create table a (a1 int not null,
                a2 text primary key,
                a3 timestamptz(6),
                like b including constraints,
                like c)
        inherits (d);

You can see the broken state:

alvherre=# \d [ab]
                   Tabla «public.a»
 Columna |            Tipo             | Modificadores
---------+-----------------------------+---------------
 d1      | integer                     |
 d2      | point                       | not null
 a1      | integer                     | not null
 a2      | text                        | not null
 a3      | timestamp(6) with time zone |
 b1      | integer                     |
 c1      | integer                     | not null
Índices:
    "a_pkey" PRIMARY KEY, btree (a2)
Restricciones CHECK:
    "b_b1_check" CHECK (a2 > 100)
Hereda: d

         Tabla «public.b»
 Columna |  Tipo   | Modificadores
---------+---------+---------------
 b1      | integer |
Índices:
    "b_b1_key" UNIQUE CONSTRAINT, btree (b1)
Restricciones CHECK:
    "b_b1_check" CHECK (b1 > 100)
Referenciada por:
    TABLE "c" CONSTRAINT "c_c1_fkey" FOREIGN KEY (c1) REFERENCES b(b1)


Notice how the CHECK constraint in table b points to column b1, but in
table a it is mentioning column a2, even though that one is not even of
the correct datatype.  In fact if you try an insert, you get a weird
error message:

alvherre=# insert into a (d2, a2, a1, c1) values ('(1, 0)', '1', 1, 1);
ERROR:  attribute 4 has wrong type
DETALLE:  Table has type text, but query expects integer.

If I take out the INHERITS clause in table a, the error disappears.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


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