Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux