How to delete 1 table if another table exists in PostgreSQL using IF-statements

Question

I'm trying to write a query that deletes/drops a table, if another table exists:

DO
$do$
BEGIN
   IF  EXISTS (SELECT FROM table1) THEN
      DELETE  FROM table2;
    END IF;
END
$do$;

but all that it is doing is deleting rows from table1 if table2 exists rather than the table itself.

Can you use a function similar to the one above or should I use drop table if exists?

Answer 1

delete/drop a table if another table exists

That would be:

do $$
begin
    if exists(select 1 from information_schema.tables where table_schema = current_schema() and table_name = 'table1') then
    drop table table2;
  end if;
end; $$ language plpgsql;

Rationale:

• selecting from a table is not the right way to check if it exists - if it doesn't, an error is raised. Instead, you can query the information schema.

• to remove a table, you want drop table; delete, on the other hand, removes the content of the table, not the table itself.