CREATE TABLE AS

Create a new table that stores the results of a query.

CREATE [ { TEMPORARY | TEMP } ] TABLE [ IF NOT EXISTS ] table_name
    [ (column_name [, ...]) ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    AS query
    [ WITH ]
    [ DATA | NO DATA ]
    [ DISTRIBUTE { ON (column) | REPLICATE | RANDOM } ]
    { [ SORT ON (column) ] |
      [ CLUSTER ON (column [, ... ] }
    [ PARTITION BY ( { range_partition_spec | hash_partition_spec } [ , ... ] )
    ]
TEMPORARY | TEMP
Create a temporary table that is automatically dropped at the end of the session (and is visible only to that session). You cannot create "global" temporary tables that are visible across sessions. Temporary tables belong to a temporary schema. You cannot qualify a temporary table name with a user-defined schema name.
IF NOT EXISTS
Create the table if it does not already exist. If it does exist, do not create it and do not return an error.
table_name
Give the table a name that is distinct from the name of any other table or view in the same schema. Optionally, qualify the name of a persistent table with the schema name. You can also qualify table names with the name of the current database, but you cannot create a table in a different database; the table must belong to the current database.

The maximum length of a table name is 128 bytes; longer names are automatically truncated. See SQL Identifiers.

column_name
Name each column uniquely.
ON COMMIT
Set the behavior for temporary tables when a transaction commits.
  • PRESERVE ROWS: do not delete rows from temporary tables when transactions commit.
  • DELETE ROWS: delete rows from temporary tables when transactions commit.
  • DROP: drop temporary tables when transactions commit.
AS query
Supply any query (SELECT statement) that Yellowbrick supports. The results of this query populate the table. You can also use a TABLE statement or an EXECUTE statement.
WITH
The options that follow the query definition may be preceded by the WITH keyword.
NO DATA | DATA
NO DATA creates an empty table, regardless of the rows that the query returns. If NO DATA is not specified or DATA is specified, the table will contain the results of the query.
DISTRIBUTE
Define data distribution for the table; see CREATE TABLE, which supports the same options. See also Data Distribution for CREATE TABLE AS (CTAS) Results.
Note: You cannot distribute a table on a floating-point column (FLOAT, FLOAT4, FLOAT8).
SORT ON
Define a sort column for the table (how the data is sorted when it is loaded). The SORT ON and CLUSTER ON options are mutually exclusive. See Sorted and Clustered Tables.
CLUSTER ON
Define cluster columns for the table; see CREATE TABLE, which supports the same syntax.
PARTITION BY
See Partitioning Options. CREATE TABLE supports the same syntax.
Note: If used, the following clauses at the end of the CREATE TABLE AS statement must appear in the order shown in the syntax diagram:
  1. DISTRIBUTE
  2. SORT ON or CLUSTER ON
  3. PARTITION BY

Data Distribution for CREATE TABLE AS (CTAS) Results

If you do not specify a distribution type for a CTAS table, the resulting data distribution depends on the nature of the query that creates the table.

  • A table created from columns in one or more replicated tables is also replicated.
  • A table created from a single hash-distributed table is typically hash-distributed on the same column when the distribution column is included in the select list. If the distribution column is not included in the select list, random distribution is used.
  • Tables resulting from equality joins over distribution columns typically preserve the hash distribution.
  • In general, hash distribution is preserved where possible, but tables created from complex joins or that contain complex select list expressions may produce randomly distributed result sets.

Constraints

A CTAS statement preserves NOT NULL constraints on columns from the source table. Other constraints are not preserved.

Examples

Create a table that filters and sorts the rows from another table:
premdb=# create table season20 as 
select * from match where seasonid=20 order by seasonid,matchday;
SELECT 380
premdb=# \d season20
              Table "public.season20"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 seasonid | smallint                    | 
 matchday | timestamp without time zone | 
 htid     | smallint                    | 
 atid     | smallint                    | 
 ftscore  | character(3)                | 
 htscore  | character(3)                | 

Distribution: Hash (seasonid)
Create a table that uses a TABLE statement to select all of the rows from another table:
premdb=# create table newseason as 
table season;
SELECT 25
Create a table that holds the result of an executed prepared statement:
premdb=# prepare x(varchar(30)) as 
select * from hometeam where name=$1;
PREPARE
premdb=# create table homectas as execute x('Leicester City');
SELECT 1
premdb=# select * from homectas;
 htid |      name      
------+----------------
   23 | Leicester City
(1 row)
Create a table that contains no data, just the table structure with column definitions:
premdb=# create table ctas_season as 
select * from season with no data;
SELECT 0
premdb=# \d ctas_season
           Table "public.ctas_season"
   Column    |         Type          | Modifiers 
-------------+-----------------------+-----------
 seasonid    | smallint              | 
 season_name | character(9)          | 
 numteams    | smallint              | 
 winners     | character varying(30) | 

Distribution: Replicated

premdb=# select count(*) from ctas_season;
 count 
-------
     0
(1 row)
Create a version of the match table that is sorted on the htid column:
premdb=# create table matchsort as 
select * from match with sort on(htid);
SELECT 8606
premdb=# \d matchsort
              Table "public.matchsort"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 seasonid | smallint                    | 
 matchday | timestamp without time zone | 
 htid     | smallint                    | 
 atid     | smallint                    | 
 ftscore  | character(3)                | 
 htscore  | character(3)                | 

Distribution: Hash (seasonid)
Sort Column: (htid)
Create a table that is sorted and partitioned:
premdb=# create table stats_sorted_partitioned as 
select * from newmatchstats where seasonid>4 
distribute on(seasonid) 
sort on(htid) 
partition by(hash(matchday with 100 partitions, is null));
SELECT 39398400