Re: DOMAIN/composite TYPE vs. base TYPE

On 9/28/20 2:58 PM, Joe Abbate wrote:
> Hello Tom,
> 
> 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?  (1) How late model are we talking? 
> The DOMAIN syntax doesn't seem changed from PG 11 to PG 13? (2) Can a 
> CHECK constraint specify attributes of the composite?
> 
>> 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.

Except that the case Tom is talking about would occur due to something like:

select table_a left join table_b on table_a.id = table_b.id where 
table_b.id is null;

That has been very useful to me and I'm not sure that how anything you 
replace NULL with to represent 'unknown' would change the situation.

> 
> Joe
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com