Postgrest: Upsert with "Prefer: resolution=merge-duplicates" on a specified column

[kasugaicrow]

According to the PostgREST's documentation, if I want to make an upsert on a table I need to specify the "Prefer: resolution=merge-duplicates" string on the header of the request. The documentation also says

UPSERT operates based on the primary key columns

But what if I want to made an upsert on a table with a specified column rather than the primary key. It is possible with the latest version of PostgREST?

For instance, if I have a table like the following, the upsert will work with the primary key id_user but in this case the field type is UUID so the values ​​will always be different and the upsert will "fail". I would like to upsert using unique_code column that it's unique anyways.

CREATE TABLE IF NOT EXISTS user (
    id_user UUID PRIMARY KEY,
        unique_code TEXT,       
    first_name TEXT
    last_name TEXT,
    age TEXT,
    address TEXT,
);

[steve-chavez]

I see your point. Right now it's not possible to specify a column different than the PK.

The UPSERT feature is possible thanks to PostgreSQL ON CONFLICT clause.
Maybe we could offer an additional query param that makes explicit use of this clause.

That way you could specify your UNIQUE column(must have a constraint). It could be like:

POST /user?on_conflict=unique_code

What do you think?

[kasugaicrow]

Ok great, that's exactly what I was looking for. A friend of mine could implement it, what do you think @NSilv?

[steve-chavez]

This feature would involve changes in many pgrst modules, but I think it should be straightforward.

on_conflict=cola,colb just needs a list of columns and pg will only admit it if a constraint is defined on those columns, so validation on our part would not be needed. This new query param should complement merge-duplicates/ignore-duplicates as a way to override the on conflict target.

A rough guideline for implementing this would be:

1. Add the type for the on conflict values(actually this is already available)
   https://github.com/PostgREST/postgrest/blob/ea970554491557d79408ee2193d8a5f08d2984ea/src/PostgREST/Types.hs#L378-L385

2. Define the querystring param in(should only be allowed for POST, otherwise ignored)
   https://github.com/PostgREST/postgrest/blob/ea970554491557d79408ee2193d8a5f08d2984ea/src/PostgREST/ApiRequest.hs#L76

3. Define the Parser for this query param in(this is an example for how it's done for ?select=..)
   https://github.com/PostgREST/postgrest/blob/ea970554491557d79408ee2193d8a5f08d2984ea/src/PostgREST/Parsers.hs#L29-L31

4. Build the type value on
   https://github.com/PostgREST/postgrest/blob/ea970554491557d79408ee2193d8a5f08d2984ea/src/PostgREST/DbRequestBuilder.hs#L324

5. Finally translate that type to SQL in
   https://github.com/PostgREST/postgrest/blob/ea970554491557d79408ee2193d8a5f08d2984ea/src/PostgREST/QueryBuilder.hs#L306

The logic for doing on conflict is already there, it mostly involves a refactor I think.

[ErwinJanssen]

I'm also very interested in this feature! What is the current status?

[NSilv]

I haven't had much time to work on it sadly because most of my time has been taken up by work and life, but I think I'll pick up the pace in the next few weeks, I'm hoping to get it working during this month

[ykst]

Hi, I'm also interested in this feature and made a patch based on the discussion above. May I send PR?

[steve-chavez]

@ykst Sure! Open a PR so I can give it a review. Your branch looks really good.

[steve-chavez]

Implemented in #1432!

[ykst]

Thank you for your quick response!