PostgreSQL如果不存在则创建表

Question

在MySQL脚本中你可以写：

CREATE TABLE IF NOT EXISTS foo ...;

...其他内容...

然后，您可以多次运行脚本而无需重新创建表。

在 PostgreSQL 中，如何实现这一点？

- peter2108

Answer 1

这个功能已经在Postgres 9.1中实现：

CREATE TABLE IF NOT EXISTS myschema.mytable (i integer);

对于较旧的版本，这里是一个解决方法的函数：

CREATE OR REPLACE FUNCTION create_mytable()
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF EXISTS (SELECT FROM pg_catalog.pg_tables 
              WHERE  schemaname = 'myschema'
              AND    tablename  = 'mytable') THEN
      RAISE NOTICE 'Table myschema.mytable already exists.';
   ELSE
      CREATE TABLE myschema.mytable (i integer);
   END IF;
END
$func$;

调用：

SELECT create_mytable();        -- call as many times as you want. 

注意事项

pg_tables中的schemaname和tablename大小写敏感。如果在CREATE TABLE语句中使用双引号标识符，则需要使用完全相同的拼写。否则，您需要使用小写字符串。详情请参见：

• PostgreSQL列名是否大小写敏感？

pg_tables仅包含实际的表。标识符可能仍然被相关对象占用。详情请参见：

• 如何检查给定模式中是否存在一个表

如果执行此函数的角色没有创建表所需的必要权限，则可能希望为该函数使用SECURITY DEFINER，并将其所有者更改为具有必要权限的另一个角色。这个版本足够安全。

- Erwin Brandstetter

---

我被迫使用现有的Postgres 8.4数据库。这个技巧很管用，谢谢！ - Boundless

@Boundless：我看到你的编辑被拒绝了，原因是“太小了”。我应用了它，因为它不会有任何影响。然而，你应该只执行一次 CREATE FUNCTION。要多次调用的是 SELECT create_mytable();。 - Erwin Brandstetter

Brandstetter: 我同意你的观点。我遇到的问题是我不知道函数是否已经被创建（就像表格可能存在或不存在一样）。因此，在调用函数之前，我想确保该函数已被创建。 - Boundless

顺便提一下，相反的 DROP TABLE IF EXISTS <tablename> 也是被支持的。 - Krumelur

Answer 2

试一试：

CREATE TABLE IF NOT EXISTS app_user (
  username varchar(45) NOT NULL,
  password varchar(450) NOT NULL,
  enabled integer NOT NULL DEFAULT '1',
  PRIMARY KEY (username)
)

- Achilles Ram Nakirekanti

---

这实际上是更干净的解决方案。应该得到点赞。 - SDReyes

事实上，我非常害怕涉及“函数”的解决方案有多少种。 - SDReyes

@SDReyes在Postgres 9.1发布之前，那些其他解决方案已经发布了，但这个版本中新增了IF NOT EXISTS选项。 - Kris

不确定这个回答的贡献，因为 @erwin-brandstetter 的回答本身已经很完整了。 - comiventor

@comiventor 正确，这个确实显示了参数的使用方式。我没有注意到这个主要答案，直到看到这个。这确实有所帮助。 - Angry 84

如果 PRIMARY KEY (user_id) 无法工作，请尝试使用 id SERIAL PRIMARY KEY。（PostgreSQL 10.5） - Derek Soike

Answer 3

我将现有答案中的通用解决方案进行了整合，可以被重复使用在任何表格上：

CREATE OR REPLACE FUNCTION create_if_not_exists (table_name text, create_stmt text)
RETURNS text AS
$_$
BEGIN

IF EXISTS (
    SELECT *
    FROM   pg_catalog.pg_tables 
    WHERE    tablename  = table_name
    ) THEN
   RETURN 'TABLE ' || '''' || table_name || '''' || ' ALREADY EXISTS';
ELSE
   EXECUTE create_stmt;
   RETURN 'CREATED';
END IF;

END;
$_$ LANGUAGE plpgsql;

用法：

