On Wednesday, December 28, 2022, Brad White <
b55white@gmail.com> wrote:
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 then we would still fail if we ever needed to restore a database. So I need something that I can build into my Powershell restore script.
I've gotten the list of columns and, I think, table IDs.
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()'
Extend the query so all inputs needed to manually write an ALTER TABLE command (chiefly, you need textual names, not oids). Then use format() to actually write those commands using the query as input. You can use psql \gexec to actually execute those queries, though other options exist depending on what tools you are comfortable with).
David J.