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 [ TEMPORARY | TEMP ] TABLE 
                    [ IF NOT EXISTS ]  table_name 
                    [ ( column_name [ , ... ] ) ]  AS subquery 
                    [ WITH [ NO ] DATA ]

create_table_as

CREATETEMPORARYTEMPTABLEIFNOTEXISTStable_name(,column_name)ASsubqueryWITHNODATA

Semantics

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

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
TEMPORARY or TEMP

Using this qualifier will create a temporary table. Temporary tables are visible only in the current client session or transaction in which they are created and are automatically dropped at the end of the session or transaction. Any indexes created on temporary tables are temporary as well. See the section Creating and using temporary schema-objects.

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 AS 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