INSERT -> return row if conflict

[JFGHT]

Summary

Introduce a mechanism in PostgREST to support a common “get or create” workflow: when an insert fails due to a unique constraint violation, automatically return the existing conflicting row instead of an error, without requiring a second request.

---

Problem

Currently, when inserting into a table with a unique constraint, PostgREST returns an error if the row already exists:

POST /articles
{ "url": "...", "title": "...", "content": "...", "unique_hash": "abc123" }

If the unique_hash column has a UNIQUE constraint and the row already exists, the response is:

{
  "code": "23505",
  "message": "duplicate key value violates unique constraint ..."
}

At this point:

• The client receives no data about the existing row.
• To obtain it, the client must issue a second query (GET /articles?unique_hash=eq.abc123).

This adds complexity and latency to a very common workflow: “insert or return existing”.

---

Proposed Solution

Allow an insert request to specify a conflict-handling mode that falls back to selecting the existing row if a unique violation occurs.

Option 1 – Conflict-fallback return

POST /articles?on_conflict=unique_hash&return=existing
{ "url": "...", "title": "...", "content": "...", "unique_hash": "abc123" }

• If a new row is inserted → return it.
• If a conflict occurs → return the existing row identified by unique_hash.

Option 2 – Extend upsert

PostgREST already supports upsert with on_conflict. Currently, it either updates or ignores the row. Extend this with an option to return the existing row without modification:

POST /articles?on_conflict=unique_hash&return=existing_only
{ "url": "...", "title": "...", "content": "...", "unique_hash": "abc123" }

• If inserted → return new row.
• If conflict → return existing row, untouched.

---

Rationale

• Avoids extra round-trips: Today, clients must retry with GET after a 23505 error.
• Common pattern: The “get or create” pattern is widely used in web applications.
• Keeps atomicity: No race conditions between separate insert/select calls.
• Aligns with PostgreSQL semantics: This is essentially INSERT ... ON CONFLICT DO NOTHING RETURNING *, extended to return the conflicting row rather than nothing.

[wolfgangwalther]

This is essentially an upstream feature missing here. If https://www.postgresql.org/message-id/flat/CAEZATCV_Urr7FO_xQ%2ByHyhssbJYwns%2Bpqg0j35eDajN2%2BzFBGw%40mail.gmail.com#dd8fd9fb8f9ec84d7e5d7ca66fe09e7b was implemented, this would be trivial to do.

[JFGHT]

This is essentially an upstream feature missing here. If https://www.postgresql.org/message-id/flat/CAEZATCV_Urr7FO_xQ%2ByHyhssbJYwns%2Bpqg0j35eDajN2%2BzFBGw%40mail.gmail.com#dd8fd9fb8f9ec84d7e5d7ca66fe09e7b was implemented, this would be trivial to do.

Do you want me to close the issue then?

[wolfgangwalther]

No, not really, I think. We'd still need to do something once upstream supports it. So tracking is still a good idea.