0

I have a Spring Boot application where I need to insert an object to a redshift table. No problem with manual insertion of data using a query. But I'm facing a challenge while persisting data to Amazon Redshift table from my Spring Boot app. Redshift table was created using the below query:

create table demand_test(id int not null identity(1,1), dateOfrequest DATE, country varchar(100), primary key(id))

So, I created a model class as shown below:

@Entity
@Table(name = "demand_test")
public class Demand implements Serializable {

    private static final long serialVersionUID = 1L;

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

    @Column
    @DateTimeFormat(pattern = "dd/MM/yyyy")
    private Date dateofrequest;

    @Column
    private String country;


    public BigInteger getId() {
        return id;
    }

    public void setId(BigInteger id) {
        this.id = id;
    }

    public Date getDateofrequest() {
        return dateofrequest;
    }

    public void setDateofrequest(Date dateofrequest) {
        this.dateofrequest = dateofrequest;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

}

When I try to insert Demand object, I get the below error:

com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: relation "demand_test_id_seq" does not exist;
    at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(ErrorResponse.java:1830) ~[redshift-jdbc42-1.2.1.1001.jar:RedshiftJDBC_1.2.1.1001]
    at com.amazon.redshift.client.PGMessagingContext.handleErrorResponse(PGMessagingContext.java:822) ~[redshift-jdbc42-1.2.1.1001.jar:RedshiftJDBC_1.2.1.1001]
    at com.amazon.redshift.client.PGMessagingContext.handleMessage(PGMessagingContext.java:647) ~[redshift-jdbc42-1.2.1.1001.jar:RedshiftJDBC_1.2.1.1001]

I see a number of posts that are similar and were posted a couple of years back with no answers. I tried to filter out this issue to Hibernate specific, but I have no luck so far on this. I have been trying out various options such as:

  • using GenerationType.AUTO
  • removing @GeneratedValue(strategy=GenerationType.IDENTITY)

I'm not sure why I'm getting this error with sequence since I'm using only IDENTITY generation type for the column.The JpaRepository save() method throws the mentioned error. What's going wrong here?

halfer
  • 19,824
  • 17
  • 99
  • 186
Rahul Raj
  • 3,197
  • 5
  • 35
  • 55
  • 1
    Does this answer your question? [Sequence does not exist when it does - Postgres/Spring Boot](https://stackoverflow.com/questions/44962937/sequence-does-not-exist-when-it-does-postgres-spring-boot) – Šimon Kocúrek Mar 18 '20 at 07:15
  • @ŠimonKocúrek I believe redshift doesnt support sequences even though it's postgresql based. Also, we didnt create sequence for the purpose. We're just using `IDENTITY` generation technique. – Rahul Raj Mar 18 '20 at 07:58
  • Rahul, could it be that the table was created before setting the ID to identity? In that case [recreating](https://stackoverflow.com/a/18342887/5521670) the schema might help. – Šimon Kocúrek Mar 18 '20 at 08:10
  • I dropped the table and ran the above mentioned query to re-create it. Then I ran my app again. But I'm still getting the issue :( – Rahul Raj Mar 18 '20 at 08:30
  • Can you make sure you also have `org.hibernate.dialect.PostgreSQL82Dialect` and `true` set, so that we can see what query is being sent? – Šimon Kocúrek Mar 18 '20 at 08:33
  • These are the current configuration in `application.properties`: `spring.jpa.hibernate.ddl-auto=none`, `spring.datasource.driver-class-name=com.amazon.redshift.jdbc42.Driver`. No special mention of `dialect` there. – Rahul Raj Mar 18 '20 at 08:37
  • Can you change it then? – Šimon Kocúrek Mar 18 '20 at 08:45
  • So, I can keep the above mentioned config and add `org.hibernate.dialect.PostgreSQL82Dialect=true` into the properties file right? Adding `create-drop` will delete my data? – Rahul Raj Mar 18 '20 at 08:47
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/209831/discussion-between-rahul-raj-and-simon-kocurek). – Rahul Raj Mar 18 '20 at 09:01
  • @ŠimonKocúrek I tried all your suggestions, but I'm still getting the same error. Note that updating object values are working. Persisting a new object is what not working. – Rahul Raj Mar 18 '20 at 18:09

0 Answers0