On Wednesday, December 28, 2022, Brad White <b55white@xxxxxxxxx> 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 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()'
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.
----
I'm still suffering here.
I have 95 column defaults to update across most of my data tables.
I can generate ALTER statements, per David's sensible suggestion, but they fail because you have to drop all the views.
I created a script that would drop and re-create all the views, but all the table/column names are non-quoted which fails because all my tables/columns are mixed-case.
So I either need to
-- generate a script to re-create the views that works,
-- or parse my script, recognize all the relation names, force them to proper casing, and wrap in quotes, so I can drop and regenerate the views properly,
-- or alter the definition of the column defaults inplace in pg_catalog.pg_attrdef.
Altering the defaults seems safe because the default value shouldn't affect the view at all.
Thanks for any suggestions,
Brad.
