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
includingconstraints, like c)inherits (d);
You can see the broken state:
alvherre=# \d [ab] Tabla «public.a»Columna | Tipo | Modificadores
---------+-----------------------------+---------------d1 | integer | d2 | point
| not nulla1 | integer | not nulla2 | text | not
nulla3 | 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
