4

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,

  1. id
  2. The old value of chr
  3. 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.

6

You can use merge with the output clause.

Temp table:

create table #foo
(
  id int unique,
  chr char(1)
);

insert into #foo(id, chr) values
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E');

Merge:

with t1 as
(
  select T.id, 
         T.chr
  from (values (3, 'M'),
               (4, 'N'),
               (5, 'O'),
               (6, 'P'),
               (7, 'Q'),
               (8, 'R')) as T(id, chr)
)
merge #foo as T
using t1 as S
on T.id = S.id
when not matched then
  insert (id, chr) values(S.id, S.chr)
when matched then
  update set chr = S.chr
output inserted.id,
       inserted.chr,
       deleted.chr as oldchr;

Result:

id          chr  oldchr
----------- ---- ------
3           M    C
4           N    D
5           O    E
6           P    NULL
7           Q    NULL
8           R    NULL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.