CODEWITHSUNDEEP
javapostgresqlupsert
EpsilonTal
EpsilonTal

Reputation: 457

PostgreSQL “syntax error at end of input” after INSERT INTO with ON CONFLICT DO UPDATE condition

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

Answers (1)

EpsilonTal
EpsilonTal

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

Related Questions