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?

GMB · Accepted Answer · 2020-10-04 14:11:39Z

3

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.

answered Oct 4, 2020 at 14:11

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SladeX Zengimana Over a year ago

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?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related