CODEWITHSUNDEEP
sqlpostgresql
csdev
csdev

Reputation: 323

postgresql - update, set DEFAULT in case statement

I get a syntax error when trying to update a table to using the DEFAULT keyword value inside a case statement, as follows:

UPDATE tbl
SET status =
     CASE
        WHEN ( st.status = '' ) THEN ( DEFAULT )
        ELSE ( ROUND( CAST(st.status as numeric) ) )
     END

FROM sourceTable st

Here is the error:

ERROR:  syntax error at or near "DEFAULT"
LINE 4: ...                  WHEN ( tc.status = '''' ) THEN ( DEFAULT )

Looking at the docs now for a while, and I cannot see how this violates any rules of statement construction.

Previous code does using NULL does work

UPDATE tbl
SET status =
     CASE
        WHEN ( st.status = '' ) THEN ( NULL )
        ELSE ( ROUND( CAST(st.status as numeric) ) )
     END

FROM sourceTable st

My goal is to update the column to a default value, or to NULL if there is an empty string in the source table column.

Using NULL worked fine but updated the column to NULL of course, and the change to DEFAULT I would think would work - can anyone help with a syntax correction or alternative?

This chunk of code is generated by something processing various columns in different scenarios, so not looking for any alternative approaches to the problem as such (though I am open to any response) - just looking for a fix if possible, or an explanation of why the syntax won't work. Thanks!

Upvotes: 3

Views: 8209

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562731

PostgreSQL documentation for UPDATE syntax seems to say that you can set a column either to an expression or to a bare word DEFAULT, but not to an expression containing the keyword DEFAULT.

SET column_name = { expression | DEFAULT }

This syntax reference means the right-hands-side of the = can be an expression, or it can be DEFAULT. The notation of { X | Y } means "either X or Y, pick exactly one."

The choice of using the DEFAULT keyword does not appear in the documentation for value expressions. DEFAULT can be used as a special-case assignment in INSERT or UPDATE but only as an alternative to a value expression, not as part of a more complex expression.

To get around this, you'll probably have to read the DEFAULT value for the column from information_schema.columns.column_default for the respective table and column, then use the value literally in your UPDATE statement.

Upvotes: 4

Related Questions