From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Joe Abbate <jma(at)freedomcircle(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: DOMAIN/composite TYPE vs. base TYPE |
Date: | 2020-09-28 22:45:18 |
Message-ID: | 474455.1601333118@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Joe Abbate <jma(at)freedomcircle(dot)com> writes:
> On 28/9/20 17:25, Tom Lane wrote:
>> Domain-over-composite might be a slightly simpler answer than your first
>> one. It's only available in relatively late-model PG, and I'm not sure
>> about its performance relative to your other design, but it is an
>> alternative to think about.
> "Domain-over-composite" meaning create a TYPE first (DATE, CHAR(1)) and
> then a DOMAIN based on that type?
Right.
regression=# create type t1 as (d date, t char(1));
CREATE TYPE
regression=# create domain dt1 as t1 check((value).t in ('a', 'b'));
CREATE DOMAIN
> (1) How late model are we talking?
> The DOMAIN syntax doesn't seem changed from PG 11 to PG 13?
Back to 11, looks like. The syntax didn't change, but v10 complains
ERROR: "t1" is not a valid base type for a domain
>> Note that attaching NOT NULL constraints at the domain level is almost
>> never a good idea, because then you find yourself with a semantically
>> impossible situation when, say, a column of that type is on the nullable
>> side of an outer join. We allow such constraints, but they will be
>> nominally violated in cases like that.
> NULLs: Tony Hoare's "billion dollars of pain and damage" transported to SQL.
I dunno, outer joins are awfully useful. It is true that the SQL
committee has stuck too many not-quite-consistent meanings on NULL,
but on the other hand, several different kinds of NULL might be
worse.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Yessica Brinkmann | 2020-09-29 05:20:10 | Gurjeet Singh Index Adviser User Interface |
Previous Message | Adrian Klaver | 2020-09-28 22:12:59 | Re: DOMAIN/composite TYPE vs. base TYPE |