如何复制身份字段? [英] How can I copy an IDENTITY field?

问题描述

我想更新表的一些参数，例如dist和sort键.为此，我已重命名了该表的旧版本，并使用新参数重新创建了该表(创建表后将无法更改这些参数).

我需要保留旧表的ID字段，它是IDENTITY字段.但是，如果尝试以下查询，则会收到错误消息:

insert into edw.my_table_new select * from edw.my_table_old;
ERROR: cannot set an identity column to a value [SQL State=0A000] 

如何从旧表中保留相同的ID?

解决方案

您无法 INSERT 数据设置 IDENTITY 列，但是可以使用以下命令从S3加载数据 COPY 命令.

首先，您需要使用 UNLOAD创建源表的转储.

然后只需使用 复制 与 加载中所述默认列值:

如果列列表中包含IDENTITY列，则EXPLICIT_IDS 选项还必须在COPY命令或COPY命令中指定 将失败.同样，如果从该列中省略了IDENTITY列 列表，并指定了EXPLICIT_IDS选项，即COPY操作 将失败.

I’d like to update some parameters for a table, such as the dist and sort key. In order to do so, I’ve renamed the old version of the table, and recreated the table with the new parameters (these can not be changed once a table has been created).

I need to preserve the id field from the old table, which is an IDENTITY field. If I try the following query however, I get an error:

insert into edw.my_table_new select * from edw.my_table_old;
ERROR: cannot set an identity column to a value [SQL State=0A000] 

How can I keep the same id from the old table?

解决方案

You can't INSERT data setting the IDENTITY columns, but you can load data from S3 using COPY command.

First you will need to create a dump of source table with UNLOAD.

Then simply use COPY with EXPLICIT_IDS parameter as described in Loading default column values:

If an IDENTITY column is included in the column list, the EXPLICIT_IDS option must also be specified in the COPY command, or the COPY command will fail. Similarly, if an IDENTITY column is omitted from the column list, and the EXPLICIT_IDS option is specified, the COPY operation will fail.

这篇关于如何复制身份字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！