How to change column ordering in Amazon Redshift

Question

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?

Tosha

Purists would argue that you should never need to, because columns should be referred to by name, not order, but in practice SQL does treat columns as ordered, so it sort of makes sense to want to choose that order. The ALTER TABLE docs don't mention any way to do it, but that doesn't prove there isn't a workaround of some sort, particularly in Redshift, which uses a "column-oriented" storage model. – Phia

when importing data with a COPY command you need the column order in the table to match the column order of the CSV file. – Tosha

Probably a better link would be to the Redshift docs, since it diverged from Postgres some time ago. However, now you mention COPY, I think we may have an X/Y Problem here... – Phia

Can I ask - why do you need to change the order of the columns? The way I look at it, redshift column order should not matter. – Finale

Because what Martin said. When you copy to upload a table from s3 e.g. you need columns of the csv/parquet to be in the same order as the redshift table, otherwise it won't work – Kafir

Answer 1

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

Phia

It would be much more difficult for me to change the copy commands, than to change the column ordering. There seems to be a way to do it in postgres, I am checking if it can work in Redshift: #286233 but, thanks for the effort – Tosha

@MartinTaleski OK, thought it was worth the suggestion. Note that a view won't help for writing to the table, and the other options require manually deleting and reinserting existing data. – Phia

It does not matter what he needed, the title is "How to change column ordering in Amazon Redshift". I actually need to do this for reasons beyond the scope of this question. "Do something completely different instead" should be ADDED to the actual answer. – Malefic

Answer 2

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:

• unload original table
• create new table
• load new table with copy syntax ... PAY CAREFUL ATTENTION to the column specifiers, as per the other answer (at time of writing)
• drop old table
• rename new table to old table
• delete gigantoid S3 bucket that had your data, so you won't have to pay for it

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.

Malefic

Answer 3

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

Housekeeping

Answer 4

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;

Margaret

Redshift does support alter table. See their docs for details. It doesn't seem like they support re-ordering columns through it though. – Boarhound

Yes, agreed! Red-shift does support alter, but here I meant alter for ordering only. I have corrected my statement. – Margaret

Answer 5

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;

Lui