SELECT INTO

A DDL command for creating a table from a query

SELECT INTO is a DDL command for creating a table based on the results of a query.

SELECT INTO has always been present in PostgreSQL.

Usage

SELECT INTO will create a table based on the provided SELECT query. Column names (unless explicitly modified with AS) and data types will be those generated by the SELECT query. Note that no constraints or indexes will be created, and will need to be added manually if required.

Alternatives

  • CREATE TABLE AS provides similar functionality with more options, and is more compliant with the SQL standard.
  • CREATE TABLE ... (LIKE ...) can be used to "clone" the definition of an existing table, but cannot populate it at the same time.

Notes

In ECPG and PL/pgSQL, SELECT INTO is used for selecting values into scalar variables.

Examples

Basic usage example for SELECT INTO:

postgres=# CREATE TABLE foo (id INT NOT NULL PRIMARY KEY, val TEXT);
CREATE TABLE

postgres=# INSERT INTO foo VALUES(generate_series(1,100), clock_timestamp()); 
INSERT 0 100

postgres=# SELECT * INTO bar FROM foo WHERE id % 2 = 1;
SELECT 50

postgres=# SELECT COUNT(*) FROM bar;
 count
-------
    50
(1 row)

The table created will not have any indexes, constraints or other properties, even if the query it is generated from only references a single source table:

postgres=# \d bar
                Table "public.bar"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
 val    | text    |           |          |

To clone the definition of an existing table, CREATE TABLE ... (LIKE ...) can be used, e.g.:

postgres=# CREATE TABLE baz (LIKE foo INCLUDING ALL);
CREATE TABLE

postgres=# \d baz
                Table "public.baz"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 val    | text    |           |          |
Indexes:
    "baz_pkey" PRIMARY KEY, btree (id)

postgres=# SELECT COUNT(*) FROM baz;
 count
-------
     0
(1 row)

The target table cannot exist:

postgres=# SELECT * INTO bar FROM foo WHERE id % 2 = 0;
ERROR:  relation "bar" already exists

To insert data into an existing table from another table, use INSERT INTO ... (SELECT ... FROM ...), e.g.:

postgres=# INSERT INTO bar (select * FROM foo where id %2 = 0);
INSERT 0 50

Categories

DDL

See also

CREATE TABLE AS, CREATE TABLE, CREATE VIEW, CREATE MATERIALIZED VIEW