Re: Updating column default values in code

On timestamp fields, I need to update the column default from the current "Now()" to "LOCALTIMESTAMP(0)"

I could just manually make the change on every table, but they want the existing backups to still work. So I need something that I can build into my restore script.

I've gotten the list of tables and columns.

How do I update the relation?

SELECT a.attrelid, a.attname, pg_get_expr(d.adbin, d.adrelid) AS default_value
FROM   pg_catalog.pg_attribute AS a
JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum)
WHERE  NOT a.attisdropped           -- no dropped (dead) columns
AND    a.attnum   > 0               -- no system columns
AND pg_get_expr(d.adbin, d.adrelid) = 'now()'