PostgreSQL:创建不存在的表

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"

Is there any way to do this?

有什么办法吗?

3 个解决方案

#1

CREATE TABLE AS is considered a separate statement from a normal CREATE TABLE, and until Postgres version 9.5 (see changelog entry) didn't support an IF NOT EXISTS clause. (Be sure to look at the correct version of the manual for the version you are using.)

创建表作为一个单独的语句，从一个普通的创建表，直到Postgres版本9.5(见changelog条目)不支持IF NOT EXISTS子句。(请务必查看您正在使用的版本的手册的正确版本。)

Although not quite as flexible, the CREATE TABLE ... LIKE syntax might be an alternative in some situations; rather than taking its structure (and content) from a SELECT statement, it copies the structure of another table or view.

尽管不太灵活，CREATE表格…在某些情况下，语法可能是一种选择;它不会从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;

#2

If you are going to write a function for this, base it on system catalog table pg_class, not on views in the information schema or the statistics collector (which only exist if activated).

如果要为此编写一个函数，请将其基于system catalog表pg_class，而不是基于information schema或statistics collector中的视图(只有在激活时才存在)。

How to check if a table exists in a given schema
如何检查给定模式中是否存在表

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:

注意在函数头中正确使用=，在函数体中使用:=:

The forgotten assignment operator "=" and the commonplace ":="
被遗忘的赋值算子"="和平凡的":="

Also note how identifiers are escaped as identifiers. You can't use regclass, since the table does not exist, yet:

还要注意标识符是如何作为标识符转义的。不能使用regclass，因为该表还不存在:

Table name as a PostgreSQL function parameter
表名作为PostgreSQL函数参数

#3

Try this,

试试这个,

create or replace function create_table(tblname text) returns text as
$$ 
BEGIN
$1 = trim($1);
IF not EXISTS (select relname from pg_stat_user_tables where relname =$1) THEN
execute 'create table '||$1||' as select * from tbl'; -- <put your query here>
return ''||$1||' Created Successfully !!';
else
return  ''||$1||' Already Exists !!';
END IF;
END
$$
language plpgsql

create or replace function create_table_qry(tblname text,qry text) returns text as
$$ 
BEGIN
$1 = trim($1);
IF not EXISTS (select relname from pg_stat_user_tables where relname =$1) THEN
execute 'create table '||$1||' as '||$2||'';
return ''||$1||' Created Successfully !!';
else
return  ''||$1||' Already Exists !!';
END IF;
END
$$
language plpgsql

#1