PosgtreSQL - update column if its value equals to DEFAULT value

user_1130231 0
Viewed 79

I have a PostgreSQL table with default values for columns that were not specified in the insert query. How can I run query to update column value if its current value is equal to DEFAULT?

In MySQL, I have

UPDATE t SET foo = 'foo' WHERE foo = DEFAULT(foo);

but it is not working in PosgtreSQL.

sql-update postgresql
2 Answers

There is no way to do that in PostgreSQL, and I cannot imagine how such a feature could work at all.

Imagine the column is defined as

foo timestamp DEFAULT localtimestamp

How could you tell if the timestamp that has been inserted happened to be the default value or not?

user_64643080

You can get the default value of a column from the column_default column of information_schema.columns.

For columns defined as VARCHAR or CHAR this value is returned as 'somevalue'::character varying or 'somevalue'::bpchar respectively, so you must take the part before :: and trim the single quotes:

UPDATE t
SET foo = 'new value'
WHERE foo = (
  SELECT TRIM('''' FROM SPLIT_PART(column_default, '::', 1))
  FROM information_schema.columns 
  WHERE table_name = 't' AND column_name = 'foo'
)

See a simplified demo.

user_104988280
Related