Redshift psql auto increment on even number

Question

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:

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.`

My create table statement looks like this:

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:

 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!

Answer 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

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

Answer 2

Set your seed value to 1 and your step value to 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.

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  |