Reputation: 457
I am trying to write an UPSERT code on my PostgreSQL DB.
Using
NamedParameterJdbcTemplate
I recieve the following error message:
Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [
INSERT INTO MY_TABLE
(ID,DATE_TIMESTAMP,ENVIRONMENT,REGION,ORGANIZATION,ARRIVAL_TIMESTAMP,LAST_UPDATED)
VALUES (?,?,?,?,?,?,?)
ON CONFLICT (ID, DATE_TIMESTAMP, ENVIRONMENT, REGION)
DO UPDATE
]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at end of input] with the root cause
org.postgresql.util.PSQLException: ERROR: syntax error at end of input
The query:
INSERT INTO MY_TABLE
(ID,DATE_TIMESTAMP,ENVIRONMENT,REGION,ORGANIZATION,ARRIVAL_TIMESTAMP,LAST_UPDATED)
VALUES (:ID,:DATE_TIMESTAMP,:ENVIRONMENT,:REGION,:ORGANIZATION,:ARRIVAL_TIMESTAMP,:LAST_UPDATED)
ON CONFLICT (ID, DATE_TIMESTAMP, ENVIRONMENT, REGION)
DO UPDATE
I've tried without the conflict condition, worked well.
I've tried adding a SET command, didn't work.
I've also tried using setters after the DO UPDATE SET statement I've also tried your solution, adding a SET statement for each column I want to update, using EXCLUDED.COLUMN_NAME
I received the following error message:
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification Call getNextException to see other errors in the batch.
Any suggestions on how to solve it?
Upvotes: 1
Views: 1919
Reputation: 457
I found a solution.
The reason for the error was the constraint I chose.
The CONFLICT condition must be a CONSTRAINT. For example, the primary keys.
See full documentation here: https://www.postgresql.org/docs/9.5/sql-insert.html
ON CONFLICT can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error. (See ON CONFLICT Clause below.)
Upvotes: 1