Hi; I figured I would report this as well, primarily because people getting into table inheritance may try to use this to solve the set exclusion problem (i.e. partly required to prevent duplicate key values in an inheritance tree). I see this as minor because I don't see a lot of people using these aspects of the software in this way now.
or_examples=# create table cities (city text, state text, is_capital bool, altitude int, check(not(is_capital and tableoid::regclass::text = 'cities'))); The intent of the check constraint is to force rows in the parent table to use only a part of the key domain, while another portion (where is_capital is true) can be reserved for child tables. or_examples=# insert into cities values ('Seattle', 'Washington', false, 100); INSERT 0 1 or_examples=# insert into cities values ('Olympia', 'Washington', true, 100); INSERT 0 1 Ok, note that the check constraint was violated by the second row but apparently this wasn't caught. or_examples=# select *, tableoid::regclass::text from cities; city | state | is_capital | altitude | tableoid ---------+------------+------------+----------+---------- Seattle | Washington | f | 100 | cities Olympia | Washington | t | 100 | cities (2 rows) And indeed if we try to add the constraint again over the top PostgreSQL will complain loudly. or_examples=# alter table cities add check(not(is_capital and tableoid::regclass::name = 'cities')); ERROR: check constraint "cities_check1" is violated by some row My guess is that tableoid is not known when the check constraint is checked. It seems to me one option would be to either disallow checking tableoid in the check constraint or making this known. However as it is, PostgreSQL will not raise an error until after the insert has already been made and the check constraint is re-applied. or_examples=# select version(); version --------------------------------------------------------------------------------- -------------------------- PostgreSQL 9.1.4 on i386-redhat-linux-gnu, compiled by gcc (GCC) 4.7.0 20120507 (Red Hat 4.7.0-5), 32-bit (1 row) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs