From: Brad White <b55white@gmail.com>
To: Ken Tanzer <ken.tanzer@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Updating column default values in code
Date: Fri, 6 Jan 2023 22:14:47 -0600
Message-ID: <a106628e-03e7-ce50-d68f-fe27e608668e@gmail.com> (raw)
In-Reply-To: <CAD3a31VXkMh9b=hWrCW8+r-ygm3iFywXH0kHYBFEY8E+_7_hWw@mail.gmail.com>
References: <CAA_1=92yKUquYzzevA6XMu9e+9wufL=W4jfu6Mm8m6emV2BnMw@mail.gmail.com>
<CAD3a31VXkMh9b=hWrCW8+r-ygm3iFywXH0kHYBFEY8E+_7_hWw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: b55white@gmail.com, ken.tanzer@gmail.com
Subject: Re: Updating column default values in code
In-Reply-To: <a106628e-03e7-ce50-d68f-fe27e608668e@gmail.com>
References: <CAA_1=92yKUquYzzevA6XMu9e+9wufL=W4jfu6Mm8m6emV2BnMw@mail.gmail.com> <CAD3a31VXkMh9b=hWrCW8+r-ygm3iFywXH0kHYBFEY8E+_7_hWw@mail.gmail.com> <a106628e-03e7-ce50-d68f-fe27e608668e@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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