CODEWITHSUNDEEP
amazon-redshift
Martin Taleski
Martin Taleski

Reputation: 6458

How to change column ordering in Amazon Redshift

Is there a way to change the column ordering in Amazon Redshift (or Postgres, since it is based on it)? Or maybe add a column at a specific position?

In mysql there you can do: 

ALTER TABLE MY_TABLE
ADD COLUMN {NEW_COL} AFTER {EXISTING_COL}

But this does not work in Redshift. Any ideas?

Upvotes: 14

Views: 36185

Answers (5)

B M
B M

Reputation: 21

Given old_table (oldcolumn1 int, oldcolumn2 int), you can do the following:

create table new_table (
  newcolumn0 int,
  oldcolumn1 int,
  oldcolumn2 int
);
alter table new_table append from old_table; --EMPTIES old_table and COMMITS!
drop table old_table;
alter table new_table rename to old_table;

This approach is not for the faint of heart, read the docs on ALTER TABLE APPEND. https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE_APPEND.html

If that's OK, the pros are: ..doesn't care about column order for columns with the same name/type ..doesn't require copying out ..should be quicker than insert..select for large tables

Upvotes: 2

Shubham Gupta
Shubham Gupta

Reputation: 434

You can simply create a new table in Redshift with the required ordering

CREATE TABLE temp_table_name (column1 dtype1, column2 dtype2, column3 dtype 3 ...);

and insert data from the source table in the required order.

INSERT INTO temp_table_name (SELECT column1, column2, column3 ... FROM table_name);

Then drop the original table

DROP TABLE table_name;

and rename the temp table to the original table

ALTER TABLE temp_table_name RENAME TO table_name;

Upvotes: 1

Red Boy
Red Boy

Reputation: 5739

Redshift doesn't support ordering at all. I have to solve same problem in my case, and this is how I have done it.

Best option is following unload,alter the table by drop and re-create.

1)Unload to S3

unload ('select (Column1,column2,Column3,...,Column_n) from orginal_table') to 's3://<your_bucket>/<your_file>' CREDENTIALS 'aws_access_key_id=<your_key>;aws_secret_access_key=<your_secret>' MANIFEST  DELIMITER '|'  GZIP   NULL AS 'null_string' ESCAPE ALLOWOVERWRITE;

2)Drop AND/Or re-create

Create duplicate_table(Column1,column2,Column3,...,Column_n);**with new sequence make sure your seq

3)Reload.

copy duplicate_table(Column1,column2,Column3,...,Column_n) from  's3://<your_bucket>/<your_file>manifest' CREDENTIALS 'aws_access_key_id=<your_key>;aws_secret_access_key=<your_secret>' MANIFEST  DELIMITER '|'  GZIP   NULL AS 'null_string' ESCAPE ALLOWOVERWRITE;

Upvotes: 1

Mark Gerolimatos
Mark Gerolimatos

Reputation: 2584

The answer is no, redshift does not (easily) support column shuffling, which is strange, as I believe tables are stored as individual columns. There is no way to do this without unloading/loading or table copying.

It is said that the unload/load is the preferred method, as it will take advantage of any parallelism you configured into your table.

Thus, the standard methodology is necessary:

There may be a "secret way" to do this with only the one column in question (dump column, drop column, add column, reload column), but that sounds incredibly sketchy and should be avoided.

Upvotes: 7

IMSoP
IMSoP

Reputation: 98005

From your comments, it seems that what you actually need is to be able to COPY into a table from a file which has columns in a particular order.

According to the Redshift documentation for the COPY command:

(column1 [, column2, ...])

Specifies an optional column list to load data fields into specific columns. The columns can be in any order in the COPY statement, but when loading from flat files, such as in an Amazon S3 bucket, their order must match the order of the source data. [...] If no column list is specified, the command behaves as if a complete, in-order column list was specified.

So rather than re-ordering the columns in your table, you just need to specify them in the COPY statement, as in some of the examples in the docs:

copy venue_new(venueid, venuename, venuecity, venuestate) 
from 's3://mybucket/data/venue_noseats.txt' 
credentials 'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>'
delimiter '|';

Upvotes: 11

Related Questions