amazon-redshift - 如何更改 Amazon Redshift 中的列顺序

Question

有没有办法更改 Amazon Redshift(或 Postgres，因为它基于它)中的列排序？或者也许在特定位置添加一列？

在 mysql 中，您可以执行以下操作:

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

但这在 Redshift 中不起作用。有任何想法吗？

Answer 1

从你的评论看来，你真正需要的是能够COPY从具有特定顺序的列的文件中转换为表。

根据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.

因此，您只需在 COPY 中指定它们，而不是重新排序表中的列。声明，如 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 '|';