Post about "safely" renaming columns

Create dummy table and real data

cyberdyne=> CREATE TABLE foo1 ( bar SERIAL PRIMARY KEY, baz JSON, quux TEXT );
CREATE TABLE
cyberdyne=> INSERT INTO foo1 (baz, quux) VALUES ('{}', ''), ('{"service_name":null}', '--'), ('{"service_name":""}', 'minus'), ('{"service_name":"jeff"}', '++'), ('{"service_name":{"nested":10}}', 'postfix');
INSERT 0 5
cyberdyne=> SELECT * FROM foo1;
bar |              baz               |  quux
-----+--------------------------------+---------
   1 | {}                             |
   2 | {"service_name":null}          | --
   3 | {"service_name":""}            | minus
   4 | {"service_name":"jeff"}        | ++
   5 | {"service_name":{"nested":10}} | postfix
(5 rows)

Replace the table with a view; the view is identical for now:

cyberdyne=> BEGIN;
BEGIN
cyberdyne=*> ALTER TABLE foo1 RENAME TO foo2;
ALTER TABLE
cyberdyne=*> CREATE VIEW foo1 AS SELECT bar, baz, quux FROM foo2;
ALTER VIEW
cyberdyne=*> COMMIT;
COMMIT

Make sure the view acts like the table

cyberdyne=> INSERT INTO foo1 (baz, quux) VALUES ('{"galaxy":10}', 'scale'), ('{"reg":"x"}', 'verbose');
INSERT 0 2
cyberdyne=> SELECT * FROM foo1;
 bar |              baz               |  quux
-----+--------------------------------+---------
   1 | {}                             |
   2 | {"service_name":null}          | --
   3 | {"service_name":""}            | minus
   4 | {"service_name":"jeff"}        | ++
   5 | {"service_name":{"nested":10}} | postfix
   6 | {"galaxy":10}                  | scale
   7 | {"reg":"x"}                    | verbose
(7 rows)

cyberdyne=> SELECT * FROM foo2;
 bar |              baz               |  quux
-----+--------------------------------+---------
   1 | {}                             |
   2 | {"service_name":null}          | --
   3 | {"service_name":""}            | minus
   4 | {"service_name":"jeff"}        | ++
   5 | {"service_name":{"nested":10}} | postfix
   6 | {"galaxy":10}                  | scale
   7 | {"reg":"x"}                    | verbose
(7 rows)

Observe the columns in our view before and after renaming a column in the table

cyberdyne=> \d+ foo1
                          View "cyberdyne.foo1"
 Column |  Type   | Collation | Nullable | Default | Storage  | Description
--------+---------+-----------+----------+---------+----------+-------------
 bar    | integer |           |          |         | plain    |
 baz    | json    |           |          |         | extended |
 quux   | text    |           |          |         | extended |
View definition:
 SELECT foo2.bar,
    foo2.baz,
    foo2.quux
   FROM foo2;

cyberdyne=> ALTER TABLE foo2 RENAME COLUMN quux TO comment;
ALTER TABLE
cyberdyne=> \d+ foo1
                          View "cyberdyne.foo1"
 Column |  Type   | Collation | Nullable | Default | Storage  | Description
--------+---------+-----------+----------+---------+----------+-------------
 bar    | integer |           |          |         | plain    |
 baz    | json    |           |          |         | extended |
 quux   | text    |           |          |         | extended |
View definition:
 SELECT foo2.bar,
    foo2.baz,
    foo2.comment AS quux
   FROM foo2;

Then observe the data

cyberdyne=> SELECT * FROM foo1;
 bar |              baz               |  quux
-----+--------------------------------+---------
   1 | {}                             |
   2 | {"service_name":null}          | --
   3 | {"service_name":""}            | minus
   4 | {"service_name":"jeff"}        | ++
   5 | {"service_name":{"nested":10}} | postfix
   6 | {"galaxy":10}                  | scale
   7 | {"reg":"x"}                    | verbose
(7 rows)

cyberdyne=> SELECT * FROM foo2;
 bar |              baz               | comment
-----+--------------------------------+---------
   1 | {}                             |
   2 | {"service_name":null}          | --
   3 | {"service_name":""}            | minus
   4 | {"service_name":"jeff"}        | ++
   5 | {"service_name":{"nested":10}} | postfix
   6 | {"galaxy":10}                  | scale
   7 | {"reg":"x"}                    | verbose
(7 rows)

Now recreate the view to have both the old name and the new name for easy migration (applications using SELECT * will have bad time)

cyberdyne=> BEGIN;
BEGIN
cyberdyne=*> DROP VIEW foo1;
DROP VIEW
cyberdyne=*> CREATE VIEW foo1 AS SELECT bar, baz, comment AS quux, comment FROM foo2;
CREATE VIEW
cyberdyne=*> COMMIT;
COMMIT
cyberdyne=> SELECT * FROM foo1;
 bar |              baz               |  quux   | comment
-----+--------------------------------+---------+---------
   1 | {}                             |         |
   2 | {"service_name":null}          | --      | --
   3 | {"service_name":""}            | minus   | minus
   4 | {"service_name":"jeff"}        | ++      | ++
   5 | {"service_name":{"nested":10}} | postfix | postfix
   6 | {"galaxy":10}                  | scale   | scale
   7 | {"reg":"x"}                    | verbose | verbose
(7 rows)

Once the application has been updated to change all queries to use comment instead of quux, get rid of the view and rename the table back to the original name:

cyberdyne=> BEGIN;
BEGIN
cyberdyne=*> DROP VIEW foo1;
DROP VIEW
cyberdyne=*> ALTER TABLE foo2 RENAME TO foo1;
ALTER TABLE
cyberdyne=*> COMMIT;
COMMIT
cyberdyne=> SELECT * FROM foo1;
 bar |              baz               | comment
-----+--------------------------------+---------
   1 | {}                             |
   2 | {"service_name":null}          | --
   3 | {"service_name":""}            | minus
   4 | {"service_name":"jeff"}        | ++
   5 | {"service_name":{"nested":10}} | postfix
   6 | {"galaxy":10}                  | scale
   7 | {"reg":"x"}                    | verbose
(7 rows)

dhermes / bossylobster-blog

Post about "safely" renaming columns #87