Re: Updating column default values in code

On 1/6/2023 7:44 PM, Ken Tanzer wrote:
>
>
> On Fri, Jan 6, 2023 at 3:32 PM Brad White <b55white(at)gmail(dot)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(at)inebraska(dot)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