Re: Assigning values to a range in Pgsql and inclusive / exclusivebounds - 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