Using CREATE TABLE AS SELECT with a WITH Condition (CTE) in PostgreSQL

ctasctepostgresqlsql-standardsyntax

There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for instance, I can do this..

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

But, I can't do this..

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

Or, I get 

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

How would I do that using the standardized CTAS syntax.

Best Answer

It may be awkward, but you have to move the WITH clause from the top into the query. It's a part of the statement to generate the table, and that statement comes after the CREATE TABLE, so you would use this syntax.

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

Also worth noting that it's not explicit in the official docs, it just falls under query

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 ]

The docs mention two other use cases for the WITH keyword,

  1. To set storage_options
  2. To set NO DATA

You can see both of them in use like this,

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

Now you've got a table with nothing.

TABLE foo;
 x 
---
(0 rows)
Related Question