Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # CREATE TABLE AS **Topics** + [Syntax](#r_CREATE_TABLE_AS-synopsis) + [Parameters](#r_CREATE_TABLE_AS-parameters) + [CTAS usage notes](r_CTAS_usage_notes.md) + [CTAS examples](r_CTAS_examples.md) Creates a new table based on a query. The owner of this table is the user that issues the command. The new table is loaded with data defined by the query in the command. The table columns have names and data types associated with the output columns of the query. The CREATE TABLE AS (CTAS) command creates a new table and evaluates the query to load the new table. ## Syntax ``` CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ ( column_name [, ... ] ) ] [ BACKUP { YES | NO } ] [ table_attributes ] AS query where table_attributes are: [ DISTSTYLE { AUTO | EVEN | ALL | KEY } ] [ DISTKEY( distkey_identifier ) ] [ [ COMPOUND | INTERLEAVED ] SORTKEY( column_name [, ...] ) ] ``` ## Parameters LOCAL Although this optional keyword is accepted in the statement, it has no effect in Amazon Redshift. TEMPORARY \$1 TEMP Creates a temporary table. A temporary table is automatically dropped at the end of the session in which it was created. *table\$1name* The name of the table to be created. If you specify a table name that begins with '\$1 ', the table is created as a temporary table. For example: ``` create table #newtable (id) as select * from oldtable; ``` The maximum table name length is 127 bytes; longer names are truncated to 127 bytes. Amazon Redshift enforces a quota of the number of tables per cluster by node type. The table name can be qualified with the database and schema name, as the following table shows. ``` create table tickit.public.test (c1) as select * from oldtable; ``` In this example, `tickit` is the database name and `public` is the schema name. If the database or schema doesn't exist, the statement returns an error. If a schema name is given, the new table is created in that schema (assuming the creator has access to the schema). The table name must be a unique name for that schema. If no schema is specified, the table is created using the current database schema. If you are creating a temporary table, you can't specify a schema name, since temporary tables exist in a special schema. Multiple temporary tables with the same name are allowed to exist at the same time in the same database if they are created in separate sessions. These tables are assigned to different schemas. *column\$1name* The name of a column in the new table. If no column names are provided, the column names are taken from the output column names of the query. Default column names are used for expressions. For more information about valid names, see [Names and identifiers](r_names.md). BACKUP \$1 YES \$1 NO \$1 A clause that specifies whether the table should be included in automated and manual cluster snapshots. For tables, such as staging tables, that don't contain critical data, specify BACKUP NO to save processing time when creating snapshots and restoring from snapshots and to reduce storage space on Amazon Simple Storage Service. The BACKUP NO setting has no effect on automatic replication of data to other nodes within the cluster, so tables with BACKUP NO specified are restored in the event of a node failure. The default is BACKUP YES. No-backup tables aren't supported for RA3 provisioned clusters and Amazon Redshift Serverless workgroups. A table marked as no-backup in an RA3 cluster or serverless workgroup is treated as a permanent table that will always be backed up while taking a snapshot, and always restored when restoring from a snapshot. To avoid snapshot costs for no-backup tables, truncate them before taking a snapshot. DISTSTYLE \$1 AUTO \$1 EVEN \$1 KEY \$1 ALL \$1 Keyword that defines the data distribution style for the whole table. Amazon Redshift distributes the rows of a table to the compute nodes according to the distribution style specified for the table. The default is DISTSTYLE AUTO. The distribution style that you select for tables affects the overall performance of your database. For more information, see [Data distribution for query optimization](t_Distributing_data.md). + AUTO: Amazon Redshift assigns an optimal distribution style based on the table data. To view the distribution style applied to a table, query the PG\$1CLASS system catalog table. For more information, see [Viewing distribution styles](viewing-distribution-styles.md). + EVEN: The data in the table is spread evenly across the nodes in a cluster in a round-robin distribution. Row IDs are used to determine the distribution, and roughly the same number of rows are distributed to each node. This is the default distribution method. + KEY: The data is distributed by the values in the DISTKEY column. When you set the joining columns of joining tables as distribution keys, the joining rows from both tables are collocated on the compute nodes. When data is collocated, the optimizer can perform joins more efficiently. If you specify DISTSTYLE KEY, you must name a DISTKEY column. + ALL: A copy of the entire table is distributed to every node. This distribution style ensures that all the rows required for any join are available on every node, but it multiplies storage requirements and increases the load and maintenance times for the table. ALL distribution can improve execution time when used with certain dimension tables where KEY distribution isn't appropriate, but performance improvements must be weighed against maintenance costs. DISTKEY (*column*) Specifies a column name or positional number for the distribution key. Use the name specified in either the optional column list for the table or the select list of the query. Alternatively, use a positional number, where the first column selected is 1, the second is 2, and so on. Only one column in a table can be the distribution key: + If you declare a column as the DISTKEY column, DISTSTYLE must be set to KEY or not set at all. + If you don't declare a DISTKEY column, you can set DISTSTYLE to EVEN. + If you don't specify DISTKEY or DISTSTYLE, CTAS determines the distribution style for the new table based on the query plan for the SELECT clause. For more information, see [Inheritance of column and table attributes](r_CTAS_usage_notes.md#r_CTAS_usage_notes-inheritance-of-column-and-table-attributes). You can define the same column as the distribution key and the sort key; this approach tends to accelerate joins when the column in question is a joining column in the query. [ COMPOUND \$1 INTERLEAVED ] SORTKEY ( *column\$1name* [, ... ] ) Specifies one or more sort keys for the table. When data is loaded into the table, the data is sorted by the columns that are designated as sort keys. You can optionally specify COMPOUND or INTERLEAVED sort style. The default is COMPOUND. For more information, see [Sort keys](t_Sorting_data.md). You can define a maximum of 400 COMPOUND SORTKEY columns or 8 INTERLEAVED SORTKEY columns per table. If you don't specify SORTKEY, CTAS determines the sort keys for the new table based on the query plan for the SELECT clause. For more information, see [Inheritance of column and table attributes](r_CTAS_usage_notes.md#r_CTAS_usage_notes-inheritance-of-column-and-table-attributes). COMPOUND Specifies that the data is sorted using a compound key made up of all of the listed columns, in the order they are listed. A compound sort key is most useful when a query scans rows according to the order of the sort columns. The performance benefits of sorting with a compound key decrease when queries rely on secondary sort columns. You can define a maximum of 400 COMPOUND SORTKEY columns per table. INTERLEAVED Specifies that the data is sorted using an interleaved sort key. A maximum of eight columns can be specified for an interleaved sort key. An interleaved sort gives equal weight to each column, or subset of columns, in the sort key, so queries don't depend on the order of the columns in the sort key. When a query uses one or more secondary sort columns, interleaved sorting significantly improves query performance. Interleaved sorting carries a small overhead cost for data loading and vacuuming operations. AS *query* Any query (SELECT statement) that Amazon Redshift supports. # CTAS usage notes ## Limits Amazon Redshift enforces a quota of the number of tables per cluster by node type. The maximum number of characters for a table name is 127. The maximum number of columns you can define in a single table is 1,600. ## Inheritance of column and table attributes CREATE TABLE AS (CTAS) tables don't inherit constraints, identity columns, default column values, or the primary key from the table that they were created from. You can't specify column compression encodings for CTAS tables. Amazon Redshift automatically assigns compression encoding as follows: + Columns that are defined as sort keys are assigned RAW compression. + Columns that are defined as BOOLEAN, REAL, DOUBLE PRECISION, GEOMETRY, or GEOGRAPHY data type are assigned RAW compression. + Columns that are defined as SMALLINT, INTEGER, BIGINT, DECIMAL, DATE, TIME, TIMETZ, TIMESTAMP, or TIMESTAMPTZ are assigned AZ64 compression. + Columns that are defined as CHAR, VARCHAR, or VARBYTE are assigned LZO compression. For more information, see [Compression encodings](c_Compression_encodings.md) and [Data types](c_Supported_data_types.md). To explicitly assign column encodings, use [CREATE TABLE](r_CREATE_TABLE_NEW.md). CTAS determines distribution style and sort key for the new table based on the query plan for the SELECT clause. For complex queries, such as queries that include joins, aggregations, an order by clause, or a limit clause, CTAS makes a best effort to choose the optimal distribution style and sort key based on the query plan. **Note** For best performance with large datasets or complex queries, we recommend testing using typical datasets. You can often predict which distribution key and sort key CTAS chooses by examining the query plan to see which columns, if any, the query optimizer chooses for sorting and distributing data. If the top node of the query plan is a simple sequential scan from a single table (XN Seq Scan), then CTAS generally uses the source table's distribution style and sort key. If the top node of the query plan is anything other a sequential scan (such as XN Limit, XN Sort, XN HashAggregate, and so on), CTAS makes a best effort to choose the optimal distribution style and sort key based on the query plan. For example, suppose you create five tables using the following types of SELECT clauses: + A simple select statement + A limit clause + An order by clause using LISTID + An order by clause using QTYSOLD + A SUM aggregate function with a group by clause. The following examples show the query plan for each CTAS statement. ``` explain create table sales1_simple as select listid, dateid, qtysold from sales; QUERY PLAN ---------------------------------------------------------------- XN Seq Scan on sales (cost=0.00..1724.56 rows=172456 width=8) (1 row) explain create table sales2_limit as select listid, dateid, qtysold from sales limit 100; QUERY PLAN ---------------------------------------------------------------------- XN Limit (cost=0.00..1.00 rows=100 width=8) -> XN Seq Scan on sales (cost=0.00..1724.56 rows=172456 width=8) (2 rows) explain create table sales3_orderbylistid as select listid, dateid, qtysold from sales order by listid; QUERY PLAN ------------------------------------------------------------------------ XN Sort (cost=1000000016724.67..1000000017155.81 rows=172456 width=8) Sort Key: listid -> XN Seq Scan on sales (cost=0.00..1724.56 rows=172456 width=8) (3 rows) explain create table sales4_orderbyqty as select listid, dateid, qtysold from sales order by qtysold; QUERY PLAN ------------------------------------------------------------------------ XN Sort (cost=1000000016724.67..1000000017155.81 rows=172456 width=8) Sort Key: qtysold -> XN Seq Scan on sales (cost=0.00..1724.56 rows=172456 width=8) (3 rows) explain create table sales5_groupby as select listid, dateid, sum(qtysold) from sales group by listid, dateid; QUERY PLAN ---------------------------------------------------------------------- XN HashAggregate (cost=3017.98..3226.75 rows=83509 width=8) -> XN Seq Scan on sales (cost=0.00..1724.56 rows=172456 width=8) (2 rows) ``` To view the distribution key and sort key for each table, query the PG\$1TABLE\$1DEF system catalog table, as shown following. ``` select * from pg_table_def where tablename like 'sales%'; tablename | column | distkey | sortkey ----------------------+------------+---------+--------- sales | salesid | f | 0 sales | listid | t | 0 sales | sellerid | f | 0 sales | buyerid | f | 0 sales | eventid | f | 0 sales | dateid | f | 1 sales | qtysold | f | 0 sales | pricepaid | f | 0 sales | commission | f | 0 sales | saletime | f | 0 sales1_simple | listid | t | 0 sales1_simple | dateid | f | 1 sales1_simple | qtysold | f | 0 sales2_limit | listid | f | 0 sales2_limit | dateid | f | 0 sales2_limit | qtysold | f | 0 sales3_orderbylistid | listid | t | 1 sales3_orderbylistid | dateid | f | 0 sales3_orderbylistid | qtysold | f | 0 sales4_orderbyqty | listid | t | 0 sales4_orderbyqty | dateid | f | 0 sales4_orderbyqty | qtysold | f | 1 sales5_groupby | listid | f | 0 sales5_groupby | dateid | f | 0 sales5_groupby | sum | f | 0 ``` The following table summarizes the results. For simplicity, we omit cost, rows, and width details from the explain plan. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_CTAS_usage_notes.html) You can explicitly specify distribution style and sort key in the CTAS statement. For example, the following statement creates a table using EVEN distribution and specifies SALESID as the sort key. ``` create table sales_disteven diststyle even sortkey (salesid) as select eventid, venueid, dateid, eventname from event; ``` ## Compression encoding ENCODE AUTO is used as the default for tables. Amazon Redshift automatically manages compression encoding for all columns in the table. ## Distribution of incoming data When the hash distribution scheme of the incoming data matches that of the target table, no physical distribution of the data is actually necessary when the data is loaded. For example, if a distribution key is set for the new table and the data is being inserted from another table that is distributed on the same key column, the data is loaded in place, using the same nodes and slices. However, if the source and target tables are both set to EVEN distribution, data is redistributed into the target table. ## Automatic ANALYZE operations Amazon Redshift automatically analyzes tables that you create with CTAS commands. You do not need to run the ANALYZE command on these tables when they are first created. If you modify them, you should analyze them in the same way as other tables. # CTAS examples The following example creates a table called EVENT\$1BACKUP for the EVENT table: ``` create table event_backup as select * from event; ``` The resulting table inherits the distribution and sort keys from the EVENT table. ``` select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 'event_backup'; column | type | encoding | distkey | sortkey ----------+-----------------------------+----------+---------+-------- catid | smallint | none | false | 0 dateid | smallint | none | false | 1 eventid | integer | none | true | 0 eventname | character varying(200) | none | false | 0 starttime | timestamp without time zone | none | false | 0 venueid | smallint | none | false | 0 ``` The following command creates a new table called EVENTDISTSORT by selecting four columns from the EVENT table. The new table is distributed by EVENTID and sorted by EVENTID and DATEID: ``` create table eventdistsort distkey (1) sortkey (1,3) as select eventid, venueid, dateid, eventname from event; ``` The result is as follows: ``` select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 'eventdistsort'; column | type | encoding | distkey | sortkey ---------+------------------------+----------+---------+------- eventid | integer | none | t | 1 venueid | smallint | none | f | 0 dateid | smallint | none | f | 2 eventname | character varying(200)| none | f | 0 ``` You could create exactly the same table by using column names for the distribution and sort keys. For example: ``` create table eventdistsort1 distkey (eventid) sortkey (eventid, dateid) as select eventid, venueid, dateid, eventname from event; ``` The following statement applies even distribution to the table but doesn't define an explicit sort key. ``` create table eventdisteven diststyle even as select eventid, venueid, dateid, eventname from event; ``` The table doesn't inherit the sort key from the EVENT table (EVENTID) because EVEN distribution is specified for the new table. The new table has no sort key and no distribution key. ``` select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 'eventdisteven'; column | type | encoding | distkey | sortkey ----------+------------------------+----------+---------+--------- eventid | integer | none | f | 0 venueid | smallint | none | f | 0 dateid | smallint | none | f | 0 eventname | character varying(200) | none | f | 0 ``` The following statement applies even distribution and defines a sort key: ``` create table eventdistevensort diststyle even sortkey (venueid) as select eventid, venueid, dateid, eventname from event; ``` The resulting table has a sort key but no distribution key. ``` select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 'eventdistevensort'; column | type | encoding | distkey | sortkey ----------+------------------------+----------+---------+------- eventid | integer | none | f | 0 venueid | smallint | none | f | 1 dateid | smallint | none | f | 0 eventname | character varying(200) | none | f | 0 ``` The following statement redistributes the EVENT table on a different key column from the incoming data, which is sorted on the EVENTID column, and defines no SORTKEY column; therefore the table isn't sorted. ``` create table venuedistevent distkey(venueid) as select * from event; ``` The result is as follows: ``` select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 'venuedistevent'; column | type | encoding | distkey | sortkey ----------+-----------------------------+----------+---------+------- eventid | integer | none | f | 0 venueid | smallint | none | t | 0 catid | smallint | none | f | 0 dateid | smallint | none | f | 0 eventname | character varying(200) | none | f | 0 starttime | timestamp without time zone | none | f | 0 ```