CREATE TABLE AS

A DDL command for defining a new table from the results of a query

CREATE TABLE AS is a DDL command for defining a new table from the results of a query.

CREATE TABLE AS was added in PostgreSQL 6.3.

Usage

CREATE TABLE AS 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.

In contrast to creating a view or materialized view, CREATE TABLE AS results in a fully autonomous table.

Alternatives

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

Change history

Examples

Basic usage example for CREATE TABLE AS:

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=# CREATE TABLE bar AS SELECT * FROM foo WHERE id % 2 = 1;
SELECT 50

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)

Categories

DDL, SQL command

See also

CREATE TABLE, SELECT INTO