在SQL中创建临时表 [英] Creating temporary tables in SQL

问题描述

我正在尝试创建一个临时表，该表仅选择特定register_type的数据.我写了这个查询，但是不起作用:

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:

$ 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 ) $

我正在使用PostgreSQL.
您能告诉我查询出什么问题吗?

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

推荐答案

您可能想要

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

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

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

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

临时表在默认的 schema搜索路径中排在第一位，隐藏其他具有相同名称的可见表，除非经过模式限定:

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

• 如何search_path是否会影响标识符解析度和当前模式"

如果您希望动态 ，则需要

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

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

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

为此，最好在新代码中使用CREATE TABLE AS.

确实不需要第二种语法变体，并且SELECT INTO用于plpgsql中的赋值，因此无法使用SQL语法.

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

相关:

• 错误:带有默认值的输入参数后面的输入参数也必须具有默认值

• 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 (...) 仅从另一个表复制结构且没有数据:

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

LIKE子句指定一个表，新表从该表开始 自动复制所有列名称，其数据类型及其 非空约束.

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

---

如果仅出于单个查询的目的而需要临时"表(然后将其丢弃)，则CTE或子查询中的派生表" 的开销将大大减少:

---

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:

• 更改查询的执行计划手动手动PostgreSQL吗?
• 在PostgreSQL中合并两个SELECT查询
• 重复使用计算出的选择值
• 单个查询中有多个CTE
• 使用其他SQL的结果进行更新

• 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

这篇关于在SQL中创建临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！