How can I copy a Redshift table but add a sortkey to a column?

Question

I'm currently working on a project that uses a Redshift table with 51 columns. However, the person who made the table forgot to add a sortkey to our time column which will hurt performance for our use case if we don't add it.

How can I make a version of the table with our time column as the sortkey? I'm aware that you can't make a column a sortkey if its a member of an existing table, but I was hoping there's a way to do it that doesn't involve writing out the CREATE TABLE syntax by hand; for example, something like this would be nice:

timecube=# CREATE TABLE foo (like bar) sortkey(time);
ERROR:  CREATE TABLE LIKE is not supported with DISTSTYLE, DISTKEY(), or SORTKEY() clauses

but as you can see its not supported. Is there another way? As we're still developing we don't need any of existing data.

Using traditional tools like pgdump didn't work well because they don't include any of the Redshift extras like encoding.

Joe Harris · Answer 1 · 2019-05-14T20:30:17.357

12

Redshift supports specifying the DIST and SORT keys as part of CREATE TABLE AS statements, as per the docs.

CREATE TABLE table_name
DISTSTYLE KEY
DISTKEY ( column )
SORTKEY ( column )
AS 
(SELECT * 
FROM source_table)
;

edited May 14 '19 at 20:30

answered Sep 04 '14 at 17:10

Joe Harris

13,671
4
47
54

i've added brackets around the select clause (it doesn't work without them) – maxymoo Mar 24 '16 at 05:17
Welp, it works for me, e.g., in `psql`, but some SQL clients won't submit it. – Joe Harris Mar 25 '16 at 13:03

score 0 · Answer 2 · answered Aug 26 '14 at 05:44

First step you need to do use get create table statement for existing table. Then create new table this time add sort key to new table.

Check encoding for old table ( when you load data using copy command it automatically adds compression encodings)

select "column", type, encoding 
from pg_table_def where tablename = 'old_table'

When creating new table add encoding type for each column. Create table with Sort key .

Once new table is created use below command

insert into new table ( select * from old table order by time asc)

How can I copy a Redshift table but add a sortkey to a column?

2 Answers2