Auto Increment Column in Greenplum

We can create auto increment column in Greenplum which insert the value in the column automatically.

Syntax is:-

CREATE TABLE TABLE_NAME
(
COLUMN_NAME DATA_TYPE NULL || NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH [INTEGER], INCREMENT BY [INTEGER], MAXVALUE [INTEGER]),
COLUMN_NAME DATA_TYPE 
COLUMN_NAME DATA_TYPE,
....
);

While creating IDENTITY or AUTO INCREMENT column, make sure you have specified number families data type like int, number, decimal etc.

Let's have an example:-

CREATE TABLE EMPLOYEE
(
EMPNO INT PRIMARY KEY DEFAULT nextval('serial'),
EMPNAME TEXT(20),
SALARY INT,
DEPTNO INT
);

When you will insert a value into the table, it will insert 1 in the EMPNO column and further increment by 1.

When you insert a value into the table you would need to mention the column list. If you want to insert data into
the above table then you will have to use the statement something like below:-

INSERT INTO EMPLOYEE (EMPNAME,SALARY,DEPTNO)
VALUES ('CHANCHAL',1000,10);

Read Also:-  Auto Increment in SQL