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

问题描述

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

In a MySQL script you can write:

CREATE TABLE IF NOT EXISTS foo ...;

...其他东西...

... other stuff ...

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

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

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

How do you do this in PostgreSQL?

推荐答案

此功能已

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

致电:

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:

• 如何检查如果表存在于给定的模式中

如果角色正在执行，此函数没有创建表所必需的特权，则可能要使用

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屋！