On 2023-04-10 14:35:38 +0200, Karsten Hilbert wrote:
> All I really wanted to hint at is that "incoming call
> timestamp" may work pretty well in given settings but does
> not _always_ make for a "unique enough" key.
This is true for all natural primary keys: Any attribute of an entity
which is un
Am Mon, Apr 10, 2023 at 01:33:41PM +0200 schrieb Francisco Olarte:
> > > > This the part that's always eluded me: How does the client, the
> > > > UPSERTer, come to hold an id and not know whether or not it's already in
> > > > the database.
> > >
> > > This is extremely easy to do if you have nat
Hi karsten:
On Mon, 10 Apr 2023 at 11:40, Karsten Hilbert wrote:
>
> Am Mon, Apr 10, 2023 at 09:41:15AM +0200 schrieb Francisco Olarte:
>
> > On Mon, 10 Apr 2023 at 04:16, Rob Sargent wrote:
> > > > An UPSERT checks whether a row exists, if so, it does an update, if not
> > > > it does an inser
Am Mon, Apr 10, 2023 at 09:41:15AM +0200 schrieb Francisco Olarte:
> On Mon, 10 Apr 2023 at 04:16, Rob Sargent wrote:
> > > An UPSERT checks whether a row exists, if so, it does an update, if not
> > > it does an insert. This is the literal definition.
> > This the part that's always eluded me:
Hi Rob:
On Mon, 10 Apr 2023 at 04:16, Rob Sargent wrote:
> > An UPSERT checks whether a row exists, if so, it does an update, if not it
> > does an insert. This is the literal definition.
> This the part that's always eluded me: How does the client, the
> UPSERTer, come to hold an id and not kno
Hi Louis:
On Mon, 10 Apr 2023 at 03:05, Louis Tian wrote:
> I think we need to make a distinction between an "operation" and a
> "statement".
OK
> The concept of idempotency applies to an "operation" not an entire statement.
I think I'll need a definition of both to say anything on this.
> Lik
On 4/9/23 19:16, Rob Sargent wrote:
On 4/9/23 19:55, Louis Tian wrote:
Hi Alban,
"I am not expecting an error here", by "here" I means when doing a
TRUE UPSERT (an upsert current does not exist in Postgres).
I am NOT referring to an "Insert on conflict do update" (which despite
its intention
On 4/9/23 19:55, Louis Tian wrote:
Hi Alban,
"I am not expecting an error here", by "here" I means when doing a TRUE UPSERT
(an upsert current does not exist in Postgres).
I am NOT referring to an "Insert on conflict do update" (which despite its
intention and wide acceptance is not fully equi
NFLICT DO UPDATE is not complete equivalent to a TRUE upsert.
Cheers,
Louis Tian
-Original Message-
From: Alban Hertroys
Sent: Sunday, April 9, 2023 7:26 PM
To: Louis Tian
Cc: Peter Geoghegan ; pgsql-general@lists.postgresql.org
Subject: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres
CAU
e database to remain the same.
Cheers,
Louis Tian
-Original Message-
From: Francisco Olarte
Sent: Sunday, April 9, 2023 7:32 PM
To: Louis Tian
Cc: Peter Geoghegan ; pgsql-general@lists.postgresql.org
Subject: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres
CAUTION: This email originat
uld be in my
opinion).
It was just a question just to confirm my understanding so I got what I need,
so thank you all for that.
Cheers,
Louis Tian
From: Adrian Klaver
Sent: Sunday, April 9, 2023 7:51 AM
To: Louis Tian ; Peter Geoghegan
Cc: pgsql-general@lists.postgresql.org
Subject: [EX
From: Israel Brewster
Sent: Sunday, April 9, 2023 3:09 AM
To: Louis Tian
Cc: Peter Geoghegan ; pgsql-general@lists.postgresql.org
Subject: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres
Thanks Israel. Your example really helped me to understand where we
(not the OP on idempotency)
On Sat, 8 Apr 2023 at 18:33, Louis Tian wrote:
> > In general UPSERT (or any definition of it that I can think of) does
> > not imply idempotency.
> "Idempotence is the property of certain operations in mathematics and
> computer science whereby they can be applied mu
> On 7 Apr 2023, at 2:49, Louis Tian wrote:
(…)
> I am not expecting an error here. The problem is with no conflict it always
> go down the insert path first and results in a not null constraint error.
> While I am expecting the insert is never executed in the first place when
> that row al
This went on too long. On conflict is the solution. It has been since at
least 9. I have run that in a production stored proc without a single
problem.
This is an actual and literal solution.
Thanks,
Ben
On Sat, Apr 8, 2023, 5:51 PM Adrian Klaver
wrote:
> On 4/6/23 17:49, Louis Tian wrote:
> >
On 4/6/23 17:49, Louis Tian wrote:
Hi Peter,
Thanks for your reply. Appreciate the help and discussion.
In general UPSERT (or any definition of it that I can think of) does
not imply idempotency.
"Idempotence is the property of certain operations in mathematics and computer
science whereb
> On Apr 6, 2023, at 4:49 PM, Louis Tian wrote:
>
> Hi Peter,
>
> Thanks for your reply. Appreciate the help and discussion.
>
>> In general UPSERT (or any definition of it that I can think of) does
>> not imply idempotency.
>
> "Idempotence is the property of certain operations in mathematic
: UPSERT in Postgres
CAUTION: This email originated from outside of Envirada. Do not click links or
open attachments unless you recognize the sender and know the content is safe.
On 4/5/23 23:21, Louis Tian wrote:
> This is a question/feature request.
>
> Given the definition of upsert, I&#x
ormation on this.
Thanks for the pointer. Reading into it.
Cheers,
Louis Tian
From: Peter Geoghegan
Sent: Friday, April 7, 2023 6:58 AM
To: Louis Tian
Cc: pgsql-general@lists.postgresql.org
Subject: [EXTERNAL]: Re: UPSERT in Postgres
CAUTION: This email originated from outside of Envirada. D
On 4/6/23 18:27, Louis Tian wrote:
Hi Adrian,
Thank you. I think this is a better approach than trigger-based
solution, at least for my taste.
That being said, it does require some logic to push to the client side
(figuring out which required column value is missing and set it value to
the ex
On 4/6/23 18:27, Louis Tian wrote:
Hi Adrian,
Thank you. I think this is a better approach than trigger-based
solution, at least for my taste.
That being said, it does require some logic to push to the client side
(figuring out which required column value is missing and set it value to
the ex
That is the answer. Postgresql can upsert easily via triggers and on
conflict.
Thanks,
Ben
On Thu, Apr 6, 2023, 5:01 PM Adrian Klaver
wrote:
> On 4/5/23 23:21, Louis Tian wrote:
> > This is a question/feature request.
> >
>
> > Given the definition of upsert, I'd expect an upsert command to do
On 4/5/23 23:21, Louis Tian wrote:
This is a question/feature request.
Given the definition of upsert, I'd expect an upsert command to do the
following.
- `upsert into person (id, name) values (0, 'foo')` to insert a new row
- `upsert into person (id, is_active) values (0, true)` updates the
On Thu, Apr 6, 2023 at 1:21 PM Louis Tian wrote:
> An implicit assumption behind this definition is that table must have a
> primary key for the upsert operation to make sense since it's the primary key
> that uniquely identifies a row.
It could just be a unique index or a unique constraint. So
This is a question/feature request.
Virtually all references on the internet today suggests one can "upsert" in
PostgreSQL using the "insert ... on conflict do update ..." statement.
But this is not complete true.
The PostgreSQL's own wiki page
(https://wiki.postgresql.org/wiki/UPSERT#.22UPS
25 matches
Mail list logo
