YicSCRVc

1WITH
2    update_cte AS (
3        UPDATE t SET v = $1 WHERE id = $2 RETURNING 'updated'::text status
4    ),
5    insert_cte AS (
6        INSERT INTO t(id, v) SELECT $2, $1 WHERE NOT EXISTS
7            (SELECT 1 FROM update_cte) RETURNING 'inserted'::text status
8    )
9 (SELECT status FROM update_cte) UNION (SELECT status FROM insert_cte)
10	
11CREATE TABLE t(id INT PRIMARY KEY, v TEXT);
12
13INSERT INTO t (id, v)
14    SELECT $1, $2
15    WHERE 'inserted' = set_config('upsert.action', 'inserted', true)
16    ON CONFLICT (id) DO UPDATE
17        SET v = EXCLUDED.v
18        WHERE 'updated' = set_config('upsert.action', 'updated', true)
19RETURNING current_setting('upsert.action') AS "upsert.action";
20	
21DROP TABLE IF EXISTS tab;
22
23CREATE TABLE tab(id INT PRIMARY KEY, col VARCHAR(100),
24                 is_updated BOOLEAN DEFAULT false);
25INSERT INTO tab(id, col) VALUES (1,'a'), (2, 'b');
26
27
28-- main query
29INSERT INTO tab(id, col)
30VALUES (3, 'c'), (4, 'd'), (1,'aaaa')
31ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col, is_updated = true
32RETURNING id,col,
33          CASE WHEN is_updated THEN 'UPDATED' ELSE 'INSERTED' END AS action;
34	
35╔════╦══════╦══════════╗
36â•‘ id â•‘ col  â•‘  action  â•‘
37╠════╬══════╬══════════╣
38â•‘  3 â•‘ c    â•‘ INSERTED â•‘
39â•‘  4 â•‘ d    â•‘ INSERTED â•‘
40â•‘  1 â•‘ aaaa â•‘ UPDATED  â•‘
41╚════╩══════╩══════════╝
42	
43so=# DROP TABLE IF EXISTS tab;
44NOTICE:  table "tab" does not exist, skipping
45DROP TABLE
46so=# CREATE TABLE tab(id INT PRIMARY KEY, col text);
47CREATE TABLE
48so=# INSERT INTO tab(id, col) VALUES (1,'a'), (2, 'b');
49INSERT 0 2
50so=# INSERT INTO tab(id, col)
51VALUES (3, 'c'), (4, 'd'), (1,'aaaa')
52ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col
53returning *,case when xmax::text::int > 0 then 'updated' else 'inserted' end,ctid;
54 id | col  |   case   | ctid
55----+------+----------+-------
56  3 | c    | inserted | (0,3)
57  4 | d    | inserted | (0,4)
58  1 | aaaa | updated  | (0,5)
59(3 rows)
60
61INSERT 0 3
62so=# INSERT INTO tab(id, col)
63VALUES (3, 'c'), (4, 'd'), (1,'aaaa')
64ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col
65returning *,case when xmax::text::int > 0 then 'updated' else 'inserted' end,ctid;
66 id | col  |  case   | ctid
67----+------+---------+-------
68  3 | c    | updated | (0,6)
69  4 | d    | updated | (0,7)
70  1 | aaaa | updated | (0,8)
71(3 rows)
72
73INSERT 0 3