Query Data

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

💡 Tip: The PostgreSQL database HTTP API is based on the PostgREST protocol, and the query syntax is fully compatible with PostgREST.

Basic Syntax

GET 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 Query

# Query all data from the film table
curl -X GET 'https://{{host}}/v1/rdb/rest/film' \
-H 'Authorization: Bearer <access_token>'

💡 Note: The returned data includes all fields, defaulting to /table?select=*

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 1037
    content-type: application/json; charset=utf-8
Body:
[
    {
        "_openid": "1977683311217119233",
        "director": "陈凯歌",
        "duration": 171,
        "id": 1,
        "release_year": 1993,
        "title": "霸王别姬"
    },
    // ... 15 records in total
]

Specify Return Fields

# Specify which fields to return from the film table
curl -X GET 'https://{{host}}/v1/rdb/rest/film?select=title,director,release_year' \
-H 'Authorization: Bearer <access_token>'

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 1080
    content-type: application/json; charset=utf-8
Body:
[
    {
        "director": "陈凯歌",
        "release_year": 1993,
        "title": "霸王别姬"
    },
    {
        "director": "姜文",
        "release_year": 2010,
        "title": "让子弹飞"
    }
    // ... 15 records in total
]

Conditional Query

• For specific operators, refer to: Operators
• For specific logical operators, refer to: Logical Operators

# Query movies directed by Christopher Nolan
curl -X GET 'https://{{host}}/v1/rdb/rest/film?director=eq.Christopher Nolan' \
-H 'Authorization: Bearer <access_token>'

# Query movies released after 2000 with duration no more than 120 minutes
curl -X GET 'https://{{host}}/v1/rdb/rest/film?release_year=gte.2000&duration=lt.120' \
-H 'Authorization: Bearer <access_token>'

# Query movies released after 2000 directed by Christopher Nolan or 姜文
curl -X GET 'https://{{host}}/v1/rdb/rest/film?or=(director.eq.Christopher Nolan, director.eq.姜文)' \
-H 'Authorization: Bearer <access_token>'

Query movies directed by Christopher Nolan

HTTP/1.1 200 OK
Header:
    content-length: 260
    content-type: application/json; charset=utf-8
Body:
[
    {
        "_openid": "1977683311217119233",
        "director": "Christopher Nolan",
        "duration": 169,
        "id": 13,
        "release_year": 2014,
        "title": "Interstellar"
    },
    {
        "_openid": "1977683311217119233",
        "director": "Christopher Nolan",
        "duration": 148,
        "id": 14,
        "release_year": 2010,
        "title": "Inception"
    }
]

Sorting and Pagination

# Query movies before 2000, show top 3 by longest duration
curl -X GET 'https://{{host}}/v1/rdb/rest/film?order=duration.desc&limit=3' \
-H 'Authorization: Bearer <access_token>'

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 266
    content-type: application/json; charset=utf-8
Body:
[
    {
        "director": "James Cameron",
        "duration": 194,
        "release_year": 1997,
        "title": "Titanic"
    },
    {
        "director": "陈凯歌",
        "duration": 171,
        "release_year": 1993,
        "title": "霸王别姬"
    },
    {
        "director": "Frank Darabont",
        "duration": 142,
        "release_year": 1994,
        "title": "The Shawshank Redemption"
    }
]

Count Query

# Query movies after 2000 with total count
curl -X GET 'https://{{host}}/v1/rdb/rest/film?select=*&release_year=gte.2000' \
-H 'Authorization: Bearer <access_token>' \
-H 'Prefer: count=exact'

Request

• When the Header Prefer: count=exact is specified, the total number of records matching the conditions is returned

Response

• content-range: 0-9/10 indicates the data range returned by this query, with a total of 10 records. 0 indicates no offset, 9 indicates the index of the last record, and 10 indicates the total count

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 853
    content-range: 0-9/10
    content-type: application/json; charset=utf-8
