2

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?

GMB
  • 216,147
  • 25
  • 84
  • 135
SladeX Zengimana
  • 33
  • 8

1 Answers1

2

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • yes! that works wonderfully, just needed to add quotation marks around information_schema and tables! thx. im wondering tho, what does the 1 after select mean? – SladeX Zengimana Oct 04 '20 at 14:42