Re: Assigning values to a range in Pgsql and inclusive / exclusive bounds - Mailing list pgsql-general

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?

Realized what you want is:

select tstzrange('today', ('now'::timestamptz + interval '1 hour'), '[)');
                           tstzrange
--------------------------------------------------------------
  ["06/12/2020 00:00:00 PDT","06/12/2020 13:59:27.554229 PDT")


tx tstzrange := tstzrange('today', ('now'::timestamptz + interval '1
hour'), '[)') ;

>
> Thanks in advance for your advice.
>
> 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.klaver@aklaver.com