使用 CREATE TABLE AS SELECT 如何指定 WITH 條件 (CTE)？

PostgreSQL 中有一個舊的和不推薦使用的命令，它早於CREATE TABLE AS SELECT（CTAS）SELECT ... INTO .... FROM，它支持WITH子句/公用表表達式（CTE）。所以，例如，我可以做到這一點..

WITH w AS (
 SELECT *
 FROM ( VALUES (1) ) AS t(x)
)
SELECT *
INTO foo
FROM w;

但是，我不能這樣做..

WITH w AS (
 SELECT *
 FROM ( VALUES (1) ) AS t(x)
)
CREATE TABLE foo AS
SELECT * FROM w;

或者，我得到

ERROR:  syntax error at or near "CREATE"
LINE 5: CREATE TABLE foo AS

我將如何使用標準化的 CTAS 語法來做到這一點。

這可能很尷尬，但您必須將WITH子句從頂部移動到查詢中。它是生成表的語句的一部分，並且該語句位於之後CREATE TABLE，因此您將使用此語法。

CREATE TABLE foo AS
WITH w AS (
 SELECT *
 FROM ( VALUES (1) ) AS t(x)
)
SELECT * FROM w;

還值得注意的是，它在官方文件中並沒有明確說明，它只是在查詢中

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
   [ (column_name [, ...] ) ]
   [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
   [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
   [ TABLESPACE tablespace_name ]
   AS query
   [ WITH [ NO ] DATA ]

文件提到了關鍵字的另外兩個案例，WITH

1. 設置storage_options
2. 設置NO DATA

你可以看到它們都像這樣使用，

CREATE TABLE foo
WITH (fillfactor = 100)
AS
 WITH w AS (
   SELECT *
   FROM ( VALUES (1) ) AS t(x)
 )
 SELECT * FROM w
WITH NO DATA;

現在你有一張什麼都沒有的桌子。

TABLE foo;
x 
---
(0 rows)

引用自：https://dba.stackexchange.com/questions/162978