csvsql: Add option to use ON CONFLICT DO NOTHING/UPDATE

[jschuur]

I'm using csvsql to insert into to a Postregress database, and I'd like it to ignore any errors generated from unique constraints (basically skip importing the same data twice).

In Postgres and that would be handled like this:

INSERT INTO table (num1, num2) VALUES (1,1) ON CONFLICT DO UPDATE

However, it doesn't look like any of the current csvsql options allow me to modify the insert statement like this, since --prefix would add it direcly after INSERT and --after-insert executes a seperate statement. Postgres doesn't support the 'OR IGNORE' format that MySQL does.

Seems like a --suffix option to append something to the end of the query would be useful here, or is this a fundamental issue, since agate-sql doesn't support this?

Are there any alternatives for ignoring unique constraint insert attempt errors? Even just a flag to ignore any error from an individual insert statement and keep trying would help here.

[jpmckinney]

Indeed. agate-sql uses SQLAlchemy, and --prefix causes it to call prefix_with(). Your feature would involve adding a CLI argument that causes it to call on_conflict_do_update or on_conflict_do_nothing.

If you write a PR for agate-sql, I can expose it in csvkit.

[dmannarino]

In case it's helpful, what we do is use csvsql to create the table (including unique constraints) and then psql to load the data from the CSVs. See here:
https://github.com/wri/gfw-data-api/blob/master/batch/scripts/create_tabular_schema.sh
https://github.com/wri/gfw-data-api/blob/master/batch/scripts/load_tabular_data.sh