SQL Function Reference¶
This page documents SQL functions and aggregate/window additions recently implemented in DecentDB.
For broader syntax coverage, see the SQL reference and feature matrix.
Compatibility catalog and introspection surfaces¶
DecentDB exposes a narrow read-only compatibility layer for SQLite and PostgreSQL-adjacent tooling. These are virtual SQL surfaces, not persistent catalog tables.
SQL type inspection¶
PG_TYPEOF(expression) (also pg_catalog.pg_typeof) returns a TEXT SQL type name, including bigint, double precision, numeric, text, boolean, timestamp without time zone, and timestamp with time zone.
SELECT PG_TYPEOF(CAST(NULL AS NUMERIC)); -- numeric
SELECT PG_TYPEOF(TO_TIMESTAMP(1711929600123456 / 1000000.0));
-- timestamp with time zone
Casts and catalog columns retain their declared types even when the value is NULL. Untyped NULL, and NULL expressions whose type cannot be inferred, report unknown. This function returns type names, not PostgreSQL regtype OIDs. DecentDB integer values use the bigint name because integers are 64-bit. ENUM values report enum; the function does not expose a PostgreSQL type catalog.
SQLite compatibility catalog views¶
SELECT * FROM sqlite_schema;
SELECT * FROM sqlite_master;
SELECT * FROM sqlite_temp_schema;
SELECT * FROM temp.sqlite_schema;
sqlite_schema and sqlite_master expose type, name, tbl_name, rootpage, and sql. rootpage is always 0 because DecentDB does not expose SQLite B-tree root pages. Temporary schema aliases expose session-scoped temp tables, views, and indexes.
Minimal information_schema¶
SELECT * FROM information_schema.schemata;
SELECT * FROM information_schema.tables;
SELECT * FROM information_schema.columns;
information_schema.schemata includes main, temp, and registered schemas. information_schema.tables and information_schema.columns expose visible persistent and temporary table/view metadata with DecentDB type names.
SQLite-compatible PRAGMA table functions¶
The following functions mirror the corresponding PRAGMA shapes and may be used in FROM clauses, joins, filters, and projections:
SELECT * FROM pragma_table_info('users');
SELECT * FROM pragma_table_xinfo('users');
SELECT * FROM pragma_table_list();
SELECT * FROM pragma_index_list('users');
SELECT * FROM pragma_index_info('users_name_idx');
SELECT * FROM pragma_index_xinfo('users_name_idx');
SELECT * FROM pragma_foreign_key_list('orders');
SELECT * FROM pragma_database_list();
main. and temp. prefixes are accepted for these functions. Unknown table or index names return an empty result set for the table-valued helpers.
generate_series¶
generate_series(start, stop [, step]) returns one visible column named value. It supports inclusive integer series with an optional integer step, TIMESTAMP and TIMESTAMPTZ series with an explicit INTERVAL, and date series with an explicit whole-day INTERVAL.
When either timestamp endpoint is TIMESTAMPTZ, the series retains that type and interprets any TIMESTAMP endpoint as UTC, consistent with explicit casts.
SELECT value FROM generate_series(1, 5);
SELECT value FROM generate_series(5, 1, -1);
SELECT value FROM generate_series(
TIMESTAMP '2026-01-01 00:00:00',
TIMESTAMP '2026-01-01 02:00:00',
INTERVAL '1 hour'
);
Zero steps are rejected, temporal series require an explicit interval, and a series may not produce more than 1,000,000 rows.
Compatibility scalar helpers¶
current_database()andcurrent_schema()returnmain.database()andschema()are compatibility aliases returningmain.current_audit_context(key)returns the current connection-local audit context value forkey, orNULL.current_tenant()returns audit contexttenant_idortenant, orNULL.current_actor()returns audit contextactororuser, orNULL.version()returns a DecentDB version string.sqlite_version()andpg_backend_pid()are rejected rather than returning misleading SQLite or PostgreSQL server values.
Audit context inspection¶
sys_audit_context exposes the current handle's audit context as key / value rows.
SET AUDIT CONTEXT tenant_id = 'tenant-a';
SET AUDIT CONTEXT actor = 'alice@example.com';
SELECT current_tenant(), current_actor();
SELECT key, value FROM sys_audit_context ORDER BY key;
Audit context is connection-local. It is used by row policies, column masks, and security audit event rows.
Full-Text Search Functions¶
Full-text search is exposed through ordinary SQL functions over USING fulltext indexes.
| Function | Returns | Notes |
|---|---|---|
fulltext_match(index_name, query) | BOOL | Predicate for a full-text index in a WHERE query block. |
bm25(index_name) | FLOAT64 | Ranking score for rows matched by the same-block fulltext_match predicate. |
Example:
CREATE INDEX idx_docs_search
ON docs USING fulltext (title, body)
WITH (prefix = '2,3');
SELECT id, title, bm25('idx_docs_search') AS rank
FROM docs
WHERE fulltext_match('idx_docs_search', '"embedded database" OR search')
ORDER BY rank DESC
LIMIT 20;
The query string supports terms, quoted phrases, uppercase OR, exclusions with -term, and prefix terms such as dec* when the index has matching prefix lengths. Invalid full-text query syntax returns an FTS query error: SQL error. Calling bm25(...) without a compatible same-block fulltext_match(...) returns an FTS semantic error:.
Operational inspection views¶
DecentDB exposes operational inspection surfaces through stable, read-only sys.* views. These are not persistent catalog tables and do not accept bind parameters. Use the documented SELECT * forms; arbitrary projection, joins, LIMIT, and bind parameters are not part of this surface.
The canonical operational surfaces are:
SELECT * FROM sys.sync_status;
SELECT * FROM sys.wal_metrics;
SELECT * FROM sys.process_coordination;
SELECT * FROM sys.process_readers;
SELECT * FROM sys.process_lock_metrics;
SELECT * FROM sys.write_queue_metrics;
SELECT * FROM sys.storage_metrics;
SELECT * FROM sys.reactive_metrics;
SELECT * FROM sys.reactive_subscriptions;
SELECT * FROM sys.extensions;
SELECT * FROM sys.extension_functions;
SELECT * FROM sys.extension_collations;
SELECT * FROM sys.extension_dependencies;
SELECT * FROM sys.extension_validation;
SELECT * FROM sys.sessions;
SELECT * FROM sys.slow_queries;
SELECT * FROM sys.lock_waits;
SELECT * FROM sys.index_usage;
SELECT * FROM sys.doctor_findings;
SELECT * FROM sys.fix_plan;
SELECT * FROM sys.plan_cache;
SELECT * FROM sys.plan_cache_summary;
sys.plan_cache returns one row per cached entry with the cache key hash (SipHash-style, not portable across engine versions), the persistent and temp schema cookies, the policy/mask generation, hit count, last-used timestamp, and a closed-enum statement category. sys.plan_cache_summary returns one row with aggregate counters (total_entries, total_hits, total_misses, total_evictions, total_size_bytes, max_size_bytes, total_oversized_refusals, hit_rate). Neither view exposes the full SQL text of cached statements. Use PRAGMA flush_plan_cache to evict all entries and reset the counters. See design/_archive/WIN_QUERY_PLAN_CACHING_AND_STATEMENT_REUSE.md and ADR 0190-0194 for the full contract.
sys.doctor_findings includes current-connection plan-cache guidance when the cache is disabled, oversized entries are refused, eviction churn is observed, or sustained lookup history shows a low hit rate.
Legacy sys_sync_* names remain for sync inspection compatibility:
sys_sync_statussys_sync_journalsys_sync_retentionsys_sync_peer_lagsys_sync_peerssys_sync_scopessys_sync_scope_tablessys_sync_peer_scopessys_sync_sessionssys_sync_conflict_policysys_sync_conflictssys_sync_doctor
sys.sync_status¶
One row describing the local sync state. It has the same shape and values as legacy sys_sync_status.
| Column | Type | Nullable | Unit / meaning |
|---|---|---|---|
enabled | BOOL | no | Whether sync capture is enabled. |
replica_id | TEXT | yes | Local replica identity, or NULL before sync initialization. |
next_sequence | INT64 | no | Next local sync journal sequence number. |
journal_path | TEXT | no | Sync journal sidecar path for this database handle. |
journal_size_bytes | INT64 | no | Current sync journal size in bytes. |
Example:
sys.write_queue_metrics¶
One row describing the current engine-owned write queue snapshot. All columns are non-null. Calling this view may initialize the lazy queue object, but it does not route direct writes through queued execution.
| Column | Type | Unit / meaning |
|---|---|---|
capacity | INT64 | Maximum admitted queued requests for this handle. |
current_depth | INT64 | Requests currently waiting in the queue. |
admitted | INT64 | Requests admitted since this handle's queue was initialized. |
rejected | INT64 | Requests rejected because immediate admission was impossible. |
timed_out | INT64 | Requests timed out before admission or execution start. |
canceled | INT64 | Requests canceled before execution start. |
executed | INT64 | Requests whose SQL execution started. |
committed | INT64 | Successfully committed queued requests. |
failed | INT64 | Queued requests that failed during execution or group sync. |
group_commit_batches | INT64 | Successful queued batches covered by strict group commit accounting. |
group_commit_syncs | INT64 | Physical WAL syncs performed for grouped queued batches. |
group_commit_max_batch | INT64 | Largest successful queued batch size observed. |
group_commit_commits_covered | INT64 | Successful queued commits covered by group commit accounting. |
physical_syncs_saved | INT64 | Estimated syncs avoided by grouped queued commits. |
total_queue_wait_ns | INT64 | Sum of queue wait time for executed requests, in nanoseconds. |
Example:
sys.wal_metrics¶
One row describing the current WAL runtime state. All columns are non-null.
| Column | Type | Unit / meaning |
|---|---|---|
latest_lsn | INT64 | Current WAL end offset / latest visible snapshot boundary. |
file_size_bytes | INT64 | WAL sidecar file size in bytes. |
active_readers | INT64 | Active WAL reader snapshots registered on this shared WAL. |
max_page_count | INT64 | Maximum page count currently known to the WAL handle. |
checkpoint_epoch | INT64 | In-memory checkpoint epoch counter for this WAL handle. |
warning_count | INT64 | Current reader-retention warning count. |
version_count | INT64 | WAL page versions tracked in memory or sidecar index. |
resident_versions | INT64 | WAL page versions with resident payloads. |
on_disk_versions | INT64 | WAL page versions whose payload is read back from WAL storage. |
shared_wal | BOOL | Whether this handle is using the process shared-WAL registry. |
Example:
sys.process_coordination¶
One row describing this handle's cross-process coordination mode and observed sidecar state.
| Column | Type | Unit / meaning |
|---|---|---|
mode | TEXT | auto, required, or single_process_unsafe. |
enabled | BOOL | Whether this handle is using the coordination sidecar. |
supported | BOOL | Whether the selected VFS can support process coordination. |
coord_path | TEXT | Coordination sidecar path, or NULL when disabled. |
coord_version | INT64 | Sidecar format version. |
coordinator_generation | INT64 | Sidecar generation observed by this handle. |
wal_end_lsn | INT64 | WAL end LSN published in the sidecar. |
checkpoint_generation | INT64 | Checkpoint generation published in the sidecar. |
last_refresh_lsn | INT64 | Last WAL snapshot LSN visible to this handle. |
last_refresh_age_ms | INT64 | Age of the last coordination refresh, or NULL. |
sys.process_readers¶
One row per active cross-process reader slot. Empty when coordination is disabled or no external reader slots are active.
| Column | Type | Unit / meaning |
|---|---|---|
slot_id | INT64 | Fixed sidecar reader slot number. |
pid | INT64 | Owning process ID. |
connection_id | TEXT | Best-effort per-connection owner token. |
snapshot_lsn | INT64 | Reader snapshot LSN retained by this slot. |
age_ms | INT64 | Reader slot age. |
heartbeat_age_ms | INT64 | Time since the slot was last refreshed. |
state | TEXT | Current slot state. |
retention_blocking | BOOL | Whether the slot can block WAL truncation. |
sys.process_lock_metrics¶
One row describing cross-process lock wait counters and current lock owners.
| Column | Type | Unit / meaning |
|---|---|---|
writer_lock_waits | INT64 | Writer lock acquisitions that waited or succeeded. |
writer_lock_timeouts | INT64 | Writer lock acquisition timeouts. |
current_writer_pid | INT64 | Current writer lock owner PID, or NULL. |
current_writer_lock_age_ms | INT64 | Current writer lock age, or NULL. |
current_checkpoint_pid | INT64 | Current checkpoint lock owner PID, or NULL. |
current_checkpoint_lock_age_ms | INT64 | Current checkpoint lock age, or NULL. |
checkpoint_lock_waits | INT64 | Checkpoint lock acquisitions that waited or succeeded. |
checkpoint_lock_timeouts | INT64 | Checkpoint lock acquisition timeouts. |
reader_slots_allocated | INT64 | Reader slot allocations by this handle. |
stale_slots_cleaned | INT64 | Stale reader slots reclaimed by this handle. |
wal_refreshes | INT64 | WAL refreshes after external generation changes. |
wal_refresh_failures | INT64 | Failed WAL refresh attempts. |
sys.storage_metrics¶
One row describing the current database file and storage snapshot. All columns are non-null.
| Column | Type | Unit / meaning |
|---|---|---|
path | TEXT | Database path for this handle. |
wal_path | TEXT | WAL sidecar path for this handle. |
format_version | INT64 | Decoded database file-format version. |
page_size | INT64 | Database page size in bytes. |
cache_size_mb | INT64 | Configured page cache size in MiB. |
page_count | INT64 | Database file page count on disk. |
schema_cookie | INT64 | Current schema cookie from the database header. |
wal_end_lsn | INT64 | Current WAL end offset / latest visible snapshot boundary. |
wal_file_size | INT64 | WAL sidecar file size in bytes. |
last_checkpoint_lsn | INT64 | Last checkpoint LSN persisted in the database header. |
active_readers | INT64 | Active WAL reader snapshots. |
wal_versions | INT64 | WAL page versions tracked in memory or sidecar index. |
warning_count | INT64 | Current reader-retention warning count. |
shared_wal | BOOL | Whether this handle is using the process shared-WAL registry. |
Example:
sys.reactive_metrics¶
One row describing in-process reactive subscription state.
| Column | Type | Unit / meaning |
|---|---|---|
active_watch_count | INT64 | Active table, range, query, and change-stream watches. |
table_watch_count | INT64 | Active table watches. |
range_watch_count | INT64 | Active primary-key range watches. |
query_watch_count | INT64 | Active query watches. |
change_stream_count | INT64 | Active change streams. |
events_published | INT64 | Commit events published by the reactive hub. |
events_delivered | INT64 | Events delivered to watch queues without overflow. |
events_dropped | INT64 | Events dropped because a watch queue lagged. |
lagged_watch_count | INT64 | Watches currently marked lagged. |
row_change_events_truncated | INT64 | Commit events whose row details were reduced to table invalidation. |
Example:
sys.reactive_subscriptions¶
One row per active in-process watch.
| Column | Type | Unit / meaning |
|---|---|---|
watch_id | INT64 | In-process watch identifier. |
kind | TEXT | table, range, query, or change_stream. |
created_at_micros | INT64 | Watch creation timestamp in Unix microseconds. |
queue_capacity | INT64 | Per-watch event queue capacity. |
queue_depth | INT64 | Events currently waiting in the watch queue. |
last_delivered_event_id | INT64 | Last event ID read by the watch handle. |
dropped_events | INT64 | Events dropped for this watch because of queue overflow. |
lagged | BOOL | Whether the watch is currently lagged and must resynchronize. |
dependencies_json | TEXT | Watch dependency description as JSON. |
Example:
sys.sessions¶
One row per active session for the current database handle.
| Column | Type | Nullable | Unit / meaning |
|---|---|---|---|
session_id | INT64 | no | Session identifier. |
connection_id | TEXT | no | Connection identifier. |
opened_at_unix_ms | INT64 | no | Session open timestamp in Unix milliseconds. |
state | TEXT | no | active, in_transaction, or closed. |
tracing_enabled | BOOL | no | Whether tracing is enabled for this session. |
slow_query_threshold_us | INT64 | yes | Configured slow-query threshold, or NULL. |
database_id_hash | TEXT | no | Short SHA-256 hash of the database path. |
Example:
sys.slow_queries¶
One row per captured slow query statement. Empty when tracing is disabled or no queries have exceeded the threshold.
| Column | Type | Nullable | Unit / meaning |
|---|---|---|---|
event_id | INT64 | no | Unique event identifier. |
session_id | INT64 | no | Owning session identifier. |
connection_id | TEXT | no | Owning connection identifier. |
started_at_unix_ms | INT64 | no | Statement start timestamp in Unix milliseconds. |
duration_us | INT64 | no | Elapsed execution time in microseconds. |
threshold_us | INT64 | no | Threshold that qualified this event. |
statement_kind | TEXT | no | Statement type, e.g. SELECT, INSERT, UPDATE. |
read_only | BOOL | no | Whether the statement was read-only. |
sql_fingerprint | TEXT | no | Normalized query fingerprint. |
sql_text | TEXT | no | Full SQL text when sql_text_mode = full; otherwise empty. |
sql_text_mode | TEXT | no | none or full. |
status | TEXT | no | Execution status, e.g. ok or error. |
error_code | TEXT | yes | Error code if status is error. |
database_id_hash | TEXT | no | Short SHA-256 hash of the database path. |
internal | BOOL | no | Whether the statement originated from internal logic. |
Example:
sys.lock_waits¶
One row per captured lock-wait event. Empty when lock-wait tracing is disabled or no waits have exceeded the threshold.
| Column | Type | Nullable | Unit / meaning |
|---|---|---|---|
event_id | INT64 | no | Unique event identifier. |
session_id | INT64 | no | Owning session identifier. |
connection_id | TEXT | no | Owning connection identifier. |
duration_us | INT64 | no | Time spent waiting for the lock in microseconds. |
threshold_us | INT64 | no | Threshold that qualified this event. |
wait_source | TEXT | no | Source of the wait, e.g. sql_write or process_writer. |
status | TEXT | no | Acquisition result: ok, busy, or timeout. |
database_id_hash | TEXT | no | Short SHA-256 hash of the database path. |
internal | BOOL | no | Whether the wait originated from internal logic. |
Example:
sys.index_usage¶
One row per index with observed read or write traffic. Empty when index-usage tracing is disabled or no indexes have been accessed.
| Column | Type | Nullable | Unit / meaning |
|---|---|---|---|
table_name | TEXT | no | Table the index belongs to. |
index_name | TEXT | no | Index name. |
index_kind | TEXT | no | Index kind, e.g. btree. |
read_count | INT64 | no | Number of observed index reads. |
write_count | INT64 | no | Number of observed index writes. |
Example:
sys.doctor_findings¶
Merged findings from the static operational Doctor report and the runtime Advisor engine. Empty when no findings are present.
| Column | Type | Nullable | Unit / meaning |
|---|---|---|---|
id | TEXT | no | Finding identifier. |
category | TEXT | no | Category, e.g. Wal, Indexes, Storage, Query, Index, Contention. |
severity | TEXT | no | info, warning, or error. |
title | TEXT | no | Short finding title. |
message | TEXT | no | Detailed description. |
evidence | TEXT | no | Evidence as key-value pairs. |
recommendation | TEXT | no | Human-readable recommendation. |
Example:
sys.fix_plan¶
One row per safe-to-apply fix plan emitted by the Advisor engine. Empty when no plans are available.
| Column | Type | Nullable | Unit / meaning |
|---|---|---|---|
advisor_id | TEXT | no | Advisor that produced this plan. |
action | TEXT | no | Planned action, e.g. checkpoint. |
target | TEXT | no | Target of the action, e.g. wal. |
auto_safe | TEXT | no | Safety classification: auto, manual, or review. |
Example:
Lifecycle and compatibility notes¶
sys.write_queue_metricsis a one-row snapshot ofDb::write_queue_metricsand the C ABIddb_db_write_queue_metricsvalues. Counter values are accumulated for the current database handle's lazy queue lifetime and reset when the database is reopened through a new handle.sys.reactive_metricsandsys.reactive_subscriptionsdescribe only in-process watch handles. They are not durable changefeed state and reset when the process exits.sys.storage_metricsis a one-row snapshot equivalent toDb::storage_infofor stable fields, and includes both database and WAL paths.sys.wal_metricsis a one-row snapshot of internal WAL runtime counters such as active readers, warning state, payload versions, and checkpoint state.sys.process_coordination,sys.process_readers, andsys.process_lock_metricsdescribe native cross-process coordination for the current local database handle. They return disabled/empty snapshots on unsupported VFSes orsingle_process_unsafeopens.sys.sync_statusis the canonical name for the sync status row. Thesys_sync_statuscompatibility name remains supported.- Runtime tracing views (
sys.slow_queries,sys.lock_waits,sys.index_usage,sys.doctor_findings,sys.fix_plan) are in-memory snapshots. They do not write telemetry rows or create catalog objects, and they reset when the database handle is closed.
sys_sync_status¶
Columns:
enabledreplica_idnext_sequencejournal_pathjournal_size_bytes
Example:
sys_sync_journal¶
Columns:
sequencereplica_idtransaction_lsntable_nameoperationprimary_key_jsonafter_jsonschema_cookiecommitted_at_micros
Notes:
SELECT * FROM sys_sync_journalreturns the full journal.SELECT * FROM sys_sync_journal WHERE sequence > 42is also recognized by the engine for incremental inspection.- Ordered variants on
sequenceare accepted.
Example:
sys_sync_peers¶
Columns:
nameendpointtoken_envcreated_at_microsupdated_at_micros
sys_sync_retention¶
Columns:
journal_recordsfirst_sequencelast_sequencesafe_prune_throughprunable_recordsblocked_by_jsonjournal_size_bytes
SELECT * FROM sys.sync_retention is the canonical dotted alias. Shape client checkpoints are retention blockers and appear as shape:<shape>:client:<id> in blocked_by_json.
sys_sync_peer_lag¶
Columns:
peer_nameremote_replica_idin_watermarkout_watermarklocal_high_watermarkin_lagout_lag
SELECT * FROM sys.sync_peer_lag is the canonical dotted alias.
sys.sync_relay_status¶
Columns:
relay_idprotocol_versiondatabase_replica_idproduction_modesecure_transport_requiredinsecure_override_enabledactive_sessionsactive_streamsstarted_at_micros
sys.sync_relay_sessions¶
Columns:
session_idtenant_idsubject_idsubject_kindrequest_idoperationscope_nameshape_idstarted_at_microsended_at_microsstatuserrorrows_seenbytes_seen
sys.sync_shapes¶
Columns:
shape_idnamescope_nametenant_idallowed_roles_jsonallowed_subjects_jsoncreated_at_microsupdated_at_microsretention_ttl_microsmax_recordsack_deadline_microsheartbeat_micros
sys.sync_shape_clients¶
Columns:
shape_idtenant_idclient_replica_idsubject_idsession_idlast_ack_sequencelast_ack_watermarklast_changeset_idlast_seen_at_microsretention_blockingstatus
sys.sync_changeset_history¶
Columns:
changeset_idsource_replica_idsource_kindscope_nameshape_idrecord_countbytescreated_at_microsapplied_at_microsoutcomeintegrity_hash
sys_sync_doctor¶
Columns:
enabledreplica_idhighest_severityjournal_recordsjournal_size_bytesunresolved_conflictsguidance_json
sys_sync_scopes¶
Columns:
nameinclude_tables_jsonrow_filterfilter_columns_jsoncreated_at_microsupdated_at_micros
sys_sync_scope_tables¶
Columns:
scope_nametable_name
sys_sync_peer_scopes¶
Columns:
peer_namescope_namecreated_at_microsupdated_at_micros
sys_sync_sessions¶
Columns:
session_idpeer_namedirectionremote_replica_idstarted_at_microsended_at_microsstatuserrorpushed_batch_idpulled_batch_idpushed_seenpushed_appliedpushed_skippedpushed_conflictedpulled_seenpulled_appliedpulled_skippedpulled_conflictedretry_count
sys_sync_conflict_policy¶
Columns:
default_policyorigin_priority_json
sys_sync_conflicts¶
Columns:
conflict_idbatch_idremote_replica_idremote_sequencetable_nameoperationconflict_typemessageprimary_key_jsonremote_record_jsonlocal_row_jsoncreated_at_microsresolvedresolutionresolved_at_microsresolved_byresolution_notepolicy_namelocal_record_json
Example:
Subquery operators¶
Supported:
EXISTS (subquery)/NOT EXISTS (subquery)expr op ANY (subquery)andexpr op SOME (subquery)(SOMEis a synonym)expr op ALL (subquery)
Behavior notes:
- Subquery comparison operators support
=,<>/!=,<,<=,>,>=. ANYreturnsTRUEif at least one comparison is true;ALLreturnsTRUEonly if all comparisons are true.- Empty subquery semantics follow SQL quantifier rules:
ANYyieldsFALSE,ALLyieldsTRUE. NULLcomparison propagation follows SQL three-valued logic.
Examples:
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM peers);
SELECT * FROM employees
WHERE salary >= ALL (SELECT salary FROM peers);
Regex comparison operators¶
Supported:
left ~ pattern(case-sensitive match)left ~* pattern(case-insensitive match)left !~ pattern(case-sensitive non-match)left !~* pattern(case-insensitive non-match)
Behavior notes:
- Both operands must be
TEXT; otherwise an SQL type error is raised. NULLoperands yieldNULL.- Invalid regex patterns return an SQL error.
Examples:
SELECT name FROM users WHERE name ~ '^A';
SELECT name FROM users WHERE name ~* '^admin';
SELECT name FROM users WHERE name !~ 'bot$';
SELECT name FROM users WHERE name !~* '^test_';
Math functions¶
Trigonometric¶
Supported:
SIN(x)COS(x)TAN(x)ASIN(x)ACOS(x)ATAN(x)ATAN2(y, x)PI()DEGREES(x)RADIANS(x)COT(x)
Behavior notes:
- Numeric inputs are accepted (
INT64,FLOAT64,DECIMAL); outputs areFLOAT64. ASINandACOSreturnNULLfor out-of-domain values outside[-1, 1].TANreturnsNULLnear undefined points (odd multiples ofπ/2).COTreturnsNULLwhentan(x)is approximately zero.
Examples:
SELECT SIN(PI() / 2), COS(0), TAN(PI() / 4);
SELECT ASIN(1), ACOS(0), ATAN2(1, 1);
SELECT DEGREES(PI()), RADIANS(180), COT(PI() / 4);
Conditional functions¶
Supported:
GREATEST(value1, value2, ...)LEAST(value1, value2, ...)IIF(condition, then_value, else_value)
Behavior notes:
GREATEST/LEASTreturnNULLif any argument isNULL.IIFfollowsCASE-like behavior and uses DecentDB truthiness semantics for the condition.
Examples:
Date/time functions¶
Supported:
DATE_TRUNC(precision, timestamp)DATE_PART(field, timestamp)DATE_DIFF(part, start, end)LAST_DAY(timestamp)NEXT_DAY(timestamp, weekday)MAKE_DATE(year, month, day)MAKE_TIMESTAMP(year, month, day, hour, minute, second)TO_TIMESTAMP(epoch_or_text [, format])AGE(timestamp [, timestamp])INTERVAL '...'(for timestamp arithmetic)
Behavior notes:
DATE_TRUNCsupports: microsecond, millisecond, second, minute, hour, day, week, month, quarter, year, decade, century, millennium.TO_TIMESTAMP(text, format)currently supports formats:YYYY-MM-DD HH24:MI:SS,YYYY-MM-DD, andDD/MM/YYYY.TO_TIMESTAMPreturnsTIMESTAMPTZin UTC for numeric epoch and text/format inputs. Epoch seconds may beINT64,FLOAT64, orDECIMAL; fractions are rounded to microseconds. Non-finite or out-of-range epochs return a SQL error. CLI and REPL output renders timestamps as readable dates, with+00for UTC timezone-aware values.AGEreturns a textual interval (for example,"1 days 00:00:00").INTERVALliteral parsing supports integeryear/month/week/day/hour/minute/secondunits in amount-unit pairs.- Timestamp interval arithmetic supports
timestamp +/- INTERVAL '...'and date/timestamp text on the left side.
Examples:
SELECT DATE_TRUNC('month', '2024-03-15 14:30:45');
SELECT DATE_PART('doy', '2024-03-15');
SELECT DATE_DIFF('day', '2024-03-10', '2024-03-15');
SELECT LAST_DAY('2024-02-11'), NEXT_DAY('2024-03-15', 'Monday');
SELECT MAKE_DATE(2024, 3, 15), MAKE_TIMESTAMP(2024, 3, 15, 14, 30, 0);
SELECT TO_TIMESTAMP(1710505800), TO_TIMESTAMP('15/03/2024', 'DD/MM/YYYY');
SELECT AGE('2024-03-15', '2024-03-14');
SELECT '2024-03-15 14:30:00'::timestamp + INTERVAL '1 day';
String functions¶
Supported:
CONCAT(expr, ...)CONCAT_WS(separator, expr, ...)POSITION(substring IN string)INITCAP(string)ASCII(string)REGEXP_REPLACE(string, pattern, replacement [, flags])SPLIT_PART(string, delimiter, index)STRING_TO_ARRAY(string, delimiter)QUOTE_IDENT(string)QUOTE_LITERAL(string)MD5(string)SHA256(string)
Behavior notes:
CONCATtreatsNULLarguments as empty strings.CONCAT_WSskipsNULLvalue arguments;NULLseparator returnsNULL.POSITIONreturns 1-based positions, and0if no match exists.REGEXP_REPLACEsupportsg(global) andi(case-insensitive) flags.STRING_TO_ARRAYreturns a JSON text array.
Examples:
SELECT CONCAT('hello', ' ', 'world');
SELECT CONCAT_WS(', ', 'Alice', NULL, 'Bob');
SELECT POSITION('world' IN 'hello world');
SELECT INITCAP('hello world from decentdb');
SELECT ASCII('A');
SELECT REGEXP_REPLACE('abc123def', '\d', '', 'g');
SELECT SPLIT_PART('a,b,c', ',', 2);
SELECT STRING_TO_ARRAY('a,b,c', ',');
SELECT QUOTE_IDENT('table name'), QUOTE_LITERAL('O''Brien');
SELECT MD5('hello'), SHA256('hello');
Spatial functions¶
Spatial functions operate on native GEOMETRY and GEOGRAPHY values. Spatial values are stored as normalized EWKB; GEOGRAPHY uses SRID 4326 and lon/lat coordinates.
Supported:
- Constructors:
ST_Point,ST_MakePoint,ST_PointZ,ST_PointM,ST_PointZM - Geography point constructors:
ST_GeogPoint,ST_GeogPointZ,ST_GeogPointM,ST_GeogPointZM - Import/export:
ST_GeomFromText,ST_GeogFromText,ST_GeomFromWKB,ST_GeogFromWKB,ST_GeomFromGeoJSON,ST_GeogFromGeoJSON,ST_AsText,ST_AsBinary,ST_AsGeoJSON - Accessors:
ST_SRID,ST_SetSRID,ST_GeometryType,ST_X,ST_Y,ST_Z,ST_M,ST_IsValid - Predicates:
ST_DWithin,ST_Intersects,ST_Contains,ST_Within,ST_Equals - Measurements:
ST_Distance,ST_Length,ST_Area - Distance ordering:
<->
Behavior notes:
ST_Distancereturns meters forGEOGRAPHYpoint-to-point distance and planar units forGEOMETRY.ST_DWithinuses the same units asST_Distance.ST_LengthandST_Areaare planar forGEOMETRY; GEOGRAPHY uses spherical approximations.- Spatial indexes (
CREATE INDEX ... USING spatial) are single-column indexes forGEOMETRYandGEOGRAPHY.
Examples:
CREATE TABLE places (id INT PRIMARY KEY, geog GEOGRAPHY(POINT,4326));
CREATE INDEX idx_places_geog ON places USING spatial(geog);
INSERT INTO places VALUES (1, ST_GeogPoint(-97.7431, 30.2672));
SELECT id
FROM places
WHERE ST_DWithin(geog, ST_GeogPoint(-97.7431, 30.2672), 5000);
SELECT ST_AsText(ST_GeomFromText('POINT(1 2)'));
SELECT ST_Area(ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0))'));
SELECT id FROM places ORDER BY geog <-> ST_GeogPoint(-97.7431, 30.2672) LIMIT 10;
Aggregate functions¶
Statistical aggregates¶
Supported:
STDDEV(expr)(alias ofSTDDEV_SAMP)STDDEV_SAMP(expr)STDDEV_POP(expr)VARIANCE(expr)(alias ofVAR_SAMP)VAR_SAMP(expr)VAR_POP(expr)
Behavior notes:
- Implemented using a numerically stable online (Welford-style) accumulation strategy.
*_SAMPforms returnNULLwhen fewer than 2 non-NULLvalues exist.- Population forms return
NULLfor empty input sets. DISTINCTis supported.
Boolean aggregates¶
Supported:
BOOL_AND(expr)BOOL_OR(expr)
Behavior notes:
NULLinputs are ignored.- If all values are
NULL, result isNULL. - Non-boolean non-
NULLinputs are rejected.
Collection and ordered-set aggregates¶
Supported:
ARRAY_AGG(expr [ORDER BY ...])MEDIAN(expr)PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY expr)PERCENTILE_DISC(fraction) WITHIN GROUP (ORDER BY expr)
Behavior notes:
ARRAY_AGGreturns JSON text arrays (for example,"[1,null,2]").ARRAY_AGG(DISTINCT ...)is supported.MEDIANreturnsFLOAT64and ignoresNULLinputs.- Percentile fraction must be between
0and1inclusive. PERCENTILE_CONTinterpolates and returnsFLOAT64.PERCENTILE_DISCreturns a value from the ordered input domain.
Examples:
SELECT STDDEV(amount), VARIANCE(amount), BOOL_AND(amount > 0), BOOL_OR(amount > 100) FROM orders;
SELECT ARRAY_AGG(amount ORDER BY created_at) FROM orders;
SELECT MEDIAN(amount) FROM orders;
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) FROM orders;
SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY amount) FROM orders;
Window functions¶
Additional supported window features include:
NTILE(n)PERCENT_RANK()CUME_DIST()- Aggregate window functions such as
SUM(...) OVER (...),COUNT(...) OVER (...),MIN/MAX/AVG/... OVER (...) ROWSframe clausesRANGEframes forUNBOUNDED/CURRENT ROWstyle bounds (offset-basedRANGEbounds are not yet supported)
Examples:
SELECT id, NTILE(4) OVER (ORDER BY salary DESC) AS quartile FROM employees;
SELECT id,
PERCENT_RANK() OVER (ORDER BY score) AS pct_rank,
CUME_DIST() OVER (ORDER BY score) AS cume_dist
FROM results;
SELECT created_at, amount,
SUM(amount) OVER (
ORDER BY created_at
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS rolling_sum
FROM orders;