Search Postgresql Archives

Updating column default values in code

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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()


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux