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;
“
Leave a Reply