Re: Assigning values to a range in Pgsql and inclusive / exclusive bounds

On 6/12/20 11:45 AM, Ron Clarke wrote:
> Hi,
>
> I've got a simple problem, but I'm convinced that there must be an
> elegant solution. I'm a refugee from the world of MSSQL, so I'm still
> finding some aspects of PostgreSQL alien.
>
> I'm trying to use the /tstzrange /datatype. My issue is correctly
> setting the bound types when assigning values to a range in code (PGSQL).
>
> So if i declare this : e.g.
>
> /declare tx tstzrange := '[today, tomorrow)' ;/
>
>
> I get the variable tx as expected with the Inclusive '[' lower bound and
> exclusive upper ')' bound.
>
> But if I attempt to reassign the value in code within pgsql I can do
> this simply, only with '(' syntax for the lower bound i.e. with an
> exclusive lower bound, e.g so this works:-
>
> /tx= (Timestamptz 'today', timestamptz 'now' + interval '1 hour');/
>
> but if I try
> /tx= [Timestamptz 'today', timestamptz 'now' + interval '1 hour'); /
> /
> /
> this will have syntax errors - as the hidden 'select [' upsets the
> parser.   I've tried to include a '[)' in variations of the expression,
> but just get various syntax errors..
>
> I've tried many combinations and I can get it to work using casts and
> concatenations, e.g. :-
>
> / tx = CONCAT('[', 'today'::Timestamptz,',',('now' ::Timestamptz +
> interval '1 hour'):: timestamptz , ')'):: tstzrange ;/
>
> works but I can't help thinking that I'm missing something much simpler
> and more elegant.
> How should this actually be done?
>
> Thanks in advance for your advice.

If:

select tstzrange('today', 'tomorrow', '[)');
tstzrange
-------------------------------------------------------
["06/12/2020 00:00:00 PDT","06/13/2020 00:00:00 PDT")

then:

tx tstzrange := tstzrange('today', 'tomorrow', '[)') ;

Not tested.

>
> Ron
> Stay safe everyone.
>
>
> here's an example script to show what I mean:-
>
> /do
> //$$
> //DECLARE
> /
>
> /tx tstzrange := '[today, tomorrow)' ;/
>
> /answer text;/
>
> /BEGIN
> /
>
> /RAISE NOTICE 'Start %', tx;/
>
> /answer = tx @> 'today'::Timestamptz;/
>
> /RAISE NOTICE 'today  %', answer;/
>
> /answer = tx @> 'tomorrow'::Timestamptz;/
>
> /RAISE NOTICE 'tomorrow  %', answer;/
>
> /-- ( works
> -- tx= (Timestamptz 'today', timestamptz 'now' + interval '1 hour');
> /-- [ doesn't work
> -- tx= [Timestamptz 'today', timestamptz 'now' + interval '1 hour');
> -- working around the parser??
> /tx = CONCAT('[', 'today'::Timestamptz,',',('now' ::Timestamptz
> + interval '1 hour'):: /timestamptz , ')'):: tstzrange ;
>
> /RAISE NOTICE 'reassign  %', tx;/
>
> /answer = tx @> 'today'::Timestamptz;/
>
> /RAISE NOTICE 'today  %', answer;/
>
> /answer = tx @> 'now'::Timestamptz;/
>
> /RAISE NOTICE 'now    %', answer;/
>
> /END;/
> /$$ /
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com