title: Import/update existing rows in PostgreSQL
body: <p>I am working on a hobby app which will contain a large slew of basically-hardcoded data, as well as dynamic user data once I deploy it. I want the ability to update the hardcoded data locally (more <code>UPDATE</code>s than <code>INSERT</code>s), and then export these data to the server. In other words, I need to dump data to a file, and import it in such a way that new rows (which will be relatively few) are <code>INSERT</code>ed, and existing rows (as identified by the PK) are <code>UPDATE</code>d. Clearly, new rows can't be <code>INSERT</code>ed on the server (or the PKs would potentially clash and issue erroneous <code>UPDATE</code>s); this is an acceptable limitation. However, I cannot <code>DELETE</code> the rows to be <code>UPDATE</code>d, let alone drop the synchronised tables, because the user-accessible tables will have FK constraints on the "static" tables.</p>
  
  <p>Unfortunately, this seems to be very difficult to do. Google and Postgres mailing lists inform me that the "MySQL-like" feature <code>on_duplicate_key_update</code> "will be in a new version" (stale information; is it there?), along with a suggestion that "<code>pg_loader can do it</code>", with no indication of <em>how</em>.</p>
  
  <p>In a worst-case scenario, I suppose I can come up with a home-brewn solution (dump a data file, write a custom import script that checks for PK conflicts and issues <code>INSERT</code> or <code>UPDATE</code> statements appropriately), but that seems an awfully clumsy solution for a problem that others have surely encountered before me.</p>
  
  <p>Any thoughts?</p>