Skip to main content

SQL Dialect & Restrictions

DortDB's SQL is based on the PostgreSQL flavor and covers its data-selection subset. It also supports several of PostgreSQL's JSON access operators.

Notable features​

Lateral joins​

A LATERAL subquery may reference tables that appear earlier in the FROM clause, so it is re-evaluated for each outer row:

SELECT t1.id, s.w
FROM t1
JOIN LATERAL (
  SELECT t2.w FROM t2 WHERE t2.id = t1.id
) AS s ON true

DISTINCT ON​

PostgreSQL's DISTINCT ON keeps the first row per distinct value of the given expressions, rather than deduplicating whole rows:

SELECT DISTINCT ON (attr1) attr1, attr2
FROM t1

Filtered aggregates​

Aggregate arguments can be filtered with a FILTER clause, and duplicates removed with DISTINCT. It is also possible to process the values in a specific order with ORDER BY:

SELECT
  count(DISTINCT attr1) AS distinct_count,
  count(x) FILTER (WHERE x % 2 = 0) AS even_count,
  collect(x ORDER BY x) AS ordered_list
FROM t1

Restrictions from the schemaless model​

DortDB sources have no declared schema, so SQL queries that would be ambiguous without one are rejected. The engine must always be able to tell which source a column comes from.

  • Unqualified columns require a single, non-joined source. With more than one source in scope, referencing a bare column name raises an ambiguous column names error, so qualify it (t1.attr). This applies to ORDER BY too, where a SELECT-list alias counts as a bare name: order by the qualified expression (ORDER BY t1.attr) rather than the alias.
  • Natural joins are disabled (there is no schema to infer the join columns from). Use an explicit JOIN ... ON ....
  • Every FROM subquery must have an alias. FROM (SELECT ...) is a parse error; write FROM (SELECT ...) AS s.
  • SELECT * is not supported. Without a schema there is nothing to reliably expand * over. It may occasionally return a result, but that behavior is not something to rely on, so always list the columns you want explicitly.
  • Identifiers are case-sensitive. There is no catalog to fold names against, so people.Name and people.name are different columns. Selecting a column no row has is not an error. The column is simply absent from the result, so a case mismatch surfaces as a missing key rather than a failure.
Referring to an outer scope: nonlocal

By default an unqualified column belongs to the current FROM table. When you embed SQL inside another language (for example Cypher, which does not prefix identifiers), prefix a column with the nonlocal schema to refer to a value from the surrounding scope instead of the current table. See Cross-language Queries.

Not implemented, possibly later​

The parser may recognize these, but they are rejected during planning or execution:

  • Window functions (... OVER (...)).
  • GROUP BY ROLLUP and related grouping-set features.

Not implemented, and not planned​

  • Data definition and modification (CREATE, INSERT, UPDATE, ...). DortDB is read-only.
  • Static typing and schema validation. These conflict with the schema-free model and are omitted across all DortDB languages.

Standard library​

Only a subset of PostgreSQL's standard functions and operators is implemented. Missing functions can be supplied as extensions; SQL is one of the languages that also supports user-defined operators.