Updating column default values in code

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.

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 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.

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
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

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.

--
Quote Signature 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

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