Search Postgresql Archives

Re: DOMAIN/composite TYPE vs. base TYPE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Joe Abbate <jma@xxxxxxxxxxxxxxxxx> 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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux