Re: PostgreSQL domains and NOT NULL constraint

Vik Fearing <vik(at)postgresfriends(dot)org> writes:
> On 10/12/23 15:54, Tom Lane wrote:
>> There's been some discussion of treating the output of such a join,
>> subselect, etc as being of the domain's base type not the domain
>> proper. That'd solve this particular issue since then we'd decide
>> we have to cast the base type back up to the domain type (and hence
>> check its constraints) before inserting the row. But that choice
>> just moves the surprise factor somewhere else, in that queries that
>> used to produce one data type now produce another one. There are
>> applications that this would break. Moreover, I do not think there's
>> any justification for it in the SQL spec.

> I do not believe this is a defect of the SQL standard at all.
> SQL:2023-2 Section 4.14 "Domains" clearly states "The purpose of a
> domain is to constrain the set of valid values that can be stored in a
> column of a base table by various operations."

So I wonder what is the standard's interpretation of

regression=# create domain dpos as integer not null check (value > 0);
CREATE DOMAIN
regression=# create table t1 (x int, d dpos);
CREATE TABLE
regression=# create view v1 as select ty.d from t1 tx left join t1 ty using (x);
CREATE VIEW
regression=# \d+ v1
View "public.v1"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+------+-----------+----------+---------+---------+-------------
d | dpos | | | | plain |
View definition:
SELECT ty.d
FROM t1 tx
LEFT JOIN t1 ty USING (x);

If we are incorrect in ascribing the type "dpos" to v1.d, where
in the spec contradicts that? (Or in other words, 4.14 might lay
out some goals for the feature, but that's just empty words if
it's not supported by accurate details in other places.)

regards, tom lane