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?

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