Re: Updating column default values in code

On 12/28/22 16:15, Brad White wrote:
> RECAP
>
> I'm running an Access front end against the Postgres back end.
>
> Copying and updating a record succeeds in 9.4 but fails in 9.5 and
> everything after.
>
> It was the precision of the timestamp fields after all.
>
> Turns out the initial data wasn't coming from Access, but from the
> field default value of "now()"
>
> They must have added additional checking between 9.4 and 9.5.    8: -)

I saw this behavior from long before 9.4 so I tend to doubt it is the
9.4 --> 9.5 change alone.

My guess is it would be in the ODBC driver. Or a change in Access version.

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

Or now()::timestamp(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.

Not following. If you change the column defaults and do a pg_dump of the
database the new defaults will be there in the restore. Maybe a further
explanation of what "... Powershell restore script" means?

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com