Auto Increment Column Options in Different Databases

• Download source code - 1.7 KB

Background

SQLServer database IDENTITY(1, 1) column will automatically generate and populate a numeric column value each time a new row is inserted into a table. In PostgreSQL, Oracle, and MySql, things are a bit different and there are some alternative ways. Here, we are going to take a quick look at some of the options.

PostgreSQL

SERIAL

This is available from v8:

DROP TABLE IF EXISTS tbl_users;

CREATE TABLE tbl_users (
    id SERIAL,
    name VARCHAR NULL,
    
    CONSTRAINT pk_tbl_users PRIMARY KEY(id) 
);

INSERT INTO tbl_users(name) VALUES ('user1');
INSERT INTO tbl_users(id, name) VALUES (DEFAULT, 'user2');

SELECT * FROM tbl_users;

We can also use SMALLSERIAL, BIGSERIAL.

Name	Storage Size	Range
SMALLSERIAL	2 bytes	1 to 32,767
SERIAL	4 bytes	1 to 2,147,483,647
BIGSERIAL	8 bytes	1 to 9,223,372,036,854,775,807

SEQUENCE

This is available from v9.1 and similar to Oracle.

DROP TABLE IF EXISTS tbl_users;
DROP SEQUENCE IF EXISTS seq_tbl_users_id;

CREATE SEQUENCE seq_tbl_users_id;
CREATE TABLE tbl_users (
    id INT NOT NULL DEFAULT nextval('seq_tbl_users_id'),
    name VARCHAR NULL,
    
    CONSTRAINT pk_tbl_users PRIMARY KEY(id) 
);

INSERT INTO tbl_users(name) VALUES ('user1');
INSERT INTO tbl_users(id, name) VALUES (nextval('seq_tbl_users_id'), 'user2');

SELECT * FROM tbl_users;

IDENTITY

This is available from v10.

DROP TABLE IF EXISTS tbl_users;

CREATE TABLE tbl_users (
    id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
    name VARCHAR NULL,
    
    CONSTRAINT pk_tbl_users PRIMARY KEY(id) 
);

INSERT INTO tbl_users(name) VALUES ('user1');
INSERT INTO tbl_users(id, name) VALUES (DEFAULT, 'user2');

SELECT * FROM tbl_users;

Oracle

SEQUENCE

CREATE SEQUENCE SEQ_USER_ID
MINVALUE 1
MAXVALUE 2147483647
START WITH 1
INCREMENT BY 1
CACHE 10;

CREATE TABLE TBL_USER (
    ID INT NOT NULL,
    NAME VARCHAR(255) NULL,
  
    CONSTRAINT PK_TBL_USER PRIMARY KEY (ID)
);

INSERT INTO TBL_USER(ID, NAME) VALUES (SEQ_USER_ID.NEXTVAL, 'user2');

SELECT * FROM TBL_USER;
SELECT SEQ_USER_ID.NEXTVAL AS ID FROM DUAL;

DROP TABLE TBL_USER;
DROP SEQUENCE SEQ_USER_ID;

IDENTITY

This is available from 12c.

CREATE TABLE TBL_USER (
    ID INT GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 1 INCREMENT BY 1,
    NAME VARCHAR(255) NULL,
  
    CONSTRAINT PK_TBL_USER PRIMARY KEY (ID)
);

INSERT INTO TBL_USER(NAME) VALUES ('user1');
INSERT INTO TBL_USER(NAME) VALUES ('user2');

SELECT * FROM TBL_USER;

DROP TABLE TBL_USER;

SQLServer

IDENTITY

DROP TABLE IF EXISTS TblUser;

CREATE TABLE TblUser (
    Id INT IDENTITY(1, 1),
    [Name] VARCHAR(MAX) NULL,
    
    CONSTRAINT PK_TblUser PRIMARY KEY(Id)
);

INSERT INTO TblUser([Name]) VALUES ('user1');

SELECT * FROM TblUser;

MySQL

AUTO_INCREMENT

This is available from v5.6.

DROP TABLE IF EXISTS tbl_user;

CREATE TABLE tbl_user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NULL,
  
    CONSTRAINT PK_tbl_user PRIMARY KEY (id)
);

INSERT INTO tbl_user(NAME) VALUES ('user1');

SELECT * FROM tbl_user;

SQLite

AUTOINCREMENT

This is available from v3.26.

DROP TABLE IF EXISTS tbl_user;

CREATE TABLE tbl_user
(
    Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name VARCHAR NULL
);

INSERT INTO tbl_user(name) VALUES ('user1');

SELECT * FROM tbl_user;

Get Inserted Id

PostgreSQL

INSERT INTO tbl_users(name) VALUES ('user1') RETURNING id;            --postgresql

Oracle

DECLARE                                                               --oracle
    INSERTED_ID INT;
BEGIN
    INSERT INTO TBL_USER(NAME) VALUES ('user1') RETURNING ID INTO INSERTED_ID;
    DBMS_OUTPUT.PUT_LINE(INSERTED_ID);
END;
/

SQLServer

INSERT INTO TblUser([Name]) OUTPUT INSERTED.Id VALUES ('user1');      --sqlserver

MySQL

INSERT INTO tbl_user(NAME) VALUES ('user1');
SELECT LAST_INSERT_ID() AS id;                                        --mysql

SQLite

INSERT INTO tbl_user(name) VALUES ('user1');
SELECT last_insert_rowid() Id;                                        --sqlite

Check Database Version

Here are a few SQL queries to check our working database versions:

SELECT @@VERSION;            /*sql server*/       /*also works with mysql*/
SELECT * FROM V$VERSION;     /*oracle*/
SELECT VERSION();            /*mysql*/            /*also works with postgre*/
SELECT VERSION();            /*postgre sql*/      /*also works with mysql*/

Online Playground

• http://sqlfiddle.com/
• https://www.db-fiddle.com/
• https://dbfiddle.uk/

References

PostgreSQL

• https://www.postgresql.org/docs/14/datatype.html

Oracle

• https://www.oracletutorial.com/oracle-basics/oracle-identity-column/

History

• 5^th July, 2022: Initial version