Re: [GENERAL] default representation of null in psql

On 12/12/2016 14:33, Adrian Klaver wrote:
> On 12/11/2016 11:34 PM, Chris Withers wrote:
>> On 01/12/2016 12:12, Francisco Olarte wrote:
>>> On Thu, Dec 1, 2016 at 12:56 PM, Chris Withers
>>> <chris@simplistix.co.uk> wrote:
>>>> So, first observation: if I make room nullable, the exclude
>>>> constraint does
>>>> not apply for rows that have a room of null. I guess that's to be
>>>> expected,
>>>> right?
>>>
>>> I would expect it, given:
>>>
>>> n=> select null=null, null<>null, not (null=null);
>>>  ?column? | ?column? | ?column?
>>> ----------+----------+----------
>>>           |          |
>>> (1 row)
>>>
>>> Those are nulls,
>>
>> Yes, it's a shame psql has the same repr for null and empty-string ;-)
>
> test=# select NULL;
>  ?column?
> ----------
>
> (1 row)
>
> test=# \pset null 'NULL'
> Null display is "NULL".
>
> test=# select NULL;
>  ?column?
> ----------
>  NULL
> (1 row)

Sure, so perhaps the default should change?

Of course, no-one has yet offered anything on the question I was really
hoping for help with:

>> Working with the exclude constraint example from
>> https://www.postgresql.org/docs/current/static/rangetypes.html:
>>
>> CREATE EXTENSION btree_gist;
>> CREATE TABLE room_reservation (
>>     room text,
>>     during tsrange,
>>     EXCLUDE USING GIST (room WITH =, during WITH &&)
>> );
>>
>> Next question: if lots of rows have open-ended periods
>> (eg: [, 2010-01-01 15:00) or [2010-01-01 14:00,)), how does that affect
>> the performance of the btree gist index backing the exclude constraint?
>>
>> Tom Lane made a comment on here but never followed up with a definitive
>> answer. Can anyone else help?

cheers,

Chris