How to auto generate and auto increment identifier values for PostGreSQL tables through Hibernate?

I was trying to connect to PostGreSQL database through Spring Boot.

I used spring-boot-starter-data-jpa dependency which automatically included hibernate dependencies to the project.

And since I was connecting to postgresql database I included the following postgresql dependency as well:

		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>

I created a simple table named my_table with an integer ID and a JSON data type like below . The corresponding entity class is as below:

package com.mapping.jsontype;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.Type;

@Entity
@Table(name = "MY_TABLE", schema = "public")
@org.hibernate.annotations.TypeDef(name = "JsonType", typeClass = JsonType.class)
public class MyTable {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	@Column(name = "data")
	@Type(type = "JsonType")
	private Person data;

	/**
	 * @return the id
	 */
	public Integer getId() {
		return id;
	}

	/**
	 * @param id the id to set
	 */
	public void setId(Integer id) {
		this.id = id;
	}

	/**
	 * @return the data
	 */
	public Person getData() {
		return data;
	}

	/**
	 * @param data the data to set
	 */
	public void setData(Person data) {
		this.data = data;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		return "MyTable [id=" + id + ", data=" + data + "]";
	}

}

Ignore the ‘Person’ class and the JSON related TypeDefs , this post is just about automatically creating identifiers for postgresql tables .

Note that I used the strategy : GenerationType.IDENTITY to generate the unique identifiers automatically.

I created the table in PostGreSQL database using the below CREATE query:

CREATE TABLE MY_TABLE(ID INTEGER PRIMARY KEY NOT NULL, DATA JSON NOT NULL);

ID is the unique identifier and is of integer type. It got created fine.

When I tried to insert a data through the application though , it threw error. It said identifier should be manually assigned.

In databases like My SQL , this works if you specify the keyword AUTO_INCREMENT next to the primary key definition while creating the table.

But PostGreSQL doesn’t support that.

Instead PostGreSQL came up with a new data type called SERIAL.

This is not a true data type just a notational convenience for creating unique identifier columns. So I dropped the table and changed the CREATE statement to this :

CREATE TABLE MY_TABLE(ID SERIAL PRIMARY KEY NOT NULL, DATA JSON NOT NULL);

And then when I tried to insert data through the application with the same Entity mapping it worked!

In short ,

To automatically generate a unique identifier in postgresql table use a SERIAL type. If you want a higher range use BIGSERIAL type.

Here is what PostGreSQL says in its documentation:

“The data types serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

“