在 SQL 中创建临时表

Creating temporary tables in SQL

提问by user1970850

I am trying to create a temporary table that selects only the data for a certain register_type. I wrote this query but it does not work:

我正在尝试创建一个临时表，该表仅选择某个register_type. 我写了这个查询，但它不起作用：

$ CREATE TABLE temp1
(Select 
    egauge.dataid,
    egauge.register_type,
    egauge.timestamp_localtime,
    egauge.read_value_avg
from rawdata.egauge
where register_type like '%gen%'
order by dataid, timestamp_localtime ) $

I am using PostgreSQL.
Could you please tell me what is wrong with the query?

我正在使用 PostgreSQL。
你能告诉我查询有什么问题吗？

回答by Erwin Brandstetter

You probably want CREATE TABLE AS- also works for TEMPORARY(TEMP) tables:

您可能想要CREATE TABLE AS- 也适用于TEMPORARY( TEMP) 表：

CREATE TEMP TABLE temp1 AS
SELECT dataid
     , register_type
     , timestamp_localtime
     , read_value_avg
FROM   rawdata.egauge
WHERE  register_type LIKE '%gen%'
ORDER  BY dataid, timestamp_localtime

This creates a temporary table and copies data into it. A static snapshotof the data, mind you. It's just like a regular table, but resides in RAM if temp_buffersis set high enough, is only visible within the current sessionand dies at the end of it. When created with ON COMMIT DROPit dies at the end of the transaction.

这将创建一个临时表并将数据复制到其中。请注意，数据的静态快照。它就像一个普通表，但如果temp_buffers设置得足够高，则驻留在 RAM 中，仅在当前会话中可见，并在它结束时死亡。当用ON COMMIT DROP它创建时在交易结束时死亡。

Temp tables comes first in the default schema search path, hiding other visible tables of the same name unless schema-qualified:

临时表首先出现在默认模式搜索路径中，除非模式限定，否则隐藏同名的其他可见表：

• How does the search_path influence identifier resolution and the "current schema"

• search_path 如何影响标识符解析和“当前模式”

If you want dynamic, you would be looking for CREATE VIEW- a completely different story.

如果你想要dynamic，你会寻找CREATE VIEW- 一个完全不同的故事。

The SQL standard also defines, and Postgres also supports: SELECT INTO.
But its use is discouraged:

SQL 标准也定义了，Postgres 也支持：SELECT INTO.
但不鼓励使用它：

It is best to use CREATE TABLE ASfor this purpose in new code.

最好CREATE TABLE AS在新代码中用于此目的。

There is really no need for a second syntax variant, and SELECT INTOis used for assignment in plpgsql, where the SQL syntax is consequently not possible.

真的不需要第二个语法变体，并且SELECT INTO用于在 中赋值plpgsql，因此 SQL 语法是不可能的。

Related:

有关的：

• Combine two tables into a new one so that select rows from the other one are ignored
• ERROR: input parameters after one with a default value must also have defaults

• 将两个表合并为一个新表，以便忽略另一个表中的选择行
• 错误：具有默认值的输入参数也必须具有默认值

---

---

CREATE TABLE LIKE (...)only copies the structurefrom another table and no data:

CREATE TABLE LIKE (...)只从另一个表复制结构，没有数据：

The LIKEclause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints.

该LIKE子句指定一个表，新表从该表自动复制所有列名、它们的数据类型和它们的非空约束。

---

---

If you need a "temporary" table just for the purpose of a single query (and then discard it) a "derived table"in a CTE or a subquery comes with considerably less overhead:

如果您需要一个“临时”表仅用于单个查询（然后丢弃它），那么CTE 或子查询中的“派生表”的开销要小得多：

• Change the execution plan of query in postgresql manually?
• Combine two SELECT queries in PostgreSQL
• Reuse computed select value
• Multiple CTE in single query
• Update with results of another sql

• 手动更改postgresql中查询的执行计划？
• 在 PostgreSQL 中合并两个 SELECT 查询
• 重用计算的选择值
• 单个查询中的多个 CTE
• 更新另一个 sql 的结果

回答by Chains

http://www.postgresql.org/docs/9.2/static/sql-createtable.html

http://www.postgresql.org/docs/9.2/static/sql-createtable.html

CREATE TEMP TABLE temp1 LIKE ...