Domains and generated columns

From: Fabian Pijcke <fabian(dot)pijcke(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Domains and generated columns
Date: 2021-03-18 11:07:26
Message-ID: CALebJyGCzBp1EYJx9BN=4wXp_aJyUjqAaU4ra6P7WhwzkJWE5g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear list,

I ran into an error when running the following snippet of code (I
simplified as much as I could):

CREATE DOMAIN domaintest AS VARCHAR(10) NOT NULL;

CREATE TABLE tabletest (
fieldtest domaintest GENERATED ALWAYS AS ('valuetest') STORED
);

INSERT INTO tabletest DEFAULT VALUES;

In PostgreSQL 12 and 13, I get the following error:
domain domaintest does not allow null values.

If I move the NOT NULL constraint from the domain to the table, the error
disappears.

I suspect that the NULL check is done before the GENERATED column is
actually computed. I don't think this is related to the warning the
documentation gives about NULL values and DOMAINs (
https://www.postgresql.org/docs/13/sql-createdomain.html#id-1.9.3.62.7)

Is this the expected behaviour? Is there any way to circumvent this and use
a domain with a NOT NULL constraint on a generated column? (I know the
documentation recommends to avoid NOT NULL on domains, but I'd still like
to do so).

Thank you,

--
Fabian Pijcke

Browse pgsql-general by date

  From Date Subject
Next Message Luca Ferrari 2021-03-18 11:14:39 Re: questions about wraparound
Previous Message Luca Ferrari 2021-03-18 08:56:16 questions about wraparound