public inbox for [email protected]
help / color / mirror / Atom feedOn exclusion constraints and validity dates
2+ messages / 2 participants
[nested] [flat]
* On exclusion constraints and validity dates
@ 2024-08-22 18:13 Justin Giacobbi <[email protected]>
2024-08-25 15:33 ` Re: On exclusion constraints and validity dates Adrian Klaver <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Justin Giacobbi @ 2024-08-22 18:13 UTC (permalink / raw)
To: [email protected] <[email protected]>
Hello,
I have an issue that on the surface seems orthogonal to existing functionality. I'm trying to dynamically update validity ranges as new <thing>s replace old <thing>s.
In a nutshell the problem looks like this:
psqlprompt=# select * from rangetest;
id | rangecol
----+-----------------------------------------------------
0 | empty
0 | ["2024-05-05 00:00:00+00","2024-05-06 00:00:00+00")
0 | ["2024-05-06 00:00:00+00","9999-03-31 00:00:00+00")
1 | ["2024-05-06 00:00:00+00",)
psqlprompt=# insert into rangetest values (1, '["2024-06-07 00:00:00+0",)') on conflict on constraint rangetest_id_ran
gecol_excl do update rangecol = concat('[', lower(rangetest.rangecol),',', lower(excluded.ran
gecol),')')::tstzrange;
ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints
So I'm not sure if I'm after a feature request, a workaround or contribution advice. Maybe someone can point me in the right direction.
1. A 'currently valid' item that becomes invalid and is replaced by a new 'currently valid' item seems like such a real-world use case that there should be explicit support for it.
* Unfortunately, the temporal tables extensions seem too immature for my needs currently.
2. Barring that an exclusion constraint arbiter would be a lovely solution.
3. Barring either of those at least a 'select all conflicts' type feature that at least makes it easy to pair the offending rows.
Currently I'm looking at working around this in the application or in a stored procedure/insert trigger that is essentially the same logic. Whichever seems easier to maintain.
Advice on how to submit a feature request, or maybe a better workaround that I haven't discovered would be most welcome. What would be even more welcome is someone with insight into these pieces of the program that can tell me if I'd be biting off more than I can chew (or violating a principle) trying to submit one of the three options above as a feature.
Thank you
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: On exclusion constraints and validity dates
2024-08-22 18:13 On exclusion constraints and validity dates Justin Giacobbi <[email protected]>
@ 2024-08-25 15:33 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Adrian Klaver @ 2024-08-25 15:33 UTC (permalink / raw)
To: Justin Giacobbi <[email protected]>; [email protected] <[email protected]>
On 8/22/24 11:13, Justin Giacobbi wrote:
> Hello,
>
> Currently I’m looking at working around this in the application or in a
> stored procedure/insert trigger that is essentially the same logic.
> Whichever seems easier to maintain.
>
> Advice on how to submit a feature request, or maybe a better workaround
> that I haven’t discovered would be most welcome. What would be even more
> welcome is someone with insight into these pieces of the program that
> can tell me if I’d be biting off more than I can chew (or violating a
> principle) trying to submit one of the three options above as a feature.
Merge?:
https://www.postgresql.org/docs/current/sql-merge.html
>
> Thank you
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-08-25 15:33 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-22 18:13 On exclusion constraints and validity dates Justin Giacobbi <[email protected]>
2024-08-25 15:33 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox