SQL PostgreSQL：如果不存在则创建表 AS
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25896843/
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 AS
提问by user3591836
I'm using PostgreSQL and am an SQL beginner. I'm trying to create a table from a query, and if I run:
我正在使用 PostgreSQL 并且是 SQL 初学者。我正在尝试从查询创建一个表，如果我运行：
CREATE TABLE table_name AS
(....query...)
it works just fine. But then if I add 'if not exists' and run:
它工作得很好。但是，如果我添加“如果不存在”并运行：
CREATE TABLE IF NOT EXISTS table_name AS
(....query...)
using exactly the same query, I get:
使用完全相同的查询，我得到：
ERROR: syntax error at or near "as"
ERROR: syntax error at or near "as"
Is there any way to do this?
有没有办法做到这一点？
采纳答案by IMSoP
CREATE TABLE ASis considered a separate statement from a normal CREATE TABLE, and until Postgres version 9.5(see changelog entry) didn't support an IF NOT EXISTSclause. (Be sure to look at the correct version of the manual for the version you are using.)
CREATE TABLE AS被认为是与普通CREATE TABLE不同的语句，并且直到 Postgres 9.5 版（请参阅更改日志条目）不支持IF NOT EXISTS子句。（请务必查看您正在使用的版本的正确版本的手册。）
Although not quite as flexible, the CREATE TABLE ... LIKEsyntax might be an alternative in some situations; rather than taking its structure (and content) from a SELECTstatement, it copies the structure of another table or view.
虽然不太灵活，但CREATE TABLE ... LIKE在某些情况下，语法可能是一种替代方法；它不是从SELECT语句中获取其结构（和内容），而是复制另一个表或视图的结构。
Consequently, you could write something like this (untested); the final insert is a rather messy way of doing nothing if the table is already populated:
因此，你可以写这样的东西（未经测试）；如果表已经被填充，最后的插入是一种相当混乱的方式，什么都不做：
CREATE OR REPLACE VIEW source_data AS SELECT * FROM foo NATURAL JOIN bar;
CREATE TABLE IF NOT EXISTS snapshot LIKE source_data;
INSERT INTO snapshot
SELECT * FROM source_data
WHERE NOT EXISTS ( SELECT * FROM snapshot );
Alternatively, if you want to discard previous data (e.g. an abandoned temporary table), you could conditionally drop the old table, and unconditionally create the new one:
或者，如果您想丢弃以前的数据（例如废弃的临时表），您可以有条件地删除旧表，并无条件地创建新表：
DROP TABLE IF EXISTS temp_stuff;
CREATE TEMPORARY TABLE temp_stuff AS SELECT * FROM foo NATURAL JOIN bar;
回答by Erwin Brandstetter
If you are going to write a function for this, base it on system catalog table pg_class, not on views in the information schemaor the statistics collector(which only exist if activated).
如果您打算为此编写一个函数，请基于系统目录表pg_class，而不是基于信息模式或统计信息收集器（只有在激活时才存在）中的视图。
CREATE OR REPLACE FUNCTION create_table_qry(_tbl text
, _qry text
, _schema text = NULL)
RETURNS bool AS
$func$
DECLARE
_sch text := COALESCE(_schema, current_schema());
BEGIN
IF EXISTS (
SELECT 1
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = _sch
AND c.relname = _tbl
) THEN
RAISE NOTICE 'Name is not free: %.%',_sch, _tbl;
RETURN FALSE;
ELSE
EXECUTE format('CREATE TABLE %I.%I AS %s', _sch, _tbl, _qry);
RAISE NOTICE 'Table created successfully: %.%',_sch, _tbl;
RETURN TRUE;
END IF;
END
$func$ LANGUAGE plpgsql;
The function takes a table name and the query string, and optionally also a schema to create the table in (defaults to the current schema).
该函数采用表名和查询字符串，以及可选的模式来创建表（默认为当前模式）。
Note the correct use of =in the function header and :=in the function body:
注意=在函数头和:=函数体中的正确使用：
Also note how identifiers are escaped as identifiers. You can't use regclass, since the table does not exist, yet:
还要注意标识符如何转义为标识符。您不能使用regclass，因为该表尚不存在：
回答by Saquib Azam
It's simple:
这很简单：
CREATE TABLE IF NOT EXISTS abc ( sql_id BIGINT(20) NOT NULL
AUTO_INCREMENT PRIMARY KEY, sender VARCHAR(20) NULL)
回答by Stéphane Goyet
Use do :
使用做：
do $$ begin
if not exists ( SELECT 1
FROM information_schema.tables
WHERE table_schema = 'schema_name'
AND table_name = 'bla ') then
create table schema_name.bla as select * from blu;
end if;
end $$;
回答by Vivek S.
Try this,
尝试这个，
create or replace function create_table(tblname text) returns text as
$$
BEGIN
= trim();
IF not EXISTS (select relname from pg_stat_user_tables where relname =) THEN
execute 'create table '||||' as select * from tbl'; -- <put your query here>
return ''||||' Created Successfully !!';
else
return ''||||' Already Exists !!';
END IF;
END
$$
language plpgsql
create or replace function create_table_qry(tblname text,qry text) returns text as
$$
BEGIN
= trim();
IF not EXISTS (select relname from pg_stat_user_tables where relname =) THEN
execute 'create table '||||' as '||||'';
return ''||||' Created Successfully !!';
else
return ''||||' Already Exists !!';
END IF;
END
$$
language plpgsql
