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
to1997-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:
- Type:
inner
left
right
outer
- Table used in the join
- Table.field - table name dot field
- Operator:
$eq
$lt
$gt
$lte
$gte
- Table2.field - table name dot field
Using query string to JOIN tables, example:
/{DATABASE}/{SCHEMA}/friends?_join=inner:users:friends.userid:$eq:users.id
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}"}'
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.