How to reorder columns in Amazon Redshift

Question

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

In mysql you can do:

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

But this does not work in Redshift. Any ideas?

Answer 1

From your comments, it seems that you really need to be able to COPY into a table from a file with columns in a specific order.

According to the Redshift Documentation for the COPY :

(column1 [, column2, ...])

Specifies an optional column list for loading data fields into specific columns. Columns can be in any order in the COPY instruction, but when loading from flat files, for example, into an Amazon S3 bucket, their order should correspond to the order of the source data. [...] If the list of columns is not specified, the command behaves as if the complete list of columns was specified in order.

Therefore, instead of reordering the columns in your table, you just need to specify them in the COPY statement, as in some examples in the documents :

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

Answer 2

Answer: no, redshift does not allow (easily) supporting shuffling of columns, which is strange, since I believe that tables are stored as separate columns. It is not possible to do this without uploading / downloading or copying the table.

Upload / download is said to be the preferred method, as it will use any parallelism that you have configured in your table.

Thus, a standard methodology is needed:

• unload source table
• create a new table
• load a new table with copy syntax ... BE CAREFULLY treat column qualifiers according to another answer (at the time of writing)
• delete old table
• rename new table to old table
• remove gigantoid S3 bucket with your data, so you don’t have to pay for it

There may be a “secret way” to this, using only one column (dump column, dump column, add column, reload column), but this sounds incredibly sketchy and should be avoided.

Answer 3

Redshift does not maintain order at all. I have to solve the same problem in my case, and that is how I did it.

The best option is to upload, modify the table behind the scenes, and recreate it.

1) Upload 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 recreate

 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;