Re: Updating column default values in code - Mailing list pgsql-general
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;
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;
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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
pgsql-general by date: