document semantics of "partial" upsert

[RaduBerinde]

Radu Berinde (RaduBerinde) commented:

I recently was surprised to learn that when you use UPSERT and don't provide values for all columns of the table, the forms UPSERT INTO t and UPSERT INTO t(col1,col2,..) have different semantics. For example:

root@127.64.244.46:35569/movr> create table cpq (c int primary key, p int default 4, q int default 8, other int);
CREATE TABLE

Time: 5.022576ms

root@127.64.244.46:35569/movr> insert into cpq values (1,1,1,1);
INSERT 1

Time: 2.289808ms

root@127.64.244.46:35569/movr> upsert into cpq(c) values (1) returning c,p,q;
  c | p | q
----+---+----
  1 | 1 | 1
(1 row)

Time: 1.780492ms

root@127.64.244.46:35569/movr> upsert into cpq values (1) returning c,p,q;
  c | p | q
----+---+----
  1 | 4 | 8
(1 row)

The form cpq(c) preserves the values for p, q that already exist for the row that is being updated. The form cpq always writes the default values.

I am not sure if there is a similar difference for other INSERT .. ON CONFLICT variants.

CC @knz who may have more knowledge on this. Note that the optimizer code was written to replicate the semantics of the old code, so the origin of these semantics predates the optimizer.

Jira Issue: DOC-444

[knz]

Yes I remember this somewhat clearly. This is spelled out in the pg sources for insert on conflict, the syntax without column list means "all columns" and only the columns *not* in the list get the DEFAULT values. So to get default on some columns you need to use the explicit syntax. For upsert the default is what's in the table already hence the semantics. An argument can be made of having the "UPSERT" syntax do something different from "INSERT (on conflict)" since pg doesn't have upsert. No strong feeling there. -- Verstuurd vanaf mijn Android apparaat met K-9 Mail. Excuseer mijn beknoptheid.