Skip to content

Instantly share code, notes, and snippets.

@chochos
Created July 22, 2019 16:59
Show Gist options
  • Save chochos/1330ee92ce0298f560878a4d4a556d92 to your computer and use it in GitHub Desktop.
Save chochos/1330ee92ce0298f560878a4d4a556d92 to your computer and use it in GitHub Desktop.
Cannot alter a column used in a view, need to drop the view first.
CREATE TABLE foo(
some_id SERIAL PRIMARY KEY,
another INT,
stuff VARCHAR(20)
);
CREATE OR REPLACE FUNCTION insert_crap_into_foo() RETURNS INTERVAL AS
$$
DECLARE
counter INT := 0;
start TIMESTAMP := timeOfDay();
stop TIMESTAMP;
BEGIN
LOOP
counter := counter + 1;
INSERT INTO foo(another) VALUES(counter);
EXIT WHEN counter > 2000000;
END LOOP;
stop := timeOfDay();
RETURN stop - start;
END
$$
language plpgsql;
SELECT insert_crap_into_foo();
SELECT now() AS creating_view;
CREATE OR REPLACE VIEW bar AS SELECT * FROM foo;
SELECT now() AS view_created;
ALTER TABLE foo ADD more_stuff NUMERIC(10,2);
ALTER TABLE foo DROP more_stuff;
ALTER TABLE foo ALTER COLUMN stuff TYPE VARCHAR(50);
ALTER TABLE foo ALTER COLUMN another TYPE BIGINT; --This gives an error:
--ERROR: cannot alter type of a column used by a view or rule
--DETAIL: rule _RETURN on view bar depends on column "another"
--Need to drop the view in the meantime
DROP VIEW bar;
SELECT now() AS dropped;
ALTER TABLE foo ALTER COLUMN another TYPE BIGINT; --now it works
-- but, in a table with millions of rows, this alteration can take some time
-- the view won't exist until the change is done
SELECT now() AS created_again;
CREATE VIEW bar AS SELECT * FROM foo;
SELECT now() AS done;
DROP FUNCTION insert_crap_into_foo();
DROP VIEW bar;
DROP TABLE foo;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment