Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

CTAS Examples

The following example creates a table called EVENT_BACKUP for the EVENT table:

Copy
create table event_backup as select * from event;

The resulting table inherits the distribution and sort keys from the EVENT table.

Copy
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:

Copy
create table eventdistsort distkey (1) sortkey (1,3) as select eventid, venueid, dateid, eventname from event;

The result is as follows:

Copy
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:

Copy
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 does not define an explicit sort key:

Copy
create table eventdisteven diststyle even as select eventid, venueid, dateid, eventname from event;
The table does not 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.
Copy
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:

Copy
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.
Copy
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 is not sorted.

Copy
create table venuedistevent distkey(venueid) as select * from event;

The result is as follows:

Copy
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