Issues using INSERT INTO ON CONFLICT using WITH queries

Question

I'm changing queries from an Oracle Database to PostgreSQL, and in this query I am getting this error:

 ERROR: syntax error at or near "ON"

the query is:

WITH d as (
      SELECT ? AS summary, ? AS eventTime), ai as (select * from alarm)
ON (ai.fault_id=? AND ai.external_key=?)
INSERT INTO alarm AS ai(
     alarm_id,
     fault_id,
     external_key)
VALUES (nextval('seq_alrm_instnc_alrm_instnc_id'),?,?)
ON CONFLICT DO UPDATE SET
        (${buildUpdateAttributes(attributes)}
        ai.summary = d.summary,
        ai.system_last_update_time = default,
        ai.event_sequence_number =
          CASE
            WHEN (d.event_number > ai.event_number) THEN d.event_number
            ELSE ai.event_number
          END)

I am using JDBC to connect to the database and here is the call code

try (PreparedStatement ps = super.prepareStatement_(sql_query)) {
    PreparedStatementHelper psh = new PreparedStatementHelper(ps);
    psh.add(attribute1);
    ...
    ps.execute()

I have tried different aproaches and taken a long look at Postgres documentation and cannot find what is wrong and didn't find any answer to this specific situation

I don't understand what the CTE (`WITH d as ...`) is supposed to do there. Why do you provide the value for the summary column in the `values` clause for the INSERT? And the `eventTime` is not used at all in the subsequent statement. And the `select * from alarm` makes no sense whatsoever in that statement. And the alias `d` doesn't contain a column named `event_number` so the reference `d.event_number` is also invalid. And you can't put the whole SET part between parantheses — , Oct 25 '18 at 11:16
Try removing the ON part and put the conditions in a WHERE. See [reference](https://www.postgresql.org/docs/9.2/static/sql-insert.html) — Joop Eggen, Oct 25 '18 at 11:19

score 1 · Accepted Answer · answered Oct 25 '18 at 11:27

I don't really understand what you are trying to do there, you have mixed so many syntax elements in the wrong way that it's really hard to understand.

The two CTEs (WITH ...) seem completely irrelevant. If you just want to provide some values and do an update if a UNIQUE key is violated, the basic syntax is:

insert into the_table (col_1, col_2, col_3, col_4)
values (1,2,3,4)
on conflict (col_1,col_2) do update 
  set col_3 = excluded.col_3,
      col_4 = excluded.col_4;

The special keyword excluded is used to reference the column value supplied in the VALUES clause of the row that caused the unique constraint to be violated.

Your CTE provides a parameter eventTime which is then never used further down. Assuming that should be the event_number, then maybe you looking for something like this:

INSERT INTO alarm 
  -- specify all columns for which you provide a value 
  (alarm_id, fault_id, external_key,summary, event_sequence_number, event_number)
VALUES 
  -- provide a value for each column
  (nextval('seq_alrm_instnc_alrm_instnc_id'), ?, ?, ?, ?, ? )
  -- define the columns of the unique constraint you want to "catch"
ON CONFLICT (fault_id, external_key) 
DO UPDATE SET
   -- "excluded.summary" refers to the corresponding value from the VALUES clause
   summary = excluded.summary, 
   system_last_update_time = default,
   -- excluded.event_number refers to the corresponding value from the VALUES clause
   event_sequence_number = CASE
                             WHEN excluded.event_number > alarm.event_number THEN excluded.event_number
                             ELSE alarm.event_number
                           END

hey man, thanks. I do not really know the basic elements of PostGres or even Oracle so I was trying to use what I had in the Oracle Query to try and migrate. This is also not the real query, because that one had atleast more 20 fields so I tried to simplify. I will try what you said — Francisco Martins Ferreira, Oct 25 '18 at 12:53

Issues using INSERT INTO ON CONFLICT using WITH queries

1 Answers1