SQL reference

SQL lets you select records, combine tables and update data with explicit statements. Start with a small SELECT, then use the sections below for parameters, grouping, joins and writes.

For application access, use the complete OC_URL from Connect with its supported OC_TOKEN credential. The tenant-scoped HTTP examples below are engine reference examples; do not derive or shorten a new connection URL to match them. PostgreSQL database logins currently provide read-only access.

Start with the supported surfaceCopy Anchor Link

OriginChainDB supports SQL selection, filtering, ordering, aggregates, joins, CTEs, set operations, window queries and data writes. Compatibility is scoped: PostgreSQL protocol support is not complete PostgreSQL parity, and a preview feature is not necessarily enabled on every deployment. Read your instance's capabilities and the PostgreSQL compatibility guide before porting an application.

Send a SQL requestCopy Anchor Link

Complete the quickstart to create quickstart.products. Set OC_BASE_URL to your full HTTPS endpoint, OC_TENANT to the tenant ID and OC_BEARER to an instance token.

curl --fail-with-body "$OC_BASE_URL/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_BEARER" \
  -H "Content-Type: application/json" \
  --data '{"sql":"SELECT id, name, price_cents FROM quickstart.products WHERE price_cents < $1 LIMIT 10","params":[2000]}'

For the original quickstart data, the result includes product-001, Trail mug and 1990. A SELECT response has kind: select and a rows array. Check the status before decoding a response as a successful query.

Bind values instead of building SQL stringsCopy Anchor Link

Use $1, $2 and a JSON params array in the same order. Bound values stay separate from SQL text, so do not build a query by concatenating user input. For a client wrapper, follow the Python guide or check the publication status in the SDK directory.

Use parameters for values, not SQL identifiers or whole clauses. Check that the supplied parameter count matches the placeholders. A transaction-control statement does not take a parameter array. Do not substitute a language's ? or :name notation into this HTTP contract.

Select and filter rowsCopy Anchor Link

Use fully qualified table names to make the intended namespace explicit. Give calculated expressions an alias and use a deterministic ORDER BY when paging. The following SQL can be run in Workbench, an enabled SQL client, or the HTTP request's sql field.

SELECT id, name, price_cents
FROM quickstart.products
WHERE price_cents BETWEEN 1000 AND 2000
ORDER BY price_cents DESC, id ASC
LIMIT 10;

SELECT id, name
FROM quickstart.products
WHERE name LIKE '%mug%' AND price_cents IS NOT NULL
LIMIT 10;

Use IS NULL and IS NOT NULL for missing values. column = NULL does not mean “is missing.” Begin with a bounded LIMIT when exploring a large table.

Aggregate and groupCopy Anchor Link

SELECT COUNT(*) AS product_count,
       MIN(price_cents) AS lowest_price,
       MAX(price_cents) AS highest_price
FROM quickstart.products;

SELECT name, COUNT(*) AS products
FROM quickstart.products
GROUP BY name
HAVING COUNT(*) > 0
ORDER BY name
LIMIT 10;

COUNT, SUM, AVG, MIN and MAX cover common summaries. Support for additional aggregates, expression combinations and numeric behavior varies by feature and build; test the exact query rather than inferring the entire PostgreSQL function catalog. Use HAVING to filter a group and WHERE to filter its input rows.

Join related tablesCopy Anchor Link

INNER and outer joins combine records using a supported ON expression. Qualify columns and use explicit output aliases, especially when tables share names such as id. Set up both source tables first. Follow the inner join and left join examples; check returned columns and null behavior when migrating a query.

Use CTEs, set operations and windowsCopy Anchor Link

WITH affordable AS (
    SELECT id, name, price_cents
    FROM quickstart.products
    WHERE price_cents < 2000
)
SELECT id, name FROM affordable ORDER BY id LIMIT 10;

CTEs and supported derived-table queries are available; older blanket statements that all derived tables are refused are obsolete. Recursive CTEs, window frames and combinations of joins, aggregates and windows still have specific limits. Use the recursive CTE example, window examples and the capability response for the shape you need.

