PostgreSQL:如果不存在,则创建表 [英] PostgreSQL: Create table if not exists AS
问题描述
我正在使用PostgreSQL,并且是SQL的初学者。我正在尝试通过查询创建表,并且如果运行:
创建表table_name AS
( .... query ...)
它很好用。但是然后如果我添加如果不存在并运行:
如果不存在table_name AS
(。 ... query ...)
使用完全相同的查询,我得到:
错误: as
或附近的语法错误pre>
有没有办法做到这一点?
创建表AS 被认为是与普通创建表和直到Postgres 9.5版(请参见更改日志条目)不支持 IF NOT EXISTS
子句。 (一定要查看所使用版本的正确手册。)
尽管不够灵活,但 CREATE TABLE在某些情况下,... LIKE
语法可能是替代方法;而不是从 SELECT
语句获取其结构(和内容),而是复制另一个表或视图的结构。
因此,您可以编写如下内容(未经测试);如果表已被填充,最后的插入是一种不执行任何操作的混乱方式:
创建或替换视图source_data作为SELECT *来自foo NATURAL JOIN栏;
如果不存在快照,则创建表。
插入快照
SELECT * FROM source_data
不存在的地方(SELECT * FROM快照);
或者,如果您想丢弃先前的数据(例如,废弃的临时表),则可以有条件地删除旧表,并无条件创建新表:
如果存在temp_stuff,则删除表;
创建临时表temp_stuff AS SELECT * FROM foo NATURAL JOIN bar;
I'm using PostgreSQL and am an SQL beginner. I'm trying to create a table from a query, and if I run:
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?
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.)
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.
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;
这篇关于PostgreSQL:如果不存在,则创建表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!