Postgres Pro
Downloads
Home   >   mailing lists

Thread: Updating column default values in code

Updating column default values in code

From
Brad White
Date:

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

Re: Updating column default values in code

From
"David G. Johnston"
Date:
On Wednesday, December 28, 2022, Brad White <b55white@gmail.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.

Re: Updating column default values in code

From
Adrian Klaver
Date:
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

Re: Updating column default values in code

From
Brad White
Date:
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 could just manually make the change on every table, but they want the existing backups to still work. So I need something that I can build into my restore script.

I've gotten the list of tables and columns.

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.
----
I'm still suffering here.
I have 95 column defaults to update across most of my data tables.

I can generate ALTER statements, per David's sensible suggestion, but they fail because you have to drop all the views. 
I created a script that would drop and re-create all the views, but all the table/column names are non-quoted which fails because all my tables/columns are mixed-case.

So I either need to 

-- generate a script to re-create the views that works, 

-- or parse my script, recognize all the relation names, force them to proper casing, and wrap in quotes, so I can drop and regenerate the views properly, 

-- or alter the definition of the column defaults inplace in pg_catalog.pg_attrdef.

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

Thanks for any suggestions,
Brad.

Re: Updating column default values in code

From
Ken Tanzer
Date:


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

--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Updating column default values in code

From
Brad White
Date:
On 1/6/2023 7:44 PM, Ken Tanzer wrote:


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.

--
I talk with clients, find out where their pain points are, and solve those.
On-call IT Management for small companies and non-profits.
SCP, Inc.
bwhite@inebraska.com
402-601-7990


 Quote of the Day
   There is a huge difference between fathering a child and being a father.
   One produces a child. The other produces an adult.
    -- John Eldredge

Re: Updating column default values in code

From
Tom Lane
Date:
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