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;
“