pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Updating column default values in code
7+ messages / 5 participants
[nested] [flat]

* Updating column default values in code
@ 2022-12-29 00:15 Brad White <b55white@gmail.com>
  2022-12-29 00:46 ` Re: Updating column default values in code David G. Johnston <david.g.johnston@gmail.com>
  2022-12-29 01:10 ` Re: Updating column default values in code Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Brad White @ 2022-12-29 00:15 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Updating column default values in code
  2022-12-29 00:15 Updating column default values in code Brad White <b55white@gmail.com>
@ 2022-12-29 00:46 ` David G. Johnston <david.g.johnston@gmail.com>
  1 sibling, 0 replies; 7+ messages in thread

From: David G. Johnston @ 2022-12-29 00:46 UTC (permalink / raw)
  To: Brad White <b55white@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Updating column default values in code
  2022-12-29 00:15 Updating column default values in code Brad White <b55white@gmail.com>
@ 2022-12-29 01:10 ` Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Adrian Klaver @ 2022-12-29 01:10 UTC (permalink / raw)
  To: Brad White <b55white@gmail.com>; pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Updating column default values in code
@ 2023-01-06 23:31 Brad White <b55white@gmail.com>
  2023-01-07 01:44 ` Re: Updating column default values in code Ken Tanzer <ken.tanzer@gmail.com>
  2023-01-07 05:13 ` Re: Updating column default values in code Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 7+ messages in thread

From: Brad White @ 2023-01-06 23:31 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Updating column default values in code
  2023-01-06 23:31 Re: Updating column default values in code Brad White <b55white@gmail.com>
@ 2023-01-07 01:44 ` Ken Tanzer <ken.tanzer@gmail.com>
  2023-01-07 04:14   ` Re: Updating column default values in code Brad White <b55white@gmail.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Ken Tanzer @ 2023-01-07 01:44 UTC (permalink / raw)
  To: Brad White <b55white@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
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.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Updating column default values in code
  2023-01-06 23:31 Re: Updating column default values in code Brad White <b55white@gmail.com>
  2023-01-07 01:44 ` Re: Updating column default values in code Ken Tanzer <ken.tanzer@gmail.com>
@ 2023-01-07 04:14   ` Brad White <b55white@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Brad White @ 2023-01-07 04:14 UTC (permalink / raw)
  To: Ken Tanzer <ken.tanzer@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Updating column default values in code
  2023-01-06 23:31 Re: Updating column default values in code Brad White <b55white@gmail.com>
@ 2023-01-07 05:13 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 7+ messages in thread

From: Tom Lane @ 2023-01-07 05:13 UTC (permalink / raw)
  To: Brad White <b55white@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2023-01-07 05:13 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-29 00:15 Updating column default values in code Brad White <b55white@gmail.com>
2022-12-29 00:46 ` David G. Johnston <david.g.johnston@gmail.com>
2022-12-29 01:10 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-01-06 23:31 Re: Updating column default values in code Brad White <b55white@gmail.com>
2023-01-07 01:44 ` Ken Tanzer <ken.tanzer@gmail.com>
2023-01-07 04:14   ` Brad White <b55white@gmail.com>
2023-01-07 05:13 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox