UPSERT into GridSQL?

We have a process that loads into our current MySQL DB that performs a:
ON DUPLICATE KEY (perform UPDATE)

The only problem is that the update doesn't just overwrite the value for
the key it actually takes the current value and adds to it if the key already
exists.

Is there a good way to do this with GridSQL?

Hi David,

I am sorry, but GridSQL and PostgreSQL do not have this capability.

You would have to do something where you first check for its existence, then either INSERT or UPDATE. Or, try and INSERT, and if it fails, perform an UPDATE.

Actually, I recommend avoiding INSERT anyway- it is much faster to populate data using COPY or gs-loader.

I have an idea- gs-loader.sh typically loads large amounts of data at a time, for efficiency. You can tell it to keep retrying in smaller and smaller chunks if something fails. It also will save rejected lines in a separate file. So, you could try and load a big delimited file, while specifying a relatively small chunk/commit size, and then once finished, have a script go through all of the lines and generate UPDATE statements in a sql file and run it via psql. You could even batch these up for better performance.

Thinking about this a bit more, that would be a nice little utility if we extended gs-loader to optionally do something like that based on the target table's meta data information, but we are busy at the moment. Perhaps someone in your organization would be willing to take that on...

Regards,

Mason