CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
MaxGabriel
MaxGabriel

Reputation: 7707

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

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.

Upvotes: 1

Views: 8867

Answers (2)

Joe Harris
Joe Harris

Reputation: 14035

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)
;

Upvotes: 12

Sandesh Deshmane
Sandesh Deshmane

Reputation: 2305

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)

Upvotes: 0

Related Questions