如何在Postgres中基于IF条件删除表？ [英] 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

结果：语法错误在IF，SQL状态：42601 。为什么？如果我不允许使用 IF ？

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 不能在SQL中使用，这只对PL / pgSQL有效。

IF can't be used in SQL, this is only valid for PL/pgSQL.

您需要在匿名PL / pgSQL块中使用动态SQL。像：

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;
$$

您可能应该扩展 select 语句加入 pg_namespace ，并在您的where条件中包含模式名称，以确保您不会意外地从错误的模式删除表。

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.

这篇关于如何在Postgres中基于IF条件删除表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！