ON CONFLICT specify Action

[vivianshub]

I'm trying to specify an action when handling on conflict that doesn't merge-duplicates. For example, I have the following:

id (pk)	name	color	letter
123	Flora	Red	A
456	Fauna	Green	B
789	Merryweather	Blue	C

I have a post request as follows:

• {"id": "123", "name": "Tinkerbell", "color": "silver", "letter": "T"}
• if id "123" doesn't exist, then create the entry
• if id "123" does exist, then just update the "letter" (nothing else) So the result would be:

id (pk)	name	color	letter
123	Flora	Red	T
456	Fauna	Green	B
789	Merryweather	Blue	C

I tried looking through the documentation, but could only find an example for merge-duplicates, which would overwrite the other fields.

Is there a way to specify the action? THANKS!

Related

• https://github.com/PostgREST/postgrest/discussions/3444

[steve-chavez]

For now you would have to use a function for this.

To be able to do this directly we could extend the on_conflict syntax like:

POST /characters?on_conflict.action=update&on_conflict.columns=letter

[steve-chavez]

Or as suggested on https://github.com/PostgREST/postgrest/issues/2066, we could set on_conflict on the Prefer header instead:

Prefer: resolution=merge-duplicates;columns=col1,col2,col3

That seems simpler to implement. To make it clearer it could also be:

Prefer: on-conflict=update;columns=col1,col2,col3

This looks even clearer

Prefer: resolution=merge-duplicates;update-set=col1,col2,col3

[steve-chavez]

We could also try to combine this with https://github.com/PostgREST/postgrest/issues/465.

Prefer: resolution=merge-duplicates;col1=set.col1;col2=set.col2;col3=set.col3

Prefer: resolution=merge-duplicates;col1=set.excluded.col1;col2=set.excluded.col2;col3=set.excluded.col3

It's longer to type but it would support using operators and other things that set does.

[bnjmnt4n]

Wondering if there's any plan to support the new MERGE DML command: https://www.postgresql.org/docs/current/sql-merge.html?

[wolfgangwalther]

Wondering if there's any plan to support the new MERGE DML command: https://www.postgresql.org/docs/current/sql-merge.html?

Unfortunately MERGE does not support RETURNING, yet. And it won't support it in v16 either. That means we can't run it in a CTE. This makes it currently impossible to use it in our queries.

I do use MERGE in INSTEAD OF triggers on exposed views and some RPCs successfully, though.

[steve-chavez]

I do use MERGE in INSTEAD OF triggers on exposed views and some RPCs successfully, though.

@wolfgangwalther Hm, why do you use MERGE in those cases? From what I see here:

https://pganalyze.com/blog/5mins-postgres-15-merge-vs-insert-on-conflict#why-there-is-an-insert-on-conflict-command-in-postgres

There are also perf advantages when sticking to INSERT ON CONFLICT.

[wolfgangwalther]

@wolfgangwalther Hm, why do you use MERGE in those cases? From what I see here:

In the cases where I use it, I do have a DELETE branch as well.

https://pganalyze.com/blog/5mins-postgres-15-merge-vs-insert-on-conflict#why-there-is-an-insert-on-conflict-command-in-postgres

Non-issue in my use-cases.

There are also perf advantages when sticking to INSERT ON CONFLICT.

Do you have data backing that up?