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

Question

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?

Please post the full INSERT statement. The Do Update must be followed by the appropriate set statement(s). That is missing from post. Does it exist in actual statement. — Belayer
– Belayer, Commented Feb 26, 2020 at 15:19

JustDeveloper · Accepted Answer · 2020-02-26 15:51:39Z

1

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.)

answered Feb 26, 2020 at 15:51

JustDeveloper

4575 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related