pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (7+ messages)  latest in thread

Message-ID: <a106628e-03e7-ce50-d68f-fe27e608668e@gmail.com>
Permalink:  ../a106628e-03e7-ce50-d68f-fe27e608668e@gmail.com/
Also on:    postgresql.org/message-id/a106628e-03e7-ce50-d68f-fe27e608668e@gmail.com

 · 

reply

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