26

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.

1 Answer 1

41

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)
2
  • If only it were consistent... the INSERT statement requires the CTE for the query part before the INSERT.
    – Gerhard
    Commented Jan 11 at 23:32
  • 1
    Thanks for clarifying the CREATE TABLE AS needed to go before the WITH. That's an unfortunate inconsistency. Commented Mar 27 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.