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.
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;*
*$$ *
