Re: Updating column default values in code

On Wednesday, December 28, 2022, Brad White <b55white(at)gmail(dot)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.