The following bug has been logged on the website:
Bug reference: 15085
Logged by: Daniel Einspanjer
Email address: deinspanjer@gmail.com
PostgreSQL version: 9.6.7
Operating system: Linux
Description:
I was trying to create two domains, one that allowed nulls, and another that
built on it that didn't allow nulls. After some testing, I came across this
unusual behavior that I believe might be a bug.
I did check the TODO and tried to do some web searches (hard keywords to
work with here) but I didn't find any reports about this.
Please find below a simple test case.
create domain test_domain text not null;
create temporary table
test_domain_constraint_vs_column_constraint(val_to_return test_domain,
val_to_find test_domain not null);
insert into test_domain_constraint_vs_column_constraint values
('good','good');
select * from test_domain_constraint_vs_column_constraint;
-- the domain constraint doesn't allow this insert.
insert into test_domain_constraint_vs_column_constraint values
(null,'bad');
-- the table constraint doesn't allow this insert.
insert into test_domain_constraint_vs_column_constraint values
('bad',null);
create function test_domain_constraint_in_return(_in test_domain) returns
test_domain language sql strict as $$
select val_to_return from test_domain_constraint_vs_column_constraint where
val_to_find = _in;
$$;
-- happy case
select test_domain_constraint_in_return('good') as val,
pg_typeof(test_domain_constraint_in_return('good')) as typ;
-- sad case
select test_domain_constraint_in_return('ugly') as val,
pg_typeof(test_domain_constraint_in_return('ugly')) as typ;
-- if we try to insert into the val_to_find column, the column constraint
prevents it
insert into test_domain_constraint_vs_column_constraint values
('ugly',test_domain_constraint_in_return('ugly'));
-- but if we insert into the val_to_return column which only has the domain
constraint to protect it, we succeed.
insert into test_domain_constraint_vs_column_constraint values
(test_domain_constraint_in_return('ugly'),'ugly');
select * from test_domain_constraint_vs_column_constraint where
val_to_return is null;
-- cleanup
drop domain test_domain cascade ;
drop table test_domain_constraint_vs_column_constraint;