Support INSERT ... ON CONFLICT DO NOTHING/UPDATE (also known as UPSERT) for PostgreSQL

[JustusLauten]

The feature exists already for MySQL databases and can be used with the :on_duplicate_key_update parameter. Upcoming versions of PostgreSQL will feature a similar functionality:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=168d5805e4c08bed7b95d351bf097cff7c07dd65

It would be cool to see this feature in activerecord-import, also for PostgreSQL databases.

[zdennis]

@JustusLauten ooh, that will be nice for Postgres to get. Any idea what version they are planning on including this in?

[johnnaegle]

Postgres 9.5: https://wiki.postgresql.org/wiki/UPSERT

[bkingon]

@zdennis any chance we could get this implemented?

[ptolts]

#209

^^ Quick and dirty. Maybe someone wants to take it on and make it merge worthy.

[jkowens]

Postgres 9.5 was released yesterday with the UPSERT feature. 👍

http://www.postgresql.org/about/news/1636/

[ptolts]

It's butter

[shlima]

+1

[jkowens]

The implementation for UPSERT with Postgres is going to have to be slightly different than that for MySQL.

Postgres requires that the constraint that may result in a duplicate key error be specified, where MySQL does not. It can referenced by either column name(s):

INSERT INTO countries (country, updated_at) VALUES ('France', now()),('Japan', now()) ON CONFLICT (country) DO UPDATE SET countries.updated_at = EXCLUDED.updated_at;

Or by constraint name:

INSERT INTO countries (country, updated_at) VALUES ('France', now()),('Japan', now()) ON CONFLICT ON CONSTRAINT countries_pkey DO UPDATE SET countries.updated_at = EXCLUDED.updated_at;

The documentation indicates column name should be preferred (see the "Tip" for on conflict clause). That being said, I was thinking it would be acceptable to just make column names the requirement for identifying the unique constraint.

Any thoughts about what this more explicit syntax should look like? I was thinking that even though it doesn't match the Postgres syntax, it might be nice to stick with the :on_duplicate_key_update parameter. This would be a hash with an attribute for the key and one for the columns that should be updated.

Country.import [country], on_duplicate_key_update: { key: :country, columns: [:updated_at] }

:key would have to take either a single value or array of column names and :columns could be either an array or hash similar to the existing MySQL functionality.

Thoughts on the naming? Other suggestions?

[rwilliams]