Update Data

Update data in the PostgreSQL database through the HTTP RESTful API. For specific API details, please refer to HTTP API/PostgreSQL Database.

Basic Syntax

PATCH https://your-envId.api.tcloudbasegateway.com/v1/rdb/rest/:table
Authorization: Bearer <access_token>
Content-Type: application/json

💡 Tip: For access_token, please refer to Get AccessToken

Basic Update

# Update a single record
curl -X PATCH 'https://{{host}}/v1/rdb/rest/film?id=eq.1' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-d '{
  "duration": 202
}'

💡 Note: By default, the update operation does not return data, only the status code and the number of affected rows

Response

• content-range: */1 indicates the number of affected rows; here it means 1 record was updated
• preference-applied: return=minimal indicates the return=minimal return strategy was applied during the write operation (default behavior for write operations, i.e., no response body is produced)

Response Example

HTTP/1.1 204 No Content
Header:
    content-range: */1
    preference-applied: return=minimal
Body: (Empty)

Update and Return Data

# Update data and return the complete updated records
curl -X PATCH 'https://{{host}}/v1/rdb/rest/film?select=*&id=in.(3,4,5)' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-H 'Prefer: return=representation' \
-d '{
    "director": "Francis Ford Coppola"
}'

Request

• Specifying the Header Prefer: return=representation in the request means the updated data will be returned after the update operation. Internally, this uses the UPDATE ... RETURNING syntax to complete the update and return results in a single query

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 387
    content-range: */3
    content-type: application/json; charset=utf-8
    preference-applied: return=representation
Body:
[
    {
        "director": "Francis Ford Coppola",
        "duration": 175,
        "id": 3,
        "release_year": 1972,
        "title": "The Godfather"
    },
    {
        "director": "Francis Ford Coppola",
        "duration": 202,
        "id": 4,
        "release_year": 1974,
        "title": "The Godfather: Part II"
    },
    {
        "director": "Francis Ford Coppola",
        "duration": 194,
        "id": 5,
        "release_year": 1990,
        "title": "The Godfather: Part III"
    }
]

Unconditional Update Restriction

# Unconditional updates will be rejected
curl -X PATCH 'https://{{host}}/v1/rdb/rest/film' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-d '{
    "duration": 202
}'

Response

• Unconditional updates are unsafe operations. When such a request is made, the system will protect the data by rejecting the request.

Response Example

HTTP/1.1 400 Bad Request
Header:
    content-length: 113
    content-type: application/json; charset=utf-8
Body:
{
    "code": "BadApiRequest",
    "details": "",
    "hint": "",
    "message": "UPDATE requires a WHERE clause"
}

Complex Conditional Update

# Update with complex conditions + sorting + pagination
curl -X PATCH 'https://{{host}}/v1/rdb/rest/film?select=*&id=in.(3,4,5)&and=(duration.gte.150,duration.lt.200)&or=(director.eq.Christopher Nolan,director.eq.Frank Darabont)&limit=1&order=id.desc' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-H 'Prefer: return=representation' \
-d '{
    "director": "Francis Ford Coppola"
}'

Request

• id=in.(3,4,5): Update records where id is 3, 4, or 5
• and=(duration.gte.150,duration.lt.200): And duration is greater than or equal to 150 minutes and less than 200 minutes
• or=(director.eq.Christopher Nolan,director.eq.Frank Darabont): Or director is Christopher Nolan or Frank Darabont
• limit=1: Update at most 1 record
• order=id.desc: Sort by id in descending order before updating

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 142
    content-range: */1
    content-type: application/json; charset=utf-8
    preference-applied: return=representation
Body:
[
    {
        "_openid": "1977683311217119233",
        "director": "Francis Ford Coppola",
        "duration": 194,
        "id": 5,
        "release_year": 1990,
        "title": "The Godfather: Part III"
    }
]

Special Scenarios

When performing update operations with data return, the system uses the primary key to retrieve the updated data. However, be aware of the following scenarios:

Single-Field Primary Key Table

If a table's primary key is a single field, when the primary key is used as both the update condition and the update value, the update will succeed, but the query return may not be as expected:

# Change the primary key of the movie with id=1 to 2
curl -X PATCH 'https://{{host}}/v1/rdb/rest/film?select=title,release_year,duration&id=eq.1' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-H 'Prefer: return=representation' \
-d '{
    "id": 2
}'

In this case, the data can be updated normally, and the Content-Range header will indicate 1 affected row. However, since id serves as both the primary key and the update value, the query result will be empty.

Composite Primary Key Table

Similar to the single-field primary key case, if any field of the composite primary key is used as both a condition and an update value, the update will succeed, but the query return may not be as expected:

# Table user_activities has a composite primary key of (activity_date, activity_type)
# Update the activity_type from login to logout in the user_activities table
curl -X PATCH 'https://{{host}}/v1/rdb/rest/user_activities?activity_type=eq.login' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-H 'Prefer: return=representation' \
-d '{
    "activity_type": "logout"
}'

In this case, the data can be updated normally, and the Content-Range header will indicate 1 affected row. However, since activity_type is part of the composite primary key and also the update value, the query result will be empty.

Table Without Primary Key

If a table has no primary key, you cannot specify the Prefer: return=representation request header during updates. Updating and returning data for tables without primary keys is not supported, and you will receive the following error:

table <table_name> has no primary key, cannot use update-with-return, suggest remove query

After removing Prefer: return=representation, tables without primary keys can be updated normally.