How to drop a table based on IF state in postgres?
I am 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
. What for? How can I drop a table based on a condition if I am not allowed to use IF
?
source to share
IF
cannot be used in SQL, this is only true for PL / pgSQL.
You need to do it with dynamic SQL inside a PL / pgSQL anonymous 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 should probably extend the operator select
to join pg_namespace
and include the schema name in your condition to make sure you don't accidentally drop the table from the wrong schema.
source to share
The already accepted answer a_horse_with_no_name will work, although you can create a custom function if you want to use the same task for other tables in the future, so you should create as 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 just call this function whenever you want
select drop_table('mytable');
or
select drop_table('mytable_1')
source to share