Re: Updating column default values in code

[Brad White <b55white@xxxxxxxxx>]

Title: Quote Signature

On 1/6/2023 7:44 PM, Ken Tanzer wrote:

On Fri, Jan 6, 2023 at 3:32 PM Brad White <b55white@xxxxxxxxx> 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@xxxxxxxxxxxxx
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