CREATE TABLE AS
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, butCREATE TABLE AS
provides more options.
Change history
- PostgreSQL 12
- PostgreSQL 9.5
IF NOT EXISTS
syntax added (commit e39b6f95)
- PostgreSQL 9.1
- option
UNLOGGED
added (commit 53dbc27c)
- option
- PostgreSQL 8.4
WITH [ NO ] DATA
clause added (commit 8ecd5351)
- PostgreSQL 8.2
- PostgreSQL 8.0
- options
WITH
/WITHOUT OIDS
added (commit 98dcf085)
- options
- 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