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
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 |