How can I find a table's views in PostgreSQL?

Question

I need to find all view based on table my_table. I try query

select *
from information_schema.views
where view_definition ilike '%my_table%'

and get empty table. That means I have no view based on my_table. But after trying to delete my_table I get an error "cannot drop table my_table because other objects depend on it". I look more carefully with

select *
from information_schema.views
where table_name = 'my_view'

and find row with view based on my table with NULL in view_definition.

Why definition can be NULL? Is there any another way to find view depends on table?

Views **are not** the only database objects that depend upon tables.The most common being Foreign keys. See [Find dependent objects](https://stackoverflow.com/questions/4462908/find-dependent-objects-for-a-table-or-view). — Belayer, Jan 31 '23 at 05:51

score 0 · Answer 1 · answered Jan 31 '23 at 06:25

0

Read the "detail" message that comes with the error message. It will tell you what objects cause the problem. Those are the dependent objects that prevent the table from being dropped.

To drop all these dependencies, you can use DROP TABLE ... CASCADE, like the hint to the error message says.

answered Jan 31 '23 at 06:25

Laurenz Albe

209,280
17
206
263

Yes, I read the detail, but I don't need to delete table if something depends on it. I have a list of table to delete, but can delete only unusable of it. – yuoggy Jan 31 '23 at 06:30
1

So there is no problem. Great! – Laurenz Albe Jan 31 '23 at 06:31

score -1 · Answer 2 · answered Jan 31 '23 at 07:12

Thats what I needed. As I say, I have a list of tables and there are more than 30 elements. It is not directly answer on my question, but it solve the problem

do $$
declare names text[] := array['my_table', 'my_table1', 'my_table2'];

declare i int4;
begin
    for i in 1..3 loop
        begin
        
        execute 'drop table if exists my_schema.' || names[i];
        
        EXCEPTION WHEN OTHERS then
            -- nothing
        end;
    end loop;

end;
$$;

How can I find a table's views in PostgreSQL?

2 Answers2