The following bug has been logged online: Bug reference: 5477 Logged by: Matt Nourse Email address: matt...@nplus1.com.au PostgreSQL version: 8.4 Operating system: Linux (Debian and Red Hat) Description: CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery Details:
Set up: CREATE DOMAIN test_id_domain INT NOT NULL; CREATE TABLE test_state(id test_id_domain PRIMARY KEY, display_value varchar(20) NOT NULL); CREATE TABLE test_city(state_id test_id_domain REFERENCES test_state(id)); This produces an error as expected: INSERT INTO test_city(state_id) VALUES (NULL); This successfully inserts a NULL value into the state_id field: INSERT INTO test_city(state_id) VALUES ((SELECT id FROM test_state WHERE display_value = 'Nonexistent state')); When I use simpler subqueries (eg SELECT 1 WHERE 1 = 0), the NOT NULL constraint is enforced. If I remove the test_id_domain domain and replace its use with INT NOT NULL, the constraint is enforced. Thanks and regards, Matt -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
