Archive for October, 2011

4 October, 2011

This week’s find: CREATE OR REPLACE VIEW

by gorthx

How many times am I working with Postgres and say “I wish I could…” and I can? Lots. Here’s another one: CREATE OR REPLACE VIEW. As long as you’re not removing columns, or changing the datatype or name of the existing columns, you can use this. It’s great because it preserves rules, triggers, and perms, saving you from digging them out of your pg_dump and re-applying them. (Schema changes, whee.)

As of 8.4, thanks to Robert Haas, you can even add columns to the view (as long as they’re at the end):

— original view
testy=# CREATE VIEW view_cisco_routers AS
SELECT device_name
FROM devices
WHERE device_type = ‘Router’;

— then the specs change, and we want to include the IP address, too
testy=# CREATE OR REPLACE VIEW view_cisco_routers AS
SELECT device_name, device_ip
FROM devices
WHERE device_type = ‘Router’;

Usually I want to alter or remove columns, so CREATE OR REPLACE VIEW has a limited use case for me. However, I recently changed the datatype of a column* that I used in the WHERE clause for about 20 views, and CREATE OR REPLACE VIEW was definitely simpler (and therefore less error-prone) than dropping & re-creating them all.

* to ENUM, which I’ll write about in another week or so.

Tags: ,