Postgres: insert multiple rows on conflict update not working

Question

I am trying to insert multiple rows into a table, and, in case of conflict, simply update them. What am I doing wrong?

insert into segments(id, departure_hour) 
values 
    (1153, 2), 
    (1156, 4), 
    (1154, 2) 
on conflict do update set 
    departure_hour = c.departure_hour 
from (values 
    (1153, 2), 
    (1156, 4), 
    (1154, 2)) 
as c(id, departure_hour)
    where c.id = segments.id

As requested, here is my table definition:

CREATE TABLE segments (
    id SERIAL PRIMARY KEY,
    route_id integer NOT NULL REFERENCES routes(id),
    origin_id integer NOT NULL REFERENCES stops(id),
    destination_id integer NOT NULL REFERENCES stops(id),
    price integer DEFAULT 0,
    departure_day integer NOT NULL,
    departure_hour integer NOT NULL,
    departure_minute integer NOT NULL,
    arrival_day integer NOT NULL,
    arrival_hour integer NOT NULL,
    arrival_minute integer NOT NULL,
    hidden boolean NOT NULL DEFAULT false,
    deleted boolean NOT NULL DEFAULT false,
    CONSTRAINT unique_origin_destination_per_route UNIQUE (route_id, origin_id, destination_id)
);

And here is my error:

ERROR:  syntax error at or near "from"
LINE 1: ...pdate set       departure_hour = c.departure_hour from (valu...

For starters, your insert only specifies one target column, but your are giving values for two columns. You might want to add the table definition for segments to your question. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 31, 2019 at 7:30
What is your error? I can see that your column declaration before values is missing a column (you specify only departure_hour, but you have 2 columns to update (1153 and 2, for example) — richyen
– richyen, Commented Oct 31, 2019 at 7:30
@TimBiegeleisen you are right, but apparently changing the number of target columns gives the same error, so it means that there is something wrong even before getting there. Added the target column anyway. — PptSbzzgt
– PptSbzzgt, Commented Oct 31, 2019 at 7:34

user330315 · Accepted Answer · 2019-10-31 07:51:24Z

21

You don't need the from part in order to be able to reference the values to update.

insert into segments(id, departure_hour) 
values 
    (1153, 2), 
    (1156, 4), 
    (1154, 2) 
on conflict do update set 
    departure_hour = excluded.departure_hour;

answered Oct 31, 2019 at 7:51

user330315

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

Collectives™ on Stack Overflow

Postgres: insert multiple rows on conflict update not working

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