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
