Reading and writing data
Query syntax, operators, partial text search in Korean.
Reading and writing data
The syntax is the same as PostgREST. Existing client SDKs work as they are.
GET /rest/v1/<table>?select=id,body&order=created_at.desc&limit=20
GET /rest/v1/<table>?body=ilike.*Seoul*
POST /rest/v1/<table> body: {"body":"hello"} or [{...},{...}]
PATCH /rest/v1/<table>?id=eq.<value>
DELETE /rest/v1/<table>?id=eq.<value>Operators
eq neq gt gte lt lte like ilike match imatch cs cd ov sl sr nxr nxl adj isdistinct is in fts plfts phfts wfts
Put not. in front to negate one (status=not.eq.done). Conditions can be grouped with or=(a.eq.1,b.gt.2) and and=(…),
and in like and ilike a * means the same as %. In select you can use aliases (label:title),
casts (id::text) and tables joined by a foreign key (*,author(name)).
Call functions with POST /rest/v1/rpc/<function>. Conditions on embedded tables and many-to-many relations are not here yet.
If you need something that is missing, create a view with run_sql and read the view.
An unknown operator is refused by name. It is never silently ignored, because a filter that is not applied is an information leak under access rules.
Partial text search in Korean
pg_trgm is enabled. Korean attaches particles to words, so word-based search works poorly;
compare three characters at a time instead.
create index if not exists notes_body_trgm on notes using gin (body extensions.gin_trgm_ops);
select * from notes where body % 'Seoul';For other extensions, such as embedding search (pgvector) and maps (PostGIS), see blackwall://extensions.
CSV out and in
export_table_csv returns a table's rows as CSV (up to 1MB; past that it is cut at a whole row and truncated is true). Choose the range and the order with filters and sort, and fetch a large table in several ranges.
import_table_csv puts CSV into a table (up to 2MB and 10,000 rows). Columns go into the columns named on the first line, and if any row cannot go in, nothing goes in and the line numbers and reasons come back.
Over REST it is GET /v1/projects/<ref>/tables/<table>/csv and POST /v1/projects/<ref>/tables/<table>/csv {csv, columns}.