Use everyday SQL patterns

← SQL examples

Choose a recipe for a named subquery, bound values, time buckets, conditional values or catalog lookup.

Before you run it: The time-bucket recipe does not support ordering by its grouped date_trunc expression; sort that result in your application.

Follow the exampleCopy Anchor Link

1. Load the data named by your chosen recipe.
2. Run that request separately.
3. Compare its response with the explanation.
4. Keep its limits when adapting it to your table.

Prepare the numeric order sampleCopy Anchor Link

These advanced examples use numeric order and customer IDs. They do not use the o_001 / c_1 sample dataset from the introductory SQL recipes. Use an empty test database so the shop.orders definitions do not conflict.

curl --fail-with-body -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/schemas" \
  -H "Authorization: Bearer $OC_TOKEN" -H "Content-Type: text/plain" \
  --data-binary @- <<'TOML'
namespace = "shop"
table = "orders"
primary_key = ["id"]
[[columns]]
name = "id"
ty = "i64"
required = true
[[columns]]
name = "customer_id"
ty = "i64"
[[columns]]
name = "amount_cents"
ty = "i64"
[[columns]]
name = "status"
ty = "str"
[[columns]]
name = "created_at"
ty = "timestamp"
TOML

curl --fail-with-body -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/rows/shop.orders/_batch" \
  -H "Authorization: Bearer $OC_TOKEN" -H "Content-Type: application/json" \
  -d '[
    {"id":1,"customer_id":1,"amount_cents":14900,"status":"paid","created_at":1767225600000},
    {"id":4,"customer_id":1,"amount_cents":3900,"status":"paid","created_at":1767225600000},
    {"id":3,"customer_id":2,"amount_cents":7200,"status":"pending","created_at":1769904000000},
    {"id":6,"customer_id":2,"amount_cents":22000,"status":"paid","created_at":1769904000000}
  ]'

There are two January orders totaling 18,800 cents and two February orders totaling 29,200 cents. The three paid orders total 40,800 cents. These checks help distinguish a query mistake from an incorrectly loaded sample.

The bare SQL blocks below can run in the SQL editor. To use HTTP, send the statement as the sql field to the SQL endpoint; the parameter example already shows that JSON body.

About these examplesCopy Anchor Link

Each example below shows one supported request shape and its recorded result. Check SQL support for query restrictions and window functions for running totals.

Name a subquery, then aggregate over itCopy Anchor Link

A WITH block names the result of a query so a later query can use it. This is called a common table expression, or CTE. Here paid means the paid orders only; the outer query counts and totals them for each customer.

WITH paid AS (
  SELECT customer_id, amount_cents
    FROM shop.orders
   WHERE status = 'paid'
)
SELECT customer_id,
       count(*)          AS n,
       sum(amount_cents) AS total
  FROM paid
 GROUP BY customer_id

Read the resultCopy Anchor Link

{ "kind": "select",
  "columns": ["customer_id", "n", "total"],
  "rows": [
    { "customer_id": 1, "n": 2, "total": 18800 },
    { "customer_id": 2, "n": 1, "total": 22000 }
  ] }

Recursive CTEs (WITH RECURSIVE) execute over /sql too, under their own bounds — one recursive CTE per statement, UNION ALL between the arms, and depth and row caps on the walk. See WITH RECURSIVE. A shape outside those bounds comes back as a clear 400 naming the constraint it broke, never a silent re-interpretation.

Bind values with a params arrayCopy Anchor Link

Keep SQL and values apart: write positional $1, $2 placeholders and pass a params array alongside the statement. Bind user-supplied values instead of interpolating them into the SQL string.

{
  "sql": "SELECT id, amount_cents, status FROM shop.orders WHERE customer_id = $1 AND amount_cents > $2 ORDER BY amount_cents DESC",
  "params": [1, 1000]
}

Read the resultCopy Anchor Link

{ "kind": "select",
  "columns": ["id", "amount_cents", "status"],
  "rows": [
    { "id": 1, "amount_cents": 14900, "status": "paid" },
    { "id": 4, "amount_cents": 3900,  "status": "paid" }
  ] }

To write a timestamp, bind its value through params rather than using an inline TIMESTAMP '…' literal. The sample table also requires an explicit order ID.

{
  "sql": "INSERT INTO shop.orders (id, customer_id, amount_cents, status, created_at) VALUES ($1,$2,$3,$4,$5)",
  "params": [7, 1, 14900, "paid", "2026-01-04 10:00:00"]
}

Roll rows up into time bucketsCopy Anchor Link

The dashboard shape: bucket a timestamp column by day / month and aggregate per bucket, in a single GROUP BY.

SELECT date_trunc('month', created_at) AS m,
       count(*)          AS n,
       sum(amount_cents) AS rev
  FROM shop.orders
 GROUP BY date_trunc('month', created_at)

Read the resultCopy Anchor Link

{ "kind": "select",
  "columns": ["m", "n", "rev"],
  "rows": [
    { "m": 1767225600000, "n": 2, "rev": 18800 },
    { "m": 1769904000000, "n": 2, "rev": 29200 }
  ] }

Buckets come back as epoch-milliseconds (1767225600000 = 2026-01-01). Sort the buckets in your app for now — ORDER BY over a date_trunc group key isn't wired through /sql yet.

Branch inline with CASECopy Anchor Link

Derive a column from a per-row condition without a second query — CASE WHEN … THEN … ELSE … END evaluates in the SELECT list.

SELECT id, amount_cents,
       CASE WHEN amount_cents >= 10000 THEN 'big'
            ELSE 'small' END AS tier
  FROM shop.orders
 ORDER BY id

Read the resultCopy Anchor Link

{ "kind": "select",
  "columns": ["id", "amount_cents", "tier"],
  "rows": [
    { "id": 1, "amount_cents": 14900, "tier": "big"   },
    { "id": 3, "amount_cents": 7200,  "tier": "small" },
    { "id": 4, "amount_cents": 3900,  "tier": "small" },
    { "id": 6, "amount_cents": 22000, "tier": "big"   }
  ] }

Ask the engine about itselfCopy Anchor Link

The examples query information_schema and pg_catalog, then inspect session state with SHOW and current_schema(). Use the documented catalog fields; these examples do not establish compatibility with every ORM discovery query.

SELECT column_name
  FROM information_schema.columns
 WHERE table_name = 'orders'

Read the resultCopy Anchor Link

{ "kind": "select",
  "columns": ["column_name"],
  "rows": [
    { "column_name": "id" }, { "column_name": "customer_id" },
    { "column_name": "amount_cents" }, { "column_name": "status" },
    { "column_name": "created_at" }
  ] }

SHOW search_path → public · SELECT current_schema() → public. SET search_path and GUCs persist for the session.

Expected resultCopy Anchor Link

Each recipe includes its own result to inspect.

ContinueCopy Anchor Link

Continue to the related examples.