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

PostgreSQL 12
- option USING added (commit 8586bf7e)
- option WITH OIDS removed (commit 578b2297)
PostgreSQL 9.5
- IF NOT EXISTS syntax added (commit e39b6f95)
PostgreSQL 9.1
- option UNLOGGED added (commit 53dbc27c)
PostgreSQL 8.4
- WITH [ NO ] DATA clause added (commit 8ecd5351)
PostgreSQL 8.2
- WITH option for storage parameters added (commit 277807bd)
- ON COMMIT clause added (commit 85c0eac1)
- TABLESPACE option added (commit 85c0eac1)
PostgreSQL 8.0
- options WITH / WITHOUT OIDS added (commit 98dcf085)
PostgreSQL 7.4
- GLOBAL clause added (commit ac5fdea6)
PostgreSQL 7.2
- LOCAL clause added (commit bcb5aac8)
PostgreSQL 6.3
- added (commit 2c833a72)

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)

References

PostgreSQL documentation: CREATE TABLE AS

Version charts

Useful overviews

Useful links

Contents