Insert Data

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

Basic Syntax

POST 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 Insert

# Insert a single record into the film table
curl -X POST 'https://{{host}}/v1/rdb/rest/film' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-d '{
  "title": "Inception",
  "release_year": 2010,
  "director": "Christopher Nolan",
  "duration": 148
}'

💡 Note: By default, the insert 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 inserted
• 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 201 Created
Header:
    content-length: 0
    content-range: */1
    preference-applied: return=minimal
Body: (Empty)

Insert and Return Data

# Insert data and return the complete inserted record
curl -X POST 'https://{{host}}/v1/rdb/rest/film?select=*' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-H 'Prefer: return=representation' \
-d '{
    "title": "The Shawshank Redemption",
    "release_year": 1994,
    "director": "Frank Darabont",
    "duration": 142
}'

Request

• Specifying the Header Prefer: return=representation in the request means the inserted data will be returned after the insert operation. This does not generate an additional query; all field data is returned by default.

Response

• If the table contains an auto-increment field (SERIAL), the returned value will include the auto-increment field value

Response Example

HTTP/1.1 201 Created
Header:
    content-length: 266
    content-range: */1
    preference-applied: return=representation
Body:
[
    {
        "_openid": "1977683311217119233",
        "director": "Frank Darabont",
        "duration": 142,
        "id": 2,
        "release_year": 1994,
        "title": "The Shawshank Redemption"
    }
]

Specify Return Fields

# Insert data and return specified fields
curl -X POST 'https://{{host}}/v1/rdb/rest/film?select=title,director' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-H 'Prefer: return=representation' \
-d '{
    "title": "The Godfather",
    "release_year": 1972,
    "director": "Francis Ford Coppola",
    "duration": 175
}'

Request

• Specifying the Header Prefer: return=representation in the request means the inserted data will be returned after the insert operation. This does not generate an additional query; all field data is returned by default.
• When /table?select=* is specified, all inserted field data is returned by default; when /table?select=id,title,director is specified, only the specified fields of the inserted data are returned.

Response Example

HTTP/1.1 201 Created
Header:
    content-length: 78
    content-range: */1
    preference-applied: return=representation
Body:
[
    {
        "id": 1,
        "director": "Francis Ford Coppola",
        "title": "The Godfather"
    }
]

Batch Insert

# Batch insert multiple records
curl -X POST 'https://{{host}}/v1/rdb/rest/film?select=id,title,director,duration' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-H 'Prefer: return=representation' \
-d '[
    {
        "title": "The Godfather: Part II",
        "release_year": 1974,
        "director": "Francis Ford Coppola",
        "duration": 202
    },
    {
        "title": "The Godfather: Part III",
        "release_year": 1990,
        "director": "Francis Ford Coppola",
        "duration": 194
    }
]'

Response

• content-range: */2 indicates that 2 records were created in this request
• Batch creation is executed within a single transaction; if any record fails, the entire batch is rolled back

Response Example

HTTP/1.1 201 Created
Header:
    content-length: 312
    content-range: */2
    preference-applied: return=representation
Body:
[
    {
        "director": "Francis Ford Coppola",
        "duration": 202,
        "id": 4,
        "title": "The Godfather: Part II"
    },
    {
        "director": "Francis Ford Coppola",
        "duration": 194,
        "id": 5,
        "title": "The Godfather: Part III"
    }
]

Upsert on Conflict (Merge Duplicates)

# Insert or update data
curl -X POST 'https://{{host}}/v1/rdb/rest/film?select=*' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-H 'Prefer: return=representation, resolution=merge-duplicates' \
-d '{
    "title": "The Godfather",
    "release_year": 1972,
    "director": "Francis Ford Coppola",
    "duration": 175
}'

Request

• return=representation means the written data will be returned after the write operation, used together with select; all field data is returned by default
• resolution=merge-duplicates means that when an insert conflict occurs, an update is performed instead. Conflict basis: when a primary key or unique index conflict occurs during creation, an update is performed (PostgreSQL uses the ON CONFLICT ... DO UPDATE syntax)

Note

• When using resolution=merge-duplicates, ensure the request body contains the primary key or unique index fields.
• PostgreSQL's ON CONFLICT mechanism is relatively safe, with low deadlock risk in high-concurrency scenarios.

Response Example

HTTP/1.1 201 Created

Header:
    content-length: 116
    content-range: */1
    content-type: application/json; charset=utf-8
    preference-applied: return=representation, resolution=merge-duplicates

Body:
[
    {
        "_openid": "1977683311217119233",
        "director": "Nolan",
        "duration": 150,
        "id": 1,
        "release_year": 2010,
        "title": "Inception"
    }
]

Ignore on Conflict

# Insert or ignore data
curl -X POST 'https://{{host}}/v1/rdb/rest/film?select=*' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-H 'Prefer: return=representation, resolution=ignore-duplicates' \
-d '{
    "title": "The Godfather",
    "release_year": 1972,
    "director": "Francis Ford Coppola",
    "duration": 175
}'

Request

• return=representation means the written data will be returned after the write operation, used together with select; all field data is returned by default
• resolution=ignore-duplicates means that when an insert conflict occurs, the conflicting record is ignored. Conflict basis: when a primary key or unique index conflict occurs (PostgreSQL uses the ON CONFLICT ... DO NOTHING syntax)
• content-range: */0 when an insert conflict occurs and is ignored, the number of affected rows is 0, i.e., content-range: */0

Note

• When using resolution=ignore-duplicates, PostgreSQL will ignore the conflicting record (neither insert nor update), so the number of affected rows is 0. Unlike SERIAL sequences, PostgreSQL sequences do not consume sequence values when conflicts are ignored.

Response

HTTP/1.1 201 Created

Header:
    content-length: 125
    content-range: */0
    content-type: application/json; charset=utf-8
    preference-applied: return=representation, resolution=ignore-duplicates

Body:
[
    {
        "_openid": "1977683311217119233",
        "director": "Sidney Lumet",
        "duration": 96,
        "id": 2,
        "release_year": 1957,
        "title": "12 Angry Men"
    }
]