Drop table if it has less than n records

Question

I am experimenting with postgres and can't get this simple query to work:

drop table mytable if (select count(*) from mytable)<50 ;

This gives error:

ERROR:  syntax error at or near "if"
LINE 1: drop table tablename if (select count(*) from mytable)<50 ;

How do you alter/drop tables in postgres for a given condition?

Postgres doesn't support a `WHERE` or `IF` clause on `DROP TABLE`: http://www.postgresql.org/docs/current/static/sql-droptable.html. — Gordon Linoff, Mar 21 '16 at 18:42
Why do you want to? But to answer your question put the `drop` AFTER the `if`. And start reading the documentation before asking strangers on the internet. — D Stanley, Mar 21 '16 at 18:42

score 0 · Answer 1 · edited May 23 '17 at 12:15

0

Creating dynamic SQL works for this (See answer) -

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 nsp.nspname = 'public';
  if l_count < 50 then 
    execute 'drop table mytable';
  end if;
end;
$$

edited May 23 '17 at 12:15

Community

1
1

answered Mar 21 '16 at 18:57

goelakash

2,502
4
40
56

Drop table if it has less than n records

1 Answers1