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?

Answer 1

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.