CODEWITHSUNDEEP
sqlvertica
Michael Spector
Michael Spector

Reputation: 37019

Is there a way to set AUTO_INCREMENT property on existing table column in Vertica?

Suppose I have a simple table:

CREATE TABLE user(
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(32) NOT NULL,
)

Is there a way to alter this table so id will become AUTO_INCREMENT field?

I tried the following with no luck:

Thanks!

Upvotes: 4

Views: 1679

Answers (1)

Kermit
Kermit

Reputation: 34063

I would try to just rank the rows, and use the sequence for future inserts.

\set AUTOCOMMIT 'on'

CREATE TABLE t1 (
    val char(1)
);

INSERT INTO t1 VALUES ('a');
INSERT INTO t1 VALUES ('b');
INSERT INTO t1 VALUES ('c');
INSERT INTO t1 VALUES ('d');

CREATE TABLE t2 (
    id int,
    val char(1)
);

INSERT INTO t2 (val, id)
SELECT val, RANK() OVER (ORDER BY val) as id
FROM t1;

SELECT * FROM t2;

We get:

 id | val
----+-----
  1 | a
  3 | c
  2 | b
  4 | d

Success!

Let's prepare the table for future inserts:

-- get the value to start sequence at
SELECT MAX(id) FROM t2;

-- create the sequence
CREATE SEQUENCE seq1 START 5;

-- syntax as of 6.1
-- modify the column to add next value for future rows
ALTER TABLE t2 ALTER COLUMN id SET DEFAULT NEXTVAL('seq1');

Quick test:

INSERT INTO t2 (val) VALUES ('e');
INSERT INTO t2 (val) VALUES ('f');

SELECT * FROM t2;

We get:

 id | val
----+-----
  4 | d
  2 | b
  3 | c
  6 | f
  1 | a
  5 | e

Hope this helps.

Upvotes: 7

Related Questions