All of the syntax is valid PostgreSQL as an example of an UPSERT that returns the old and new values for the field. Let's say I have a table foo
with (1,A)...(5,E)
.
CREATE TEMP TABLE foo
AS
SELECT id, chr(id+64)
FROM generate_series(1,5) AS t(id);
CREATE UNIQUE INDEX ON foo(id);
id | chr
----+-----
1 | A
2 | B
3 | C
4 | D
5 | E
(5 rows)
Now, let's say I want to UPSERT 6 rows. A few colliding, a few new rows.
SELECT id, chr(id+74)
FROM generate_series(3,8) AS t(id);
id | chr
----+-----
3 | M
4 | N
5 | O
6 | P
7 | Q
8 | R
How would I get,
id
- The old value of
chr
- The new value of
chr
In PostgreSQL, I would do..
WITH t1 AS (
SELECT id, foo.chr AS oldchr, chr(id+74)
FROM generate_series(3,8) AS t(id)
LEFT OUTER JOIN foo USING (id)
),
tupdate AS (
UPDATE foo
SET chr = t1.chr
FROM t1
WHERE foo.id = t1.id
RETURNING foo.id, t1.chr, t1.oldchr
),
tinsert AS (
INSERT INTO foo (id, chr)
SELECT id, chr
FROM t1
WHERE t1.oldchr IS NULL
RETURNING id, chr, null::text
)
SELECT * FROM tupdate
UNION ALL
SELECT * FROM tinsert;
id | chr | oldchr
----+-----+--------
3 | M | C
4 | N | D
5 | O | E
6 | P |
7 | Q |
8 | R |
(6 rows)
That table above is returned by those CTEs. And, that's the output I'm looking for.