Execute data writesCopy Anchor Link

-- Use a new sample ID so this does not replace the quickstart row.
INSERT INTO quickstart.products (id, name, price_cents)
VALUES ('product-sql-demo', 'Travel flask', 2490)
RETURNING id, name;

UPDATE quickstart.products
SET price_cents = 2290
WHERE id = 'product-sql-demo'
RETURNING id, price_cents;

DELETE FROM quickstart.products
WHERE id = 'product-sql-demo'
RETURNING id;

INSERT, UPDATE and DELETE execute. A successful SQL response is not a translation to replay against the row API. These three statements create, change and remove only product-sql-demo; running the INSERT twice before deleting it produces a duplicate-key conflict.

SQL INSERT rejects a duplicate primary key unless you explicitly choose a supported ON CONFLICT action. The ordinary HTTP row-write endpoint is an upsert; use ?expect=insert there for insert-only behavior. Keep a WHERE clause on UPDATE and DELETE and inspect affected rows. Do not assume a missing predicate is always rejected.

For bulk ingestion use the row batch API. Its NDJSON mode commits chunks, so the entire stream is not one all-or-nothing transaction and a later failure can leave earlier chunks committed.

Create and evolve a schemaCopy Anchor Link

CREATE TABLE examples.orders (
    id TEXT PRIMARY KEY,
    customer_id TEXT,
    total_cents BIGINT NOT NULL,
    status TEXT DEFAULT 'pending'
);

CREATE INDEX orders_by_customer ON examples.orders (customer_id);

ALTER TABLE examples.orders ADD COLUMN note TEXT;

This creates a separate examples.orders table; run it once in a development database. It is not part of the quickstart product fixture. Schema changes and access-control statements can form commit boundaries. Do not rely on ROLLBACK to undo DDL. Use the schema reference for types and indexes and constraints for their supported shapes.

Use integer minor units when following these price examples. For DECIMAL/NUMERIC columns, declare precision and scale and test calculations and serialization against your application's requirements; do not substitute binary floats and assume identical decimal behavior.

Inspect a planCopy Anchor Link

EXPLAIN SELECT id FROM quickstart.products WHERE price_cents < 2000;

-- ANALYZE executes the query and reports measured execution information.
EXPLAIN ANALYZE SELECT id FROM quickstart.products WHERE price_cents < 2000;

EXPLAIN inspects the plan. EXPLAIN ANALYZE executes the query and reports measurements; it is not a dry run. Use the plan to check whether an index or a scan serves your predicate. A small LIMIT does not guarantee that a sort, join or aggregate avoids reading a large input.

Group data writesCopy Anchor Link

For multiple calls over HTTP, use one X-OC-Session-Id for BEGIN, statements and COMMIT/ROLLBACK. Current supported deployments let statements see their own transaction's buffered writes. Read the SQL transaction guide for failures, DDL boundaries and topology limits. The preview transaction API has a different isolation contract.

Availability and permissionsCopy Anchor Link

A 403 normally requires checking the caller's permissions. A 402 can indicate a configuration or entitlement requirement. Read the returned code and message; do not rename SQL identifiers or buy an add-on based on an old internal plan name. The console and dedicated pricing page show current configuration options.

Limits and troubleshootingCopy Anchor Link

• Request size, result size, memory, concurrency, recursion and cross-shard gathering have separate limits. A large result can fail even when the SQL text is small.
• Multi-statement requests are supported in documented scopes. DDL and explicit transaction boundaries can change their atomicity. See the compatibility guide; do not treat every semicolon-separated script as one transaction.
• Use X-OC-Statement-Timeout-Ms and a valid unique X-OC-Query-Id where supported. Handle timeout, cancellation and temporary throttling distinctly.
• Capture the engine request ID, statement shape and deployed capability response when reporting a mismatch. Exclude credentials and private values.