CODEWITHSUNDEEP
amazon-web-services
azero0
azero0

Reputation: 2310

adding a sort-key in aws redshift

How to alter an already existing column in aws redshit to make it a sort-key?

like I have a column username in table users and I want to make it a sortkey

is there an sql query for this like:

"alter table users add sortkey(username)"

Thanks in advance.

Upvotes: 1

Views: 2679

Answers (3)

Main
Main

Reputation: 160

In December 2019 it is possible to add sort keys in existing Redshift table. Refer to documentation here. 

ALTER TABLE table_name
{
ADD table_constraint 
| DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ] 
| OWNER TO new_owner 
| RENAME TO new_name 
| RENAME COLUMN column_name TO new_name            
| ALTER COLUMN column_name TYPE new_data_type              
| ALTER DISTKEY column_name 
| ALTER DISTSTYLE KEY DISTKEY column_name   
| ALTER [COMPOUND] SORTKEY ( column_name [,...] )           
| ADD [ COLUMN ] column_name column_type
  [ DEFAULT default_expr ]
  [ ENCODE encoding ]
  [ NOT NULL | NULL ] |
| DROP [ COLUMN ] column_name [ RESTRICT | CASCADE ] }

where table_constraint is:

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] )  
| PRIMARY KEY ( column_name [, ... ] ) 
| FOREIGN KEY (column_name [, ... ] )
   REFERENCES  reftable [ ( refcolumn ) ]}

Upvotes: 1

Abrham Smith
Abrham Smith

Reputation: 163

Pull the create script for the current table, modify the column you want to be the sort key and change the table name to table2.

Import your data from "table1" into table2.

INSERT INTO table2
SELECT * FROM table1;

ALTER TABLE table1 RENAME TO table3;
ALTER TABLE table2 RENAME TO table1;

DROP TABLE table3;

Upvotes: 0

azero0
azero0

Reputation: 2310

Just so that other people dont struggle with this.

It is not possible to add a sortkey in aws redshift using the alter or any other command to an existing table.

Upvotes: -1

Related Questions