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 added code to set the default values for the 5 timestamp fields and now it works correctly.
I'm only a third of the way through the schema and I already have 30 tables with the same default which need to be updated.
Trying to find everywhere a record is added in code seems error-prone, so I want to stay with the current approach of using the column default.
PROBLEM:
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()'
returns 95 rows like
attrelid attname default_value
16398 AddDate now()
16407 AddDate now()
16421 AddDate now()
16433 Deposit_Date now()
16433 ArchDate now()
16473 AddDate now()
