Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

ON CONFLICT specify Action #2602

Open
vivianshub opened this issue Dec 29, 2022 · 7 comments
Open

ON CONFLICT specify Action #2602

vivianshub opened this issue Dec 29, 2022 · 7 comments
Labels
idea Needs of discussion to become an enhancement, not ready for implementation

Comments

@vivianshub
Copy link

vivianshub commented Dec 29, 2022

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

@steve-chavez
Copy link
Member

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 steve-chavez transferred this issue from PostgREST/postgrest-docs Jan 4, 2023
@steve-chavez steve-chavez added the idea Needs of discussion to become an enhancement, not ready for implementation label Jan 4, 2023
@steve-chavez
Copy link
Member

steve-chavez commented Jan 14, 2023

Or as suggested on #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
Copy link
Member

We could also try to combine this with #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
Copy link

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

@wolfgangwalther
Copy link
Member

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
Copy link
Member

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
Copy link
Member

@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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
idea Needs of discussion to become an enhancement, not ready for implementation
Development

No branches or pull requests

4 participants