SQL PostgreSQL 如果不存在则创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1766046/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL create table if not exists
提问by peter2108
In a MySQL script you can write:
在 MySQL 脚本中，您可以编写：
CREATE TABLE IF NOT EXISTS foo ...;
... other stuff ...
... 其他的东西 ...
and then you can run the script many times without re-creating the table.
然后您可以多次运行脚本而无需重新创建表。
How do you do this in PostgreSQL?
你如何在 PostgreSQL 中做到这一点？
回答by Erwin Brandstetter
This feature has been implemented in Postgres 9.1:
此功能已在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;
Call:
称呼：
SELECT create_mytable(); -- call as many times as you want.
Notes:
笔记：
The columns
schemanameandtablenameinpg_tablesare case-sensitive. If you double-quote identifiers in theCREATE TABLEstatement, you need to use the exact same spelling. If you don't, you need to use lower-case strings. See:pg_tablesonly contains actual tables. The identifier may still be occupied by related objects. See:If the role executingthis function does not have the necessary privileges to create the table you might want to use
SECURITY DEFINERfor the function and make it ownedby another role with the necessary privileges. This version is safe enough.
列
schemaname和tablenameinpg_tables区分大小写。如果在CREATE TABLE语句中双引号标识符，则需要使用完全相同的拼写。如果不这样做，则需要使用小写字符串。看：pg_tables只包含实际表。该标识符可能仍被相关对象占用。看：如果角色执行此功能不具备必要的权限，以创建表，你可能想使用
SECURITY DEFINER的功能，使其拥有与必要的权限另一个角色。这个版本足够安全。
回答by Achilles Ram Nakirekanti
Try this:
尝试这个：
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)
)
回答by Ingo Fischer
I created a generic solution out of the existing answers which can be reused for any table:
我从现有答案中创建了一个通用解决方案，可以对任何表重复使用：
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;
Usage:
用法：
select create_if_not_exists('my_table', 'CREATE TABLE my_table (id integer NOT NULL);');
It could be simplified further to take just one parameter if one would extract the table name out of the query parameter. Also I left out the schemas.
如果要从查询参数中提取表名，则可以进一步简化只采用一个参数。我也遗漏了模式。
回答by zpon
This solution is somewhat similar to the answer by Erwin Brandstetter, but uses only the sql language.
此解决方案与 Erwin Brandstetter 的答案有些相似，但仅使用 sql 语言。
Not all PostgreSQL installations has the plpqsql language by default, this means you may have to call CREATE LANGUAGE plpgsqlbefore creating the function, and afterwards have to remove the language again, to leave the database in the same state as it was before (but only if the database did not have the plpgsql language to begin with). See how the complexity grows?
并非所有 PostgreSQL 安装都默认使用 plpqsql 语言，这意味着您可能必须CREATE LANGUAGE plpgsql在创建函数之前调用，然后必须再次删除语言，以使数据库保持与以前相同的状态（但前提是数据库开始时没有 plpgsql 语言）。看看复杂度是如何增长的？
Adding the plpgsql may not be issue if you are running your script locally, however, if the script is used to set up schema at a customer it may not be desirable to leave changes like this in the customers database.
如果您在本地运行脚本，添加 plpgsql 可能不是问题，但是，如果脚本用于在客户处设置架构，则可能不希望在客户数据库中保留这样的更改。
This solution is inspired by a post by Andreas Scherbaum.
此解决方案的灵感来自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();
回答by Szymon Lipiński
There is no CREATE TABLE IF NOT EXISTS... but you can write a simple procedure for that, something like:
没有 CREATE TABLE IF NOT EXISTS ......但你可以为此编写一个简单的过程，例如：
CREATE OR REPLACE FUNCTION execute(TEXT) RETURNS VOID AS $$
BEGIN
EXECUTE ;
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'
);
回答by igilfanov
There is no CREATE TABLE IF NOT EXISTS... but you can write a simple procedure for that, something like:
没有 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;
