CODEWITHSUNDEEP
sqlpostgresql
Przemek
Przemek

Reputation: 6700

Using DEFAULT in an INSERT/UPDATE statement vs CASE

I have a column with a default value set. An UPDATE statement like this will work as expected:

UPDATE some_table SET
  some_column = DEFAULT
WHERE id = 1;

However, when I try to use a condition like this, i get a syntax error near DEFAULT:

UPDATE some_table SET
  some_column = CASE
    WHEN TRUE THEN DEFAULT
    ELSE 'foo'
  END
WHERE id = 1;

Is there a valid way to achieve the expected result?

Upvotes: 2

Views: 567

Answers (1)

pozs
pozs

Reputation: 36264

You cannot do that. You can use an expression there or the DEFAULT keyword.

SET { column = { expression | DEFAULT } | ... }

However, you can split your query into 2 updates:

UPDATE some_table
SET some_column = DEFAULT
WHERE id = 1 AND some_condition;

UPDATE some_table
SET some_column = 'foo'
WHERE id = 1 AND NOT some_condition;

This will do the desired changes. If you really want to do it in one query, you can use CTEs:

WITH upd1 AS (
  UPDATE some_table
  SET some_column = DEFAULT
  WHERE id = 1 AND some_condition
  RETURNING *
),
upd2 AS (
  UPDATE some_table
  SET some_column = 'foo'
  WHERE id = 1
  RETURNING *
)
SELECT * FROM upd1
UNION ALL
SELECT * FROM upd2

Note: the AND NOT some_condition skipped intentionally. The last CTE can work exactly as the ELSE branch in the CASE expression, because if you use multiple data modifying CTEs, maximum one of them can affect every row within a table.

Upvotes: 2

Related Questions