Skip to content

Update

Info

For simplicity, all the examples on this page, assume the HTTP client is already initialized and the requests are unauthenticated.

For an example of how to initialize the HTTP clients and make authenticated requests see authentication section

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:8080/rest/todos?id=eq.10
await fetch(endpoint+'/rest/todos?id=eq.10', {
    method: 'PATCH',
    body: JSON.stringify({"completed":true})
});
await client.patch('/rest/todos?id=eq.10', {"completed":true});
await client.query({ 
    query: gql`
        mutation {
            update {
                todo(id: 10, input: {completed: true}) {
                    id
                }
            }
        }
    `
});

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:8080/rest/todos?id=gt.10
await fetch(endpoint+'/rest/todos?id=gt.10', {
    method: 'PATCH',
    body: JSON.stringify({"completed":true})
});
await client.patch('/rest/todos?id=gt.10', {"completed":true});
await client.query({ 
    query: gql`
        mutation {
            update {
                todos(where: {id: {gt: 10}}, input: {completed: true}) {
                    id
                }
            }
        }
    `
});

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:8080/rest/todos?id=gt.10&select=id,updated_at"
await fetch(endpoint+'/rest/todos?id=gt.10&select=id,updated_at', {
    method: 'PATCH',
    headers: new Headers({'Prefer': 'return=representation'})},
    body: JSON.stringify({"completed":true})
});
const options = {headers: {'Prefer': 'return=representation'}}
await client.patch('/rest/todos?id=gt.10&select=id,updated_at', {"completed":true}, options);
await client.query({ 
    query: gql`
        mutation {
            update {
                todos(where: {id: {gt: 10}}, input: {completed: true}) {
                    id
                }
            }
        }
    `
});

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:8080/rest/todos?id=eq.10&select=id,updated_at"
await fetch(endpoint+'/rest/todos?id=eq.10&select=id,updated_at', {
    method: 'PATCH',
    headers: new Headers({'Prefer': 'return=representation', 'Accept': 'application/vnd.pgrst.object+json'})},
    body: JSON.stringify({"completed":true})
});
const options = {headers: {'Prefer': 'return=representation', 'Accept': 'application/vnd.pgrst.object+json'}}
await client.patch('/rest/todos?id=eq.10&select=id,updated_at', {"completed":true}, options);
await client.query({ 
    query: gql`
        mutation {
            update {
                todo(id: 10, input: {completed: true}) {
                    id
                }
            }
        }
    `
});

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": 1, "todo":"todo two"}]' \
http://localhost:8080/rest/todos
await fetch(endpoint+'/rest/todos', {
    method: 'POST',
    headers: new Headers({'Prefer': 'resolution=merge-duplicates'})},
    body: JSON.stringify([{"id": 1, "todo":"todo one"}, {"id": 1, "todo":"todo two"}])
});
const options = {headers: {'Prefer': 'resolution=merge-duplicates'}}
await client.post('/rest/todos', [{"id": 1, "todo":"todo one"}, {"id": 1, "todo":"todo two"}], options);

Note

It's also possible to use upsert if the primary key is surrogate (example: “id serial primary key”). For more details read this issue.