I've got this table (generated by Django):
CREATE TABLE feeds_person (
id serial PRIMARY KEY,
created timestamp with time zone NOT NULL,
modified timestamp with time zone NOT NULL,
name character varying(4000) NOT NULL,
url character varying(1000) NOT NULL,
email character varying(254) NOT NULL,
CONSTRAINT feeds_person_name_ad8c7469_uniq UNIQUE (name, url, email)
);
I'm trying to bulk insert a lot of data using INSERT with an ON CONFLICT clause.
The wrinkle is that I need to get the id back for all of the rows, whether they're already existing or not.
In other cases, I would do something like:
INSERT INTO feeds_person (created, modified, name, url, email)
VALUES blah blah blah
ON CONFLICT (name, url, email) DO UPDATE SET url = feeds_person.url
RETURNING id
Doing the UPDATE causes the statement to return the id of that row. Except, it doesn't work with this table. I think it doesn't work because I've got multiple fields unique together whereas in other instances I've used this method I've had just one unique field.
I get this error when trying to run the SQL through Django's cursor:
django.db.utils.ProgrammingError: ON CONFLICT DO UPDATE command cannot affect row a second time HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
How do I do the bulk insert with this table and get back the inserted and existing ids?