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;*
*$$ *
Re: Assigning values to a range in Pgsql and inclusive / exclusive bounds
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
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.kla...@aklaver.com
>
More than one UNIQUE key when matching items..
/* I'm trying to port a system from SQL server, and at the same time better learn postgreSQL. I've come across a problem that is easily solved in that world, but I am struggling to find an approach in postgres that works. We have 2 sets of events A and B (sets), they have a shared number (ncode), both have unique Id's We want to link items of set A to those of set B, but each item of each set can only be linked once.That is we do not want to link all set 'A' items to all set 'B' Items with the same code. In SQL Server this is easy, we insert the records into a temporary table with separate Unique indexes on the id for set a and the ids for set b and put the 'ignore_dup_key' on which tells SQL Server to ignore duplicate rows and carry on. The nearest to this at first appears to be the ON CONFLICT ON CONSTRAINT IGNORE in Postgres. But this only works with a single constraint, at a time i.e. we can't set the ON CONFLICT ON CONSTRAINT IGNORE to work with multiple UNIQUE indexes. To show the problem: I'm using PostgreSQL version 11. */ -- source data WITH sd AS ( SELECT iid, s, ncode FROM ( VALUES (1, 'A', 10), (2, 'A', 30), (3, 'A', 10), (4, 'B', 10), (5, 'B', 20), (6, 'B', 10) ) AS tx (iid, s, ncode)) SELECT iid, s, ncode FROM sd /* The target result would be : id:1, A, 10 this matches id:4, B, 10 id:3, A, 10 this matches id:6, B, 10 */ -- Example to get the *wrong *answer, i.e. both sets of links WITH sd (i, s, n ) AS ( SELECT iid, s, ncode FROM ( VALUES (1, 'A', 10), (2, 'A', 30), (3, 'A', 10), (4, 'B', 10), (5, 'B', 20), (6, 'B', 10) ) AS tx (iid, s, ncode)) , x AS ( SELECT ax.i as ia, ax.s as sa, ax.n as na, bx.i as ib, bx.s as sb, bx.n as nb, ROW_NUMBER () OVER ( PARTITION BY bx.i ORDER BY ax.i ) as rx FROM sd AS ax INNER JOIN sd AS bx ON ax.n = bx.n and ax.i != bx.i and bx.s = 'B' WHERE ax.s = 'A' ) SELECT ia,ib, na, rx FROM x ; /* I've tried using a recursive CTE where I'm trying to exclude results from the result set that have already been identified, but I can't get an allowed syntax. Doesn't seem to allow joins to the recursive term to exclude results. */ /* I've tried Unique and Exclusion constraints on temporary table, e.g */ -- similar Example to get the wrong answer, i.e. both sets of links DROP TABLE IF EXISTS links ; CREATE TEMPORARY TABLE links (mid serial , ia int , -- ia int UNIQUE, ib int , -- ib int UNIQUE, EXCLUDE USING gist (ia WITH =, ib WITH =) ) ; WITH sd (i, s, n ) AS ( SELECT iid, side, ncode FROM ( VALUES (1, 'A', 10), (2, 'A', 30), (3, 'A', 10), (4, 'B', 10), (5, 'B', 20), (6, 'B', 10) ) AS tx (iid, side, ncode)) , x AS ( SELECT ax.i as ia, ax.s as sa, ax.n as na, bx.i as ib, bx.s as sb, bx.n as nb, ROW_NUMBER () OVER ( PARTITION BY bx.i ORDER BY ax.i ) as rx FROM sd AS ax INNER JOIN sd AS bx ON ax.n = bx.n and ax.i != bx.i and bx.s = 'B' WHERE ax.s = 'A' ) -- SELECT * FROM x INSERT INTO links(ia,ib) SELECT ia, ib FROM x ON CONFLICT ON CONSTRAINT links_ia_ib_excl DO NOTHING; -- SELECT * from links; /* I've also tried and failed to use array(ia,ib) within or as computed column of an Exclusion constraint of && s on temporary table, e.g but can't find any syntax that doesn't result in an error */ DROP TABLE IF EXISTS links ; CREATE TEMPORARY TABLE links (mid serial , ia int , -- ia int UNIQUE, ib int , -- ib int UNIQUE, ix int[], EXCLUDE USING gist (ix WITH &&) ) ; -- This gives me: -- ERROR: data type integer[] has no default operator class for access method "gist" -- I have the btree_gist extension installed /* I appreciate I could create a cursor from a list of proposed links and step through each one, checking if the id value has been "used up" but I am trying to keep this as a set based operation to give me the results in one statement. There are some similar questions w.r.t. duplicate detection, but these again seem to be solved by evaluating each proposed record individually. If that's just what I have to do then so be it. There is probably a simple 'postgreSQL' freindly approach I'm still yet to discover having spent too long in Sybase and SQL Server worlds. Thanks for looking at this */
Re: More than one UNIQUE key when matching items..
Thank you, simple, and effective. Got sucked into trying to use a named constraint. Best regards Ron On Sat, 20 Mar 2021 at 20:00, Laurenz Albe wrote: > On Sat, 2021-03-20 at 15:51 +0000, Ron Clarke wrote: > > In SQL Server this is easy, we insert the records into a temporary table > with separate Unique > > indexes on the id for set a and the ids for set b and put the > 'ignore_dup_key' on which tells > > SQL Server to ignore duplicate rows and carry on. > > > > The nearest to this at first appears to be the ON CONFLICT ON CONSTRAINT > IGNORE in Postgres. > > But this only works with a single constraint, at a time i.e. we can't > set the ON CONFLICT ON > > CONSTRAINT IGNORE to work with multiple UNIQUE indexes. > > You can try this: > > INSERT ... ON CONFLICT DO NOTHING; > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > >
Re: More than one UNIQUE key when matching items..
Hey thanks for working out a solution to this deceptive problem. One of those you expect to be simple, but then all of a sudden it isn't. Best regards Ron On Sat, 20 Mar 2021 at 19:01, Allan Kamau wrote: > > > > > > > On Sat, Mar 20, 2021 at 6:52 PM Ron Clarke wrote: > >> /* >> I'm trying to port a system from SQL server, and at the same time better >> learn postgreSQL. >> >> I've come across a problem that is easily solved in that world, but I am >> struggling to find an approach in postgres that works. >> >> We have 2 sets of events A and B (sets), they have a shared number >> (ncode), both have unique Id's >> >> We want to link items of set A to those of set B, but each item of each >> set can only be linked once.That is we do not want to link all set 'A' >> items to all set 'B' Items with the same code. >> >> In SQL Server this is easy, we insert the records into a temporary table >> with separate Unique indexes on the id for set a and the ids for set b and >> put the 'ignore_dup_key' on which tells SQL Server to ignore duplicate rows >> and carry on. >> >> The nearest to this at first appears to be the ON CONFLICT ON CONSTRAINT >> IGNORE in Postgres. But this only works with a single constraint, at a time >> i.e. we can't set the ON CONFLICT ON CONSTRAINT IGNORE to work with >> multiple UNIQUE indexes. >> >> To show the problem: >> >> I'm using PostgreSQL version 11. >> >> */ >> -- source data >> WITH sd AS ( >> SELECT iid, s, ncode FROM ( >> VALUES (1, 'A', 10), >>(2, 'A', 30), >>(3, 'A', 10), >>(4, 'B', 10), >>(5, 'B', 20), >>(6, 'B', 10) >> ) >> AS tx (iid, s, ncode)) >> SELECT iid, s, ncode FROM sd >> >> /* The target result would be : >> >> id:1, A, 10 this matches id:4, B, 10 >> id:3, A, 10 this matches id:6, B, 10 >> */ >> >> -- Example to get the *wrong *answer, i.e. both sets of links >> >> WITH >> sd (i, s, n ) AS ( >> SELECT iid, s, ncode FROM ( >> VALUES (1, 'A', 10), >> (2, 'A', 30), >> (3, 'A', 10), >> (4, 'B', 10), >> (5, 'B', 20), >> (6, 'B', 10) >> ) >> AS tx (iid, s, ncode)) >> , >> x AS ( SELECT >> >> ax.i as ia, >> ax.s as sa, >> ax.n as na, >> bx.i as ib, >> bx.s as sb, >> bx.n as nb, >> ROW_NUMBER () OVER ( >> >> PARTITION BY bx.i >> >> ORDER BY >> >> ax.i ) as rx >> >> FROM sd AS ax >> INNER JOIN sd AS bx ON ax.n = bx.n and ax.i != bx.i and bx.s = 'B' >> WHERE ax.s = 'A' >> ) >> SELECT ia,ib, na, rx FROM x >> ; >> >> >> /* I've tried using a recursive CTE where I'm trying to exclude results >> from the result set that have already been identified, but I can't get an >> allowed syntax. >> Doesn't seem to allow joins to the recursive term to exclude results. >> */ >> >> >> /* I've tried Unique and Exclusion constraints on temporary table, e.g >> */ >> -- similar Example to get the wrong answer, i.e. both sets of links >> >> DROP TABLE IF EXISTS links ; >> >> CREATE TEMPORARY TABLE links >> (mid serial , >> ia int , >> -- ia int UNIQUE, >> ib int , >> -- ib int UNIQUE, >> EXCLUDE USING gist (ia WITH =, ib WITH =) >> >> ) ; >> >> WITH >> sd (i, s, n ) AS ( >> SELECT iid, side, ncode FROM ( >> VALUES (1, 'A', 10), >> (2, 'A', 30), >> (3, 'A', 10), >> (4, 'B', 10), >> (5, 'B', 20), >> (6, 'B', 10) >> ) >> AS tx (iid, side, ncode)) >> , >> x AS ( >> SELECT >> ax.i as ia, >> ax.s as sa, >> ax.n as na, >> bx.i as ib, >> bx.s as sb, >> bx.n as nb, >> ROW_NUMBER () OVER ( >> PARTITION BY bx.i >> ORDER BY >> ax.i >> ) as rx >> FROM sd AS ax >> INNER JOIN sd AS bx ON ax.n = bx.n and ax.i != bx.i and bx.s = 'B' >> WHERE ax.s = 'A' >> ) >> -- SELECT * FROM x >> INSERT INTO links(ia,ib) >> SELECT ia, ib FROM x >> ON CONFLICT ON CONSTRAINT links_ia_ib_excl DO NOTHING; >>
