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