postgresql 从选择中创建一个临时表，或者如果表已经存在则插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18850707/
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
Create a temporary table from a selection or insert if table already exist
提问by inf3rno
What is the best way to create a temporary table, if it does not already exist, and add the selected rows to it?
创建临时表的最佳方法是什么（如果它尚不存在）并将选定的行添加到其中？
回答by Erwin Brandstetter
CREATE TABLE AS
CREATE TABLE AS
is the simplest and fastest way:
是最简单快捷的方式：
CREATE TEMP TABLE tbl AS
SELECT * FROM tbl WHERE ... ;
Not sure whether table already exists
不确定表是否已经存在
CREATE TABLE IF NOT EXISTS ...was introduced in version Postgres 9.1.
For older versions, use the function provided in this related answer:
PostgreSQL create table if not exists
CREATE TABLE IF NOT EXISTS ...在 Postgres 9.1 版本中引入。
对于旧版本，请使用此相关答案中提供的函数：
PostgreSQL create table if not exist
Then:
然后：
INSERT INTO tbl (col1, col2, ...)
SELECT col1, col2, ...
Chances are, something is going wrong in your code if the temp table already exists. Make sure you don't duplicate data in the table or something. Or consider the following paragraph ...
如果临时表已经存在，那么您的代码可能会出错。确保您不要在表格或其他东西中重复数据。或者考虑以下段落......
Unique names
独特的名字
Temporary tables are only visible within your current session (don't confuse with transaction!). So the table name cannot conflict with other sessions.
Ifyou need unique names within your session, you could use dynamic SQL and utilize a SEQUENCE:
临时表仅在您当前的会话中可见（不要与事务混淆！）。所以表名不能与其他会话冲突。
如果您在会话中需要唯一名称，您可以使用动态 SQL 并使用SEQUENCE：
Create once:
创建一次：
CREATE SEQUENCE tablename_helper_seq;
You could use a DOstatement (or a plpgsql function):
您可以使用DO语句（或 plpgsql 函数）：
DO
$do$
BEGIN
EXECUTE
'CREATE TEMP TABLE tbl' || nextval('tablename_helper_seq'::regclass) || ' AS
SELECT * FROM tbl WHERE ... ';
RAISE NOTICE 'Temporary table created: "tbl%"' || ', lastval();
END
$do$;
lastval()and currval(regclass)are instrumental in this case to get the name actually used.
lastval()并且currval(regclass)在这种情况下有助于获得实际使用的名称。