select create_if_not_exists('my_table', 'CREATE TABLE my_table (id integer NOT NULL);');

如果将查询参数中的表名提取出来，这个过程可以进一步简化为只需要一个参数。同时，我还省略了模式信息。

- Wolkenarchitekt

Answer 4

这个解决方案与Erwin Brandstetter的答案有些相似，但仅使用sql语言。

并非所有的PostgreSQL安装都默认拥有plpqsql语言，这意味着您可能需要在创建函数之前调用CREATE LANGUAGE plpgsql，然后再次删除该语言，以使数据库保持与之前相同的状态（但仅当数据库没有plpgsql语言时）。看到复杂性增加了吗？

如果脚本仅用于在本地运行，则添加plpgsql可能不是问题，但如果脚本用于在客户端设置模式，则在客户端数据库中留下此类更改可能是不可取的。

这个解决方案受Andreas Scherbaum的一篇文章启发。

-- Function which creates table
CREATE OR REPLACE FUNCTION create_table () RETURNS TEXT AS $$
    CREATE TABLE table_name (
       i int
    );
    SELECT 'extended_recycle_bin created'::TEXT;
    $$
LANGUAGE 'sql';

-- Test if table exists, and if not create it
SELECT CASE WHEN (SELECT true::BOOLEAN
    FROM   pg_catalog.pg_tables 
    WHERE  schemaname = 'public'
    AND    tablename  = 'table_name'
  ) THEN (SELECT 'success'::TEXT)
  ELSE (SELECT create_table())
END;

-- Drop function
DROP FUNCTION create_table();

- zpon

---

你的解决方案非常棒，即使 plpgsql 可用。它易于扩展，可用于在不存在对象的情况下创建视图和函数。例如，可以在外部服务器上的表上创建视图。你救了我的一天！谢谢！ - Alex Yu

Answer 5

虽然没有CREATE TABLE IF NOT EXISTS，但你可以编写一个简单的存储过程来实现这个功能，例如：

CREATE OR REPLACE FUNCTION execute(TEXT) RETURNS VOID AS $$
BEGIN
  EXECUTE $1;
END; $$ LANGUAGE plpgsql;


SELECT 
  execute($$
      CREATE TABLE sch.foo 
      (
        i integer
      )
  $$) 
WHERE 
  NOT exists 
  (
    SELECT * 
    FROM information_schema.tables 
    WHERE table_name = 'foo'
      AND table_schema = 'sch'
  );

- Szymon Lipiński

---

在触发器内部并不总是有效：https://gist.github.com/igilfanov/4df5e90d8a88d653132746a223639f45 错误：关系“foo”已经存在。 - igilfanov

Answer 6

虽然没有CREATE TABLE IF NOT EXISTS...但是你可以编写一个简单的存储过程来实现这个功能，例如:

CREATE OR REPLACE FUNCTION prc_create_sch_foo_table() RETURNS VOID AS $$
BEGIN

EXECUTE 'CREATE TABLE /* IF NOT EXISTS add for PostgreSQL 9.1+ */ sch.foo (
                    id serial NOT NULL, 
                    demo_column varchar NOT NULL, 
                    demo_column2 varchar NOT NULL,
                    CONSTRAINT pk_sch_foo PRIMARY KEY (id));
                   CREATE INDEX /* IF NOT EXISTS add for PostgreSQL 9.5+ */ idx_sch_foo_demo_column ON sch.foo(demo_column);
                   CREATE INDEX /* IF NOT EXISTS add for PostgreSQL 9.5+ */ idx_sch_foo_demo_column2 ON sch.foo(demo_column2);'
               WHERE NOT EXISTS(SELECT * FROM information_schema.tables 
                        WHERE table_schema = 'sch' 
                            AND table_name = 'foo');

         EXCEPTION WHEN null_value_not_allowed THEN
           WHEN duplicate_table THEN
           WHEN others THEN RAISE EXCEPTION '% %', SQLSTATE, SQLERRM;

END; $$ LANGUAGE plpgsql;

- igilfanov