CREATE TABLE AS

Function

CREATE TABLE AS creates a table based on the results of a query.

It creates a table and fills it with data obtained using SELECT. The table columns have the names and data types associated with the output columns of the SELECT. Except that you can override the SELECT output column names by giving an explicit list of new column names.

CREATE TABLE AS queries once the source table and writes data in the new table. The query result view changes when the source table changes. In contrast, a view re-evaluates its defining SELECT statement whenever it is queried.

Precautions

Syntax

1
2
3
4
5
6
7
8
9
CREATE [ UNLOGGED ] TABLE table_name
    [ (column_name [, ...] ) ]
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ COMPRESS | NOCOMPRESS ]
    
    [ DISTRIBUTE BY { REPLICATION | { [HASH ] ( column_name ) } } ]
    
    AS query
    [ WITH [ NO ] DATA ];

Parameter Description

Examples

Create the store_returns_t1 table and insert numbers that are greater than 4795 in the sr_item_sk column of the store_returns table.

1
CREATE TABLE store_returns_t1 AS SELECT * FROM store_returns WHERE sr_item_sk > '4795';

-- Copy store_returns to create the store_returns_t2 table.

1
CREATE TABLE store_returns_t2 AS table store_returns;

Helpful Links

CREATE TABLE, SELECT

Parent topic: DDL Syntax