Wesley Massuda <wesley.mass...@gmail.com> writes: > I would like to propose extending composite types with constraints.
I'm not convinced we should go there, *particularly* not for not-null constraints. Data-type-related not-null constraints are broken by design, because they are fundamentally inconsistent with outer joins. Here's an example: regression=# create domain nnint as int check (value is not null); CREATE DOMAIN regression=# create table tt (id int, nn nnint); CREATE TABLE regression=# insert into tt values (1,1), (2,1); INSERT 0 2 regression=# select * from tt a left join tt b on a.id = b.nn; id | nn | id | nn ----+----+----+---- 1 | 1 | 1 | 1 1 | 1 | 2 | 1 2 | 1 | | (3 rows) We have here a column that claims to be of type nnint but contains nulls. The only really good solution to this problem for domains, IMO, is to consider that the type of the join output column is changed to the domain's base type, so that there's no relevant constraint for it to violate. We don't do that at present, but if anyone got really bent out of shape about this behavior, I'd tell them to submit a patch that does that. However, if we allow constraints on elements of a composite type, there's no similar "out" available to describe values that look like the type but don't satisfy its constraints. That's a hard place that I don't want to get wedged into. There are other implementation problems that you'd fall foul of as well, for instance that plpgsql lacks any way to initialize rowtype variables to non-null. Those are probably surmountable with enough work, but the outer join problem is built into SQL. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers