Обсуждение: Assigning values to a range in Pgsql and inclusive / exclusive bounds
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;$$
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.klaver@aklaver.com
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
Thanks for that perfect... missed the use of tstzrange() as a 'function' in the documentation.
Best regards
Ron
On Fri, 12 Jun 2020 at 21:02, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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