5

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.

6

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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