Advanced Queries

prestd allows you to do some advanced queries, but with some limitations in order to make the URL and parameters as clean as possible.

Filter range

It is common to query a database in a range of time, e.g:

I need all records that were created from 1997-11-03-03 to 1997-12-05.

created_at field is a timestamp field, so you can use the $gte and $lte operators to filter by date range.

1SELECT * FROM {SCHEMA}.{TABLE} WHERE created_at >= '1997-11-03-03' AND created_at <= '1997-12-05'

How to do it in prestd?

1GET /{DATABASE}/{SCHEMA}/{TABLE}?created_at='$gte.1997-11-03'&created_at='$lte.1997-12-0'

There are other types of operators, see them all here.

JOIN

HTTP verb GET, allows you to join tables, with 1 level of depth - unfortunately the syntax is not so friendly so we limited it to 1 level only.

/{DATABASE}/{SCHEMA}/{TABLE}?_join={TYPE}:{TABLE JOIN}:{TABLE.FIELD}:{OPERATOR}:{TABLE JOIN.FIELD}

Parameters:

  1. Type:
    • inner
    • left
    • right
    • outer
  2. Table used in the join
  3. Table.field - table name dot field
  4. Operator:
    • $eq
    • $lt
    • $gt
    • $lte
    • $gte
  5. Table2.field - table name dot field

Using query string to JOIN tables, example:

/{DATABASE}/{SCHEMA}/friends?_join=inner:users:friends.userid:$eq:users.id
If you need multiple joins, we recommend using the queues feature (sql script execution).

JSONb support

PostgreSQL offers type for storing jsonb data. To implement efficient query mechanisms for these data types.

?FIELD->>JSONFIELD:jsonb=VALUE

Example of how to do insertion via cURL

Fields:

  • id: INT
  • data: JSONB
1
2```sh
3curl -i -X POST 'http://127.0.0.1:3000/prest/public/t_mydata' \
4  -H 'Content-Type: application/json' \
5  -d '{"id": 80, "data": "{\"a\": 123}"}'

reference

Full Text Search (with tsquery)

Full Text Searching (or just text search) provides the capability to identify natural-language documents that satisfy a query, and optionally to sort them by relevance to the query. The most common type of search is to find all documents containing given query terms and return them in order of their similarity to the query. Notions of query and similarity are very flexible and depend on the specific application. The simplest search considers query as a set of words and similarity as the frequency of query words in the document.

Is native feature of PostgreSQL since version 8.3, read more here.

A tsquery value stores lexemes that are to be searched for, and combines them honoring the Boolean operators & (AND), | (OR), and ! (NOT). Parentheses can be used to enforce grouping of the operators. SELECT 'fat & rat'::tsquery;

?FIELD:tsquery=VALUE

Set language

You can specify the language you want to tokenize in, for example: portuguese

FIELD$LANGUAGE:tsquery=VALUE

Language list:

  • simple
  • arabic
  • danish
  • dutch
  • english
  • finnish
  • french
  • german
  • hungarian
  • indonesian
  • irish
  • italian
  • lithuanian
  • nepali
  • norwegian
  • portuguese
  • romanian
  • russian
  • spanish
  • swedish
  • tamil
  • turkish

To see all the languages available in your PostgreSQL run this query:

1SELECT cfgname FROM pg_ts_config;

Batch Insert

HTTP verb POST, you can insert many rows at once using batch endpoint /batch/....

/batch/DATABASE/SCHEMA/TABLE

JSON DATA:

[
    {"FIELD1": "string value", "FIELD2": 1234567890},
    {"FIELD1": "other string value", "FIELD2":1234567891},
]

The default insert method is using multiple tuple values like insert into table values ("value", 123), ("other", 456). Returns inserted rows.

You can change this behavior using the header Prest-Batch-Method with value copy. It's useful for large insertions, but the return is empty.