Using DEFAULT in an INSERT/UPDATE statement vs CASE

Question

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?

Your options are an expression or the magic word `DEFAULT`. `DEFAULT` can't appear in arbitrary positions inside of an expression. — Damien_The_Unbeliever, Jul 01 '14 at 08:17
No. Just like you can't do `order by case when true then asc else desc end`. It's part of the syntax, not a function — Bohemian, Jul 01 '14 at 08:20
So there is no way to overcome this? I know I can explicitly state the value instead of using `DEFAULT` but it's unelegant. I can also use a stored procedure with its control structures but isn't that a little overkill for a simple update? — Przemek, Jul 01 '14 at 08:29

score 2 · Accepted Answer · edited May 23 '17 at 11:49

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.

Using DEFAULT in an INSERT/UPDATE statement vs CASE

1 Answers1