Redshift psql自动增加偶数

I am trying to create a table with an auto-increment column as below. Since Redshift psql doesn't support SERIAL, I had to use IDENTITY data type:

我正在尝试创建一个带有自动增量列的表，如下所示。由于Redshift psql不支持SERIAL，我不得不使用IDENTITY数据类型：

IDENTITY(seed, step)
Clause that specifies that the column is an IDENTITY column. An IDENTITY column contains unique auto-generated values. These values start with the value specified as seed and increment by the number specified as step. The data type for an IDENTITY column must be either INT or BIGINT.`

IDENTITY（seed，step）指定该列是IDENTITY列的子句。 IDENTITY列包含唯一的自动生成值。这些值以指定为seed的值开始，并按指定为step的数字递增。 IDENTITY列的数据类型必须是INT或BIGINT

My create table statement looks like this:

我的create table语句如下所示：

CREATE TABLE my_table(
        id INT IDENTITY(1,1),
        name CHARACTER VARYING(255) NOT NULL,
        PRIMARY KEY( id ) 
);

However, when I tried to insert data into my_table, rows increment only on the even number, like below:

但是，当我尝试将数据插入my_table时，行仅在偶数上增加，如下所示：

 id | name | 
----+------+
  2 | anna |
  4 | tom  |
  6 | adam |
  8 | bob  |
 10 | rob  |

My insert statements look like below:

我的插入语句如下所示：

INSERT INTO my_table ( name ) 
VALUES ( 'anna' ), ('tom') , ('adam') , ('bob') , ('rob' );

I am also having trouble with bringing the id column back to start with 1. There are solutions for SERIAL data type, but I haven't seen any documentation for IDENTITY. Any suggestions would be much appreciated!

我也很难将id列重新开始1.有SERIAL数据类型的解决方案，但我还没有看到IDENTITY的任何文档。我们欢迎所有的建议！

3 个解决方案

#1

You have to set your identity as follows:

您必须按如下方式设置您的身份：

id INT IDENTITY(0,1)

Source: http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_examples.html

资料来源：http：//docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_examples.html

And you can't reset the id to 0. You will have to drop the table and create it back again.

并且您无法将ID重置为0.您必须删除该表并再次创建它。

#2

This issue is discussed at length in AWS forum.

此问题将在AWS论坛中详细讨论。

https://forums.aws.amazon.com/message.jspa?messageID=623201

https://forums.aws.amazon.com/message.jspa?messageID=623201

The answer from the AWS.

来自AWS的答案。

Short answer to your question is seed and step are only honored if you disable both parallelism and the COMPUPDATE option in your COPY. Parallelism is disabled if and only if you're loading your data from a single file, which is what we normally do not recommend, and hence will be an unlikely scenario for most users.

如果在COPY中同时禁用并行性和COMPUPDATE选项，那么您的问题的简短答案就是种子和步骤。当且仅当您从单个文件加载数据时才会禁用并行性，这是我们通常不建议的，因此对于大多数用户来说是不太可能的情况。

Parallelism impacts things because in order to ensure that there is no single point of contention in assigning identity values to rows, there end up being gaps in the value assignment. When parallelism is disabled, the load is happening serially, and therefore, there is no issue with assigning different id values in parallel.

并行性会影响事物，因为为了确保在为行分配标识值时没有单一的争用点，最终会在值赋值中出现间隙。当并行性被禁用时，负载是连续发生的，因此，并行分配不同的id值没有问题。

The reason COMPUPDATE impacts things is when it's enabled, the COPY is actually making 2 passes over your data. During the first pass, it internally increments the identity values, and as a result, your initial value starts with a larger value than you'd expect.

COMPUPDATE影响事物的原因是，当它启用时，COPY实际上会对您的数据进行2次传递。在第一次传递期间，它在内部递增标识值，因此，您的初始值以比您预期的值更大的值开始。

We'll update the doc to reflect this.

我们将更新文档以反映这一点。

Also multiple nodes seems to cause such effect with IDENTITY column. In essence it can only provide you with guaranteed unique IDs.

此外，多个节点似乎通过IDENTITY列导致此类效果。从本质上讲，它只能为您提供有保证的唯一ID。

#3

Set your seed value to 1 and your step value to 1.

将种子值设置为1，将步长值设置为1。

Create table

创建表格

CREATE table my_table(
    id bigint identity(1, 1),
    name varchar(100),
    primary key(id));

Insert rows

插入行

INSERT INTO organization ( name ) 
VALUES ('anna'), ('tom') , ('adam'), ('bob'), ('rob');

Results

结果

 id | name | 
----+------+
  1 | anna |
  2 | tom  |
  3 | adam |
  4 | bob  |
  5 | rob  |

For some reason, if you set your seed value to 0 and your step value to 1 then the integer will increase in steps of 2.

出于某种原因，如果将种子值设置为0并将步长值设置为1，则整数将以2为步长增加。

Create table

创建表格

CREATE table my_table(
    id bigint identity(0, 1),
    name varchar(100),
    primary key(id));

Insert rows

插入行

INSERT INTO organization ( name ) 
VALUES ('anna'), ('tom') , ('adam'), ('bob'), ('rob');

Results

结果

 id | name | 
----+------+
  0 | anna |
  2 | tom  |
  4 | adam |
  6 | bob  |
  8 | rob  |