PostgreSQL 如果不存在则创建表 [英] PostgreSQL create table if not exists

问题描述

在 MySQL 脚本中，您可以编写:

In a MySQL script you can write:

CREATE TABLE IF NOT EXISTS foo ...;

...其他东西...

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

and then you can run the script many times without re-creating the table.

你如何在 PostgreSQL 中做到这一点?

How do you do this in PostgreSQL?

推荐答案

此功能已在 Postgres 9.1 中实现:

This feature has been implemented in Postgres 9.1:

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

---

对于旧版本，这里有一个功能可以解决这个问题:

---

For older versions, here is a function to work around it:

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 语句中用双引号引用标识符，则需要使用完全相同的拼写.如果不这样做，则需要使用小写字符串.见:

• The columns schemaname and tablename in pg_tables are case-sensitive. If you double-quote identifiers in the CREATE TABLE statement, you need to use the exact same spelling. If you don't, you need to use lower-case strings. See:

PostgreSQL 列名是否区分大小写?

pg_tables 只包含实际的表格.该标识符可能仍被相关对象占用.见:

pg_tables only contains actual tables. The identifier may still be occupied by related objects. See:

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

如果执行这个函数没有必要的权限来创建你可能想要使用的表SECURITY DEFINER 用于该函数，并使其由具有必要权限的另一个角色拥有.这个版本已经足够安全了.

If the role executing this function does not have the necessary privileges to create the table you might want to use SECURITY DEFINER for the function and make it owned by another role with the necessary privileges. This version is safe enough.

这篇关于PostgreSQL 如果不存在则创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！