CREATE TABLE AS vs SELECT INTO

Question

PostgreSQL supports CREATE TABLE AS and SELECT INTO when do I use both?

CREATE TABLE AS -- define a new table from the results of a query

CREATE TABLE AS creates a table and fills it with data computed by a SELECT command. The table columns have the names and data types associated with the output columns of the SELECT (except that you can override the column names by giving an explicit list of new column names).

CREATE TABLE AS bears some resemblance to creating a view, but it is really quite different: it creates a new table and evaluates the query just once to fill the new table initially. The new table will not track subsequent changes to the source tables of the query. In contrast, a view re-evaluates its defining SELECT statement whenever it is queried.

And, then.

SELECT INTO -- define a new table from the results of a query

SELECT INTO creates a new table and fills it with data computed by a query. The data is not returned to the client, as it is with a normal SELECT. The new table's columns have the names and data types associated with the output columns of the SELECT.

Answer 1

Without the explanation, always use CREATE TABLE AS without exception. At the bottom of each under NOTES this is cleared up,

Notes for SELECT INTO,

CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.

Notes for CREATE TABLE AS,

This command is functionally similar to SELECT INTO, but it is preferred since it is less likely to be confused with other uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a superset of the functionality offered by SELECT INTO.

Also in the Compatibility section of the docs of SELECT INTO it goes even further,

The SQL standard uses SELECT INTO to represent selecting values into scalar variables of a host program, rather than creating a new table. This indeed is the usage found in ECPG (see Chapter 34) and PL/pgSQL (see Chapter 41). The PostgreSQL usage of SELECT INTO to represent table creation is historical. It is best to use CREATE TABLE AS for this purpose in new code.

So we have,

1. PostgreSQL thinks it's confusing because SELECT INTO does other stuff in contexts only available in PL/pgSQL, and ECPG.
2. CREATE TABLE supports more functionality (I assume they're referring to WITH OIDS, and TABLESPACE, IF NOT EXISTS).
3. SELECT INTO for table creation is "deprecated".

As a side note, the syntax for a CTAS with a CTE may look a bit weird., and SELECT INTO may also be some kind of hold over QUEL's RETRIEVE INTO. QUEL was the predecessor to SQL, that the predecessor to PostgreSQL (INGRES) used.

Answer 2

There's one other thing I noticed that's missing from the accepted answer. Using CREATE TABLE AS preserves the nullable attribute of each column which seems to be ignored by SELECT INTO.

Just on this basis alone, I'd recommend CREATE TABLE AS. A common use case for both statements is to load data from a long running query into a table without locking that table for the duration of your query. You create a temporary table using one of the above commands, put the long running query results in there, and then insert those results into the original table. Preserving the nullable attribute in your temp table reduces the chances of your second insert failing.

Tested this on PG 11, so perhaps a newer feature since this question was answered.