Listing Invalid Views: Quick Tip

Jim Knicely authored this tip. If any of the tables referenced in a view are dropped, the view becomes invalid. Example: dbadmin=> CREATE TABLE base_table (c INT); CREATE TABLE dbadmin=> INSERT INTO base_table SELECT 1; OUTPUT ——– 1 (1 row) dbadmin=> CREATE VIEW base_table_vw AS SELECT c FROM base_table; CREATE VIEW dbadmin=> SELECT * FROM base_table_vw; c — 1 (1 row) dbadmin=> DROP TABLE base_table; DROP TABLE dbadmin=> SELECT * FROM base_table_vw; ERROR 4568: Relation “public.base_table” does not exist You can list all of the invalid views in your database by passing a bogus search string (i.e., a view that does not exist) to the \dv vsql meta command! Example: dbadmin=> \dv this_is_a_bogus_search_string WARNING 3791: Invalid view base_table_vw: Relation "public.base_table" does not exist No matching relations found.