Product Documentation

Appearance

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) | [ ON ] REPLICATE | [ ON ] 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. If you do not specify a name for a column, the behavior is undefined. For example, if you use CAST or CONVERT expressions without aliases, the column name may default to the name of the data type used in the expression. In turn, you may try to create a table with duplicate column names, which results in the following error:

premdb=# create table casts as select cast(1 as varchar), cast(2 as varchar) from sys.const;
ERROR:  column "varchar" specified more than once in "casts"
premdb=# create table casts as select cast(1 as varchar) cast1, cast(2 as varchar) cast2 from sys.const;
SELECT 1

Tables have a limit of 2,000 user-defined columns.

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

Parent topic:SQL Commands