SQL compatibility and limitations

Implemented, partial, unsupported, and different-behavior — so PG habits don't mislead.

Version
Latest
v0.1.0 · latest 3 min read
On this page
  1. Implemented
  2. Partial
  3. Unsupported / not available in v0.1.0
  4. Different behavior worth knowing

Do not assume PostgreSQL (or any other database) behavior from similar syntax. This page is the implementation-derived record.

Implemented#

DDL/DML above; joins incl. lateral; subqueries incl. correlated EXISTS; WITH [RECURSIVE]; set ops; window functions with PARTITION BY/ORDER BY/ROWS|RANGE|GROUPS frames; GROUPING SETS/ROLLUP/CUBE; DISTINCT [ON]; FILTER; JSON operators/constructors/JSON_TABLE; temporal literals/arithmetic/EXTRACT; ON CONFLICT; RETURNING; EXPLAIN [ANALYZE]; COPY … STDIN/STDOUT (wire); SET/SHOW/DESCRIBE/USE; sequences + nextval/currval; ENUM/DOMAIN/composite types; pg_catalog/information_schema live rows.

Partial#

  • ILIKE parses but behaves as case-sensitive LIKE (verified: SELECT 'ABC' ILIKE 'abc' returns false). Use lower(x) LIKE lower(y) until this is fixed.
  • FOR UPDATE accepted, ignored.
  • AND/OR use is-true checks: NULL AND TRUE → false (differs from strict 3VL).
  • CREATE FUNCTION/PROCEDURE/TRIGGER/EXTENSION/MATERIALIZED VIEW parse to stubs without execution.
  • VACUUM runs accounted maintenance (64-mutation policy, fails closed; explicit VACUUM ungated). Freshness signals: plomid_stat_counters().tables_stale and plomid_stat_tables().is_stale show which tables have writes waiting to be folded into the fast copy. ANALYZE collects real table statistics (row/null/distinct/min/max, persisted). REINDEX/LOCK/CLUSTER/REFRESH/LISTEN/NOTIFY parse; mostly no engine effect.
  • UPDATE…FROM / COPY unsupported inside explicit transactions.
  • CREATE/DROP DATABASE unsupported inside explicit transactions (refused with a descriptive error); run them in autocommit.
  • TABLE name (shorthand SELECT), TABLESAMPLE, SQL-spelled PREPARE/EXECUTE, RESET/RESET ALL, and DISCARD ALL are not supported (syntax errors). Use SELECT * FROM name instead of TABLE; disable pooler checkout RESET/DISCARD statements or expect failures. Extended-protocol prepared statements (Parse/Bind/Describe/Execute) are supported — only the SQL-spelled PREPARE is missing.
  • Bare VALUES (…), (…) as a standalone query is not supported; use it as an INSERT source or a SELECT union instead.
  • LIMIT/OFFSET only integer literal or cast.

Unsupported / not available in v0.1.0#

Foreign-key enforcement, RENAME DATABASE, DROP ROLE / DROP USER, setval(), file COPY, cursors, stored-procedure execution, plpgsql beyond DO/PERFORM/RAISE subset, graph/blob types, multi-region/distributed, continuous aggregates/retention/downsampling, binary wire values (text only, except vector results which support binary format for binary-encodable shapes; vector parameters are text-only and binary params fail closed). Dense vector column types are supported with literals, distance operators, top-k, and ANN — see Vector search. TLS transport is supported (verified sslmode=require handshake) — see TLS.

Different behavior worth knowing#

  • COUNT(DISTINCT) dedups by to_sql_text (1 ≡ 1.0).
  • AVG(int) returns normalized Numeric (PG typing preserved deliberately).
  • Server default password is plomid; CLI default password is secret; start.sh default is secret. Always set explicitly.
  • No compatibility percentage is claimed. Wire + OID + catalog similarity does not imply semantic compatibility.
Was this page helpful?