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?
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.
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.
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')
drop table if existsdrops the table only if it exists so you can drop theif not exists ...UNLOGGED, thus I indeed need the prior select...