Update

Update operations are performed using PUT, PATCH, and POST HTTP verbs. We use multiple verbs to model the situations when one wants to update a single row, multiple rows matching certain condition or perform an upsert (insert multiple rows and update on conflict).

Update one row

Updating one row is done by performing a PATCH request and providing the filtering condition on the primary key that uniquely identifies the row

curl -X PATCH -d '{"completed":true}' \
http://localhost:3000/rest/todos?id=eq.10

Updating multiple rows

Updating multiple rows is done by performing a PATCH request and providing the filtering condition

curl -X PATCH -d '{"completed":true}' \
http://localhost:3000/rest/todos?id=gt.10

Receive the updated row columns

When updating rows, it's often desired to receive in the response the IDs (or some other columns) of the rows that were updated by the request

To achieve this result one must include the Prefer: return=representation header with the request. In almost all cases, this header is used in combination with the select query parameter to avoid fetching all the columns of the newly updated row.

curl -X PATCH \
-H 'Prefer: return=representation' \
-d '{"completed":true}' \
"http://localhost:3000/rest/todos?id=gt.10&select=id,updated_at"

When updating a single row, in order to receive back a json object representing the row (instead of a json array with a single item) include the Accept: application/vnd.pgrst.object+json header.

curl -X PATCH \
-H 'Prefer: return=representation' \
-H 'Accept: application/vnd.pgrst.object+json' \
-d '{"completed":true}' \
"http://localhost:3000/rest/todos?id=eq.10&select=id,updated_at"

Upsert

When the table has a natural primary key, it's possible to execute UPSERT requests that will update (or ignore) them existing rows and insert the missing ones.

Upsert is done my issuing a POST request along with the header Prefer: resolution=merge-duplicates or Prefer: resolution=ignore-duplicates

curl -X POST \
-H 'Prefer: resolution=merge-duplicates' \
-d '[{"id": 1, "todo":"todo one"}, {"id": 2, "todo":"todo two"}]' \
http://localhost:3000/rest/todos