Re: PostgreSQL domains and NOT NULL constraint

On 10/13/23 02:44, Tom Lane wrote:
> 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.)
Objection, Your Honor: Relevance.

Regardless of what the spec may or may not say about v1.d, it still
remains that nulls should not be allowed in a *base table* if the domain
says nulls are not allowed. Not mentioned in this thread but the
constraints are also applied when CASTing to the domain.

Now, to answer your straw man, this might be helpful:

SQL:2023-2 Section 11.4 <column definition> Syntax Rule 9, "If the
descriptor of D includes any domain constraint descriptors, then T shall
be a persistent base table.". Your v1 is not that and therefore
arguably illegal.

As you know, I am more than happy to (try to) amend the spec where
needed, but Erki's complaint of a null value being allowed in a base
table is clearly a bug in our implementation regardless of what we do
with views.
--
Vik Fearing