CREATE TABLE AS

Synopsis

Use the CREATE TABLE AS statement to create a table using the output of a subquery.

Syntax

create_table_as ::= CREATE TABLE [ IF NOT EXISTS ]  table_name 
                    [ ( column_name [ , ... ] ) ]  AS query 
                    [ WITH [ NO ] DATA ]

create_table_as

CREATETABLEIFNOTEXISTStable_name(,column_name)ASqueryWITHNODATA

Semantics

YugabyteDB may extend the syntax to allow specifying PRIMARY KEY for CREATE TABLE AS command.

create_table_as

CREATE TABLE [ IF NOT EXISTS ] table_name

Create a table.

table_name

Specify the name of the table.

( column_name [ , ... ] )

Specify the name of a column in the new table. When not specified, column names are taken from the output column names of the query.

AS query [ WITH [ NO ] DATA ]

query

Examples

CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));

INSERT INTO sample VALUES (1, 2.0, 3, 'a'), (2, 3.0, 4, 'b'), (3, 4.0, 5, 'c');

CREATE TABLE selective_sample SELECT * FROM sample WHERE k1 > 1;

yugabyte=# SELECT * FROM selective_sample ORDER BY k1;

 k1 | k2 | v1 | v2
----+----+----+----
  2 |  3 |  4 | b
  3 |  4 |  5 | c
(2 rows)

See also