如何在PostgreSQL中使用VALUES创建临时表

我正在学习PostgreSQL，并试图弄清楚如何创建一个临时表或一个WITH声明来代替常规表，以进行调试。

我查看了CREATE TABLE的文档，并说VALUES可以用作查询，但没有给出示例；

因此，我编写了一个简单的测试，如下所示：

DROP TABLE IF EXISTS lookup;
CREATE TEMP TABLE lookup (
  key integer,
  val numeric
) AS
VALUES (0,-99999), (1,100);

但是PostgreSQL（9.3）抱怨“ AS”或附近的

语法错误

我的问题是：

我该如何修正上面的陈述？
我该如何修改它以在VALUES中使用？

我试图用一些更现代的建议来回答这个问题（由于选择的答案是使用不赞成使用的非标准化语法）dba.stackexchange.com/a/201575/2639

#1 楼

编辑：我将原样保留原来的答案，但请注意，a_horse_with_no_name建议的以下编辑是使用VALUES创建临时表的首选方法。

从某些值中进行选择，而不仅仅是创建表并将其插入其中，您可以执行以下操作：

WITH  vals (k,v) AS (VALUES (0,-9999), (1, 100)) 
SELECT * FROM vals;

以类似的方式实际创建临时表，使用：

WITH  vals (k,v) AS (VALUES (0,-9999), (1, 100)) 
SELECT * INTO temporary table temp_table FROM vals;

编辑：正如a_horse_with_no_name所指出的，在文档中指出CREATE TABLE AS...在功能上与SELECT INTO ...类似，但前者是SELECT INTO的超集。后者，并且在plpgslq中使用了CREATE TABLE来为临时变量分配值，因此在这种情况下它将失败。因此，尽管以上示例对纯SQL有效，但应首选q4312079q形式。问题，这包括强制转换为值列表内的正确数据类型并使用CTE（WITH）语句。，CTE始终会实现。使用CTE的理由很多，但如果使用不当，则会对性能造成重大影响。但是，在很多情况下，优化围栏实际上可以提高性能，因此需要注意这一点，而不是盲目避免。

来自文档：“ CREATE TABLE AS在功能上类似于SELECTINTO。CREATETABLE AS是推荐的语法”

– a_horse_with_no_name
2014年12月21日在23:14

优化围栏不一定是一件坏事。因此，我已经看到许多可以调整的语句，它们可以大大提高运行速度。

– a_horse_with_no_name
19年7月19日在11:25

当然，我也做了澄清。我一直在空间环境中使用CTE。如果您的where子句带有WHERE ST_Intersects（geom，（SELECT geom FROM sometable）或WHERE ST_Intersects（geom，ST_Buffer（anothergeom，10））之类的内容，则查询计划程序通常不使用空间索引，因为geom列不再如果您在最初的CTE中创建您感兴趣的区域，则此问题就消失了，如果您想在同一查询中的多个其他表达式中使用相同的aoi，这也非常方便，这在GIS上下文中并不罕见。

–约翰·鲍威尔（John Powell）
19年7月19日在11:57

#2 楼

create table as需要一条select语句：

DROP TABLE IF EXISTS lookup;
CREATE TEMP TABLE lookup 
as 
select *
from (
   VALUES 
    (0::int,-99999::numeric), 
    (1::int, 100::numeric)
) as t (key, value);

您也可以使用CTE重新编写此代码：

create temp table lookup 
as 
with t (key, value) as (
  values 
    (0::int,-99999::numeric), 
    (1::int,100::numeric)
)
select * from t;

感谢您的评论。由于文档中所述的原因，您的方法显然更好。尽管已经将近5年了，但我已经编辑了答案。

–约翰·鲍威尔（John Powell）
19年7月19日在10:17

#3 楼

问题是数据类型。如果删除它们，该语句将起作用：

CREATE TEMP TABLE lookup
  (key, val) AS
VALUES 
  (0, -99999), 
  (1, 100) ;

您可以通过强制转换第一行的值来定义类型：

CREATE TEMP TABLE lookup 
  (key, val) AS
VALUES 
  (0::bigint, -99999::int), 
  (1, 100) ;

#4 楼

如果您只需要在查询中使用一些值，则您实际上不需要创建表或使用CTE。您可以对其进行内联：）。例如：

SELECT  *
FROM    (VALUES(0::INT, -99999::NUMERIC), (1, 100)) AS lookup(key, val)

产生：是常规表格，视图等），例如：

SELECT  *
FROM    (VALUES(0::int, -99999::numeric), (1, 100)) AS lookup(key, val)
       ,(VALUES('Red'), ('White'), ('Blue')) AS colors(color);

产生以下结果：

可以，但是问题是“如何使用...创建临时表？”

–超立方体ᵀᴹ
17-10-20在17:51

是的，但是为什么您需要一个带有一些固定查找值的临时表，如果不将其连接到另一个关系上呢？无论问题的措辞如何，此解决方案都会解决问题本身。

–isapir
17-10-20在18:37

也许OP只是碰巧将示例简化为一个易于发布的问题，但实际数据具有数千个值？

–stantanus
18年1月25日在20:56

OP特别使用值声明，因此我的答案仍然适用，因为这正是它的作用

–isapir
19-10-10在15:26

#5 楼

首先，始终使用标准化的CREATE TABLE AS，如其他答案中所建议的SELECT INTO十多年来已弃用该语法。您可以将CREATE TABLE AS与CTE一起使用

尽管这里有许多答案建议使用CTE，但这不是可取的。实际上，它可能会稍微慢一些。只需将它包装成一个表即可。 />

DROP TABLE IF EXISTS lookup;

CREATE TEMP TABLE lookup(key, value) AS
  VALUES
  (0::int,-99999::numeric),
  (1,100);

PostgreSQL中的CTE强制实现。这是一个优化围栏。因此，通常不要在任何地方使用它们，除非您了解成本并知道可以提高性能。您可以在这里看到速度变慢，例如，

CREATE TEMP TABLE lookup(key, value) AS
  SELECT key::int, value::numeric
  FROM ( VALUES
    (0::int,-99999::numeric),
    (1,100)
  ) AS t(key, value);

我已经更新了答案以反映标准，并指出接受的答案并不总是等同于CREATE TABLE AS，并在优化围栏上添加了注释，这是提出的一个很好的观点。 CTE具有许多优点，但是，如果盲目使用，确实会导致糟糕的性能。

–约翰·鲍威尔（John Powell）
19年7月19日在11:20

#6 楼

WITH u AS (
    SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS account (id,name)
)
SELECT id, name, length(name) from u;

编程黑洞网