Обсуждение: Updating column default values in code

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

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.klaver@aklaver.com

On Fri, Jan 6, 2023 at 3:32 PM Brad White <b55white@gmail.com> wrote:

I can generate ALTER statements, per David's sensible suggestion, but they fail because you have to drop all the views. 

 

Altering the defaults seems safe because the default value shouldn't affect the view at all.

Are you sure those alter statements will fail?  I do that frequently.  Plus would expect it to work because of your second statement.

Here's an example, running on 9.6:

CREATE TABLE foo (f1 integer DEFAULT 1, f2 integer);
CREATE VIEW foo_view AS SELECT * FROM foo;
ALTER TABLE foo ALTER COLUMN f1 SET DEFAULT 3;
ALTER TABLE foo ALTER COLUMN f2 SET DEFAULT 2;

agency=> BEGIN;
BEGIN
agency=> CREATE TABLE foo (f1 integer DEFAULT 1, f2 integer);
CREATE TABLE
agency=> CREATE VIEW foo_view AS SELECT * FROM foo;
CREATE VIEW
agency=> ALTER TABLE foo ALTER COLUMN f1 SET DEFAULT 3;
ALTER TABLE
agency=> ALTER TABLE foo ALTER COLUMN f2 SET DEFAULT 2;

ALTER TABLE 

Cheers,

Ken

--

That's a good point.

It was failing when I was trying to change the field type, before I stumbled on the real issue of the default values.

I realize now that I assumed and didn't try to update just the defaults.

Thanks,
Brad.

Brad White <b55white@gmail.com> writes:
> 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'm still suffering here.

I don't understand why a script to generate
    ALTER VIEW v ALTER COLUMN c SET DEFAULT LOCALTIMESTAMP(0)
commands doesn't do what you need?

            regards, tom lane