How to drop a table based on IF condition in postgres?

Question

I'm trying to drop a table on startup based on a condition:

IF NOT EXISTS (select * from pg_class where relname = 'mytable' and relpersistence = 'u') 
DROP TABLE IF EXISTS mytable

Result: Syntaxerror at 'IF', SQL state: 42601. Why? How can I drop a table based on a condition, if I'm not allowed to use IF?

`drop table if exists` drops the table only if it exists so you can drop the `if not exists ... ` — A ツ, Apr 28 '15 at 08:15
I only want to drop the table if it is in state `UNLOGGED`, thus I indeed need the prior select... — membersound, Apr 28 '15 at 08:19

score 6 · Accepted Answer · 2015-04-28T08:45:08.703

6

IF can't be used in SQL, this is only valid for PL/pgSQL.

You need to do this with dynamic SQL inside an anonymous PL/pgSQL block. Something like:

do
$$
declare
  l_count integer;
begin
  select count(*)
     into l_count
  from pg_class c
    join pg_namespace nsp on c.relnamespace = nsp.oid
  where c.relname = 'mytable' 
    and c.relpersistence = 'u'
    and nsp.nspname = 'public';

  if l_count = 1 then 
    execute 'drop table mytable';
  end if;

end;
$$

You probably should extend the select statement to join against pg_namespace and include the schema name in your where condition to make sure you are not accidently dropping a table from the wrong schema.

edited Apr 28 '15 at 08:45

answered Apr 28 '15 at 08:28

OK I see, thanks a lot. How would I create the join? `select * from pg_namespace` would give me the following: `nspname nspowner nspacl` `public 10 "{postgres=UC/postgres,=UC/postgres}"`, I of course only want to perform the drop on the "public" schema. – membersound Apr 28 '15 at 08:37
1

Can't this be made shorter, IF EXISTS (SELECT 1 FROM pg_class....) THEN EXECUTE......? – Jayadevan Apr 28 '15 at 09:57

score 2 · Answer 2 · edited May 23 '17 at 12:14

Already accepted answer by a_horse_with_no_name will works though You can create a Custom Function if you want to use the same task for other tables in future, so you should create a function like below:

create or replace function drop_table (_tbl varchar) returns void as
$$
begin
if exists(select 1
          from pg_class c
          join pg_namespace nsp on c.relnamespace = nsp.oid
          where c.relname = ''||_tbl||'' 
            and c.relpersistence = 'u'
            and nsp.nspname = 'public') then
  execute format('DROP TABLE %s',_tbl);
  raise notice 'Table %s Deleted',_tbl;
else
  raise notice 'Table %s Not Deleted',_tbl;
end if;
end;
$$
language plpgsql

and simply call this function whenever you want

select drop_table('mytable');

or

select drop_table('mytable_1')

How to drop a table based on IF condition in postgres?

2 Answers2

Linked