Currently using PostgreSQL 16.4. I looked at this post but it didn’t cover my use case.
I would like to conditionally update two (or more) columns with the same condition, so I don’t have to rewrite it. Here is a very simple example, of course my condition is a lot more complex than TRUE.
create table test (a INT, b INT);
I could do this but the condition is repeated.
update test SET a = CASE WHEN TRUE THEN 1 END, b = CASE WHEN TRUE THEN 2 END;
Ideally I would do this, but I get an error:
update test SET (a, b) = (CASE WHEN TRUE THEN (1, 2) END);
ERROR: source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression
If I do change into a subquery, I also get an error:
update test SET (a, b) = (SELECT CASE WHEN TRUE THEN (1, 2) END);
ERROR: number of columns does not match number of values
How can I write my subquery so that the update considers the select’s output as 2 values? If I run this SELECT by itself I do get 2 values so I feel I’m missing some explicit cast somewhere.
SELECT CASE WHEN TRUE THEN (1, 2) END yields 1,2 of type RECORD
2
Create and populate test:
DROP TABLE IF EXISTS test;
CREATE TABLE test (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
a INT,
b INT
);
INSERT INTO
test (a, b)
VALUES
(0, 1),
(11, 12);
SELECT *
FROM test
ORDER BY id;
The initial contents of test are:
| id | a | b |
|---|---|---|
| 1 | 0 | 1 |
| 2 | 11 | 12 |
The simplest case is where the requirement is to assign values when a condition is met and otherwise set the columns to NULL.
The following command assigns 1 to a and 2 to b if a is even:
UPDATE test
SET (a, b) = (SELECT 1, 2 WHERE a % 2 = 0);
The contents of test are then:
| id | a | b |
|---|---|---|
| 1 | 1 | 2 |
| 2 | NULL | NULL |
A more complicated case occurs when the requirement is to assign alternate values depending on the evaluation of a condition. The next command demonstrates an approach to handling such a case. After restoring test to its initial contents, run the following command to swap a and b when a is even and otherwise increment both a and b:
UPDATE test
SET
(a, b) = (
SELECT
a,
b
FROM (SELECT a % 2 = 0 AS met) c
CROSS JOIN LATERAL (SELECT b AS a, a AS b
WHERE c.met
UNION ALL
SELECT a + 1 AS a, b + 1 AS b
WHERE NOT c.met) n);
The contents of test will then be:
| id | a | b |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 12 | 13 |
Capturing the result of evaluating the conditional expression eliminates the need to repeat the expression which makes the code more maintainable and more efficient.
UNION ALL is used because it is guaranteed that only one of the two subqueries will return a row.