CODEWITHSUNDEEP
databasepostgresql
membersound
membersound

Reputation: 86687

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

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?

Upvotes: 4

Views: 2609

Answers (2)

Vivek S.
Vivek S.

Reputation: 21895

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')

Upvotes: 2

user330315
user330315

Reputation:

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.

Upvotes: 6

Related Questions