在现有的redshift表中添加自动增量列 [英] Adding a auto incremental column into existing redshift table

问题描述

我在Redshift中有一张桌子.

I have a table in Redshift.

我想添加一列，该列应具有增量值.我不想放下桌子并创建一个新桌子.

I want to add a column which should have incremental values. I dont want to drop the table and create a new one.

请建议使用该命令在redshift表中添加具有自动增量值的列.

Please suggest the command to add a column having auto incremental values in redshift table.

谢谢!!!

推荐答案

使用CREATE TABLE添加带有标识列的新表，然后使用

Use CREATE TABLE to add a new table with identity column and then use ALTER TABLE APPEND with FILLTARGET modifier, and then drop the original table and rename the new table it is extremely fast and simple.

如果目标表包含源中不存在的列 表，包括FILLTARGET.该命令填充了 具有默认列值或IDENTITY值的源表， 如果已定义，则为NULL.

If the target table contains columns that don't exist in the source table, include FILLTARGET. The command fills the extra columns in the source table with either the default column value or IDENTITY value, if one was defined, or NULL.

它以极快的速度将列从一个表移动到另一个表，在dc1.large节点中1GB的表花了我4s的时间.

It moves the columns from one table to another, extremely fast, took me 4s for 1GB table in dc1.large node.

通过从现有源移动数据将行追加到目标表 表.
...
ALTER TABLE APPEND通常比类似的CREATE TABLE快得多 AS或INSERT INTO操作，因为数据已移动而不是重复.

Appends rows to a target table by moving data from an existing source table.
...
ALTER TABLE APPEND is usually much faster than a similar CREATE TABLE AS or INSERT INTO operation because data is moved, not duplicated.

/* This is your table to be modified */
CREATE TABLE t_original (a varchar);
INSERT INTO t_original VALUES ('v1'), ('v2');

/* Here are the steps to add IDENTITY column */
CREATE TABLE t_new (id  BIGINT IDENTITY(0,1), a varchar);
ALTER TABLE t_new APPEND FROM t_original FILLTARGET;
DROP TABLE t_original;
ALTER TABLE t_new RENAME TO t_original;

/* Show the result */
SELECT * FROM t_original;
 id | a
----+----
  1 | v2
  0 | v1
(2 rows)

这篇关于在现有的redshift表中添加自动增量列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！