Body:
[
    {
        "director": "姜文",
        "duration": 132,
        "release_year": 2010,
        "title": "让子弹飞"
    },
    {
        "director": "郭帆",
        "duration": 125,
        "release_year": 2019,
        "title": "流浪地球"
    },
    {
        "director": "吴京",
        "duration": 123,
        "release_year": 2017,
        "title": "战狼2"
    }

    // ... 10 records in total
]

Force Object Format Response

By default, data is returned in array format. If you need to force an object format response, specify the Header Accept: application/vnd.pgrst.object+json.

⚠️ Note: This method should only be used when there is exactly one record to return. You can add limit=1 to restrict the number of returned records.

curl -i -X GET 'http://{{host}}/v1/rdb/rest/v1/film?select=*&id=eq.1&limit=1' \
-H 'Authorization: Bearer <access_token>' \
-H 'Accept: application/vnd.pgrst.object+json'

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 121
    content-type: application/vnd.pgrst.object+json
Body:
{
    "director": "陈凯歌",
    "duration": 171,
    "id": 1,
    "release_year": 1993,
    "title": "霸王别姬",
    "_openid": "1977683311217119233"
}

Literal Queries

PostgreSQL allows creating tables and fields with special characters such as spaces. In this case, the URL request needs literal handling (using double quotes) to avoid conflicts with special characters and keywords.

Assume the following table structure:

• Table name: my_table
• Field names: full_name, email_address

curl -i -X GET 'http://{{host}}/v1/rdb/rest/my_table?select=full_name,email_address' \
-H 'Content-Type: application/json' \
-H 'Authorization: Bearer <access_token>'

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 61
    content-type: application/json; charset=utf-8
Body:
[
    {
        "email_address": "john@example.com",
        "full_name": "John Doe"
    }
]

💡 Tip: If table or field names contain special characters like spaces (e.g. my table, full name), wrap them in double quotes, e.g. ?select="full name","email address"

Operators

Comparison Operators

Operator	Description	Example
eq	Equal to	?id=eq.1
neq	Not equal to	?id=neq.1
gt	Greater than	?age=gt.18
gte	Greater than or equal to	?age=gte.18
lt	Less than	?age=lt.35
lte	Less than or equal to	?age=lte.35
like	Pattern matching	?name=like.%value%
ilike	Pattern matching (case-insensitive)	?name=ilike.%value%
match	Regex matching (POSIX)	?name=match.^A
imatch	Regex matching (POSIX, case-insensitive)	?name=imatch.^a
in	In list	?id=in.(1,2,3)
is	Is null / boolean	?age=is.null

Array & JSON Operators

Operator	Description	Example
cs	Contains	?tags=cs.{sci-fi,action}
cd	Contained by	?tags=cd.{sci-fi,action,drama}
ov	Overlaps	?tags=ov.{action,comedy}

Range Operators

Operator	Description	Example
sl	Strictly left of	?range=sl.(0,5)
sr	Strictly right of	?range=sr.(5,10)
nxr	Does not extend right of	?range=nxr.(0,10)
nxl	Does not extend left of	?range=nxl.(0,10)
adj	Adjacent to	?range=adj.(0,5)

Full-Text Search Operators

Operator	Description	Example
fts	Full-text search (to_tsquery)	?description=fts.PostgreSQL
plfts	Full-text search (plainto_tsquery)	?description=plfts.PostgreSQL database
phfts	Full-text search (phraseto_tsquery)	?description=phfts.PostgreSQL database
wfts	Full-text search (websearch_to_tsquery)	?description=wfts.PostgreSQL or database

💡 Tip: Full-text search operators support specifying a language configuration in the format fts(language).value, e.g. ?description=fts(english).PostgreSQL

Supported Logical Operators

Operator	Description	PostgreSQL Equivalent	Example
and	Logical AND	AND	/table?id=eq.1&age=gt.18 and /table?select=*&and=(age.gt.18,age.lt.35)
or	Logical OR	OR	/table?select=*&or=(id.eq.1,name.like.%value%)
not	Logical NOT	NOT	/table?select=*&age=not.eq.18&gender=not.is.null&isTrue=not.is.true