Skip to content

SQL Compatibility Matrix

Version: v5.5 Last Updated: January 4, 2026

This document provides a comprehensive overview of HeliosDB's SQL standard compliance and compatibility with other database systems.

Table of Contents

  1. SQL Standard Compliance
  2. Data Types
  3. Query Features
  4. DML Operations
  5. DDL Operations
  6. Functions and Operators
  7. Transaction Support
  8. PostgreSQL Compatibility
  9. HeliosDB Extensions

SQL Standard Compliance

Overview

Standard Compliance Level Notes
SQL-92 Full Core SQL features
SQL:1999 Partial CTEs, recursive queries
SQL:2003 Partial Window functions, MERGE
SQL:2011 Partial Temporal queries
SQL:2016 Partial JSON functions

Compliance Details

Feature SQL Standard HeliosDB Support Notes
SELECT SQL-92 Full All standard clauses
INSERT SQL-92 Full Including multi-row
UPDATE SQL-92 Full Including subqueries
DELETE SQL-92 Full Including USING
JOINs SQL-92 Full All join types
Subqueries SQL-92 Full Correlated supported
CTEs SQL:1999 Full WITH clause
Recursive CTEs SQL:1999 Partial Basic support
Window Functions SQL:2003 Full All window functions
MERGE SQL:2003 Planned Not yet implemented
LATERAL SQL:1999 Full Lateral joins
JSON SQL:2016 Full JSON operators

Data Types

Standard SQL Types

SQL Type HeliosDB Type Storage Size Notes
BOOLEAN bool 1 byte TRUE/FALSE/NULL
SMALLINT i16 2 bytes -32768 to 32767
INTEGER i32 4 bytes Standard int
BIGINT i64 8 bytes Large integers
REAL f32 4 bytes Single precision
DOUBLE PRECISION f64 8 bytes Double precision
NUMERIC(p,s) decimal Variable Exact numeric
CHAR(n) string n bytes Fixed-length
VARCHAR(n) string Variable Variable-length
TEXT string Variable Unlimited text
DATE date 4 bytes Calendar date
TIME time 8 bytes Time of day
TIMESTAMP timestamp 8 bytes Date and time
TIMESTAMPTZ timestamptz 8 bytes With timezone
INTERVAL interval 16 bytes Time span
BYTEA bytes Variable Binary data
UUID uuid 16 bytes UUID type
JSON json Variable JSON data
JSONB jsonb Variable Binary JSON
ARRAY array Variable Array types

HeliosDB-Specific Types

Type Description Use Case
VECTOR(n) n-dimensional vector AI/ML embeddings
TIMESERIES Time-series data IoT, metrics
GEOSPATIAL Geographic data Location data

Query Features

SELECT Clauses

Clause Support Notes
SELECT Full Column selection
FROM Full Table references
WHERE Full Filtering
GROUP BY Full Aggregation grouping
HAVING Full Aggregate filtering
ORDER BY Full Sorting
LIMIT Full Row limiting
OFFSET Full Row skipping
DISTINCT Full Duplicate removal
DISTINCT ON Full PostgreSQL extension

JOIN Types

Join Type Support Example
INNER JOIN Full a JOIN b ON a.id = b.id
LEFT JOIN Full a LEFT JOIN b ON ...
RIGHT JOIN Full a RIGHT JOIN b ON ...
FULL OUTER JOIN Full a FULL JOIN b ON ...
CROSS JOIN Full a CROSS JOIN b
NATURAL JOIN Full a NATURAL JOIN b
LATERAL JOIN Full FROM a, LATERAL (...)
SELF JOIN Full Table joined to itself

Set Operations

Operation Support Notes
UNION Full Combine with dedup
UNION ALL Full Combine without dedup
INTERSECT Full Common rows
EXCEPT Full Difference

Subqueries

Type Support Example
Scalar Full SELECT (SELECT max(x) FROM b)
Row Full WHERE (a, b) = (SELECT ...)
Table Full FROM (SELECT ...) AS sub
Correlated Full Referencing outer query
EXISTS Full WHERE EXISTS (SELECT ...)
IN Full WHERE x IN (SELECT ...)
NOT IN Full WHERE x NOT IN (SELECT ...)
ANY/SOME Full WHERE x > ANY (SELECT ...)
ALL Full WHERE x > ALL (SELECT ...)

DML Operations

INSERT

Feature Support Example
Single row Full INSERT INTO t VALUES (1)
Multi-row Full INSERT INTO t VALUES (1), (2)
SELECT Full INSERT INTO t SELECT * FROM s
DEFAULT VALUES Full INSERT INTO t DEFAULT VALUES
ON CONFLICT Full INSERT ... ON CONFLICT DO UPDATE
RETURNING Full INSERT ... RETURNING *

UPDATE

Feature Support Example
Single table Full UPDATE t SET x = 1
FROM clause Full UPDATE t SET x = s.x FROM s
Subqueries Full UPDATE t SET x = (SELECT ...)
RETURNING Full UPDATE ... RETURNING *

DELETE

Feature Support Example
Simple Full DELETE FROM t WHERE ...
USING Full DELETE FROM t USING s
RETURNING Full DELETE ... RETURNING *

DDL Operations

Tables

Operation Support Notes
CREATE TABLE Full Standard syntax
CREATE TABLE AS Full From query
ALTER TABLE Full Add/drop columns
DROP TABLE Full With CASCADE
TRUNCATE Full Fast delete

Indexes

Index Type Support Notes
B-tree Full Default index
Hash Full Equality only
GIN Full Full-text, arrays
GiST Partial Geometric
BRIN Full Block range
Vector (HNSW) Full AI embeddings

Constraints

Constraint Support Notes
PRIMARY KEY Full Unique, not null
FOREIGN KEY Full Referential
UNIQUE Full Unique values
NOT NULL Full Required values
CHECK Full Custom validation
DEFAULT Full Default values
EXCLUDE Partial Exclusion constraints

Functions and Operators

Aggregate Functions

Function Support Notes
COUNT(*) Full Row count
COUNT(col) Full Non-null count
SUM(col) Full Numeric sum
AVG(col) Full Average
MIN(col) Full Minimum
MAX(col) Full Maximum
ARRAY_AGG(col) Full Array aggregation
STRING_AGG(col, sep) Full String concatenation
BOOL_AND(col) Full Boolean AND
BOOL_OR(col) Full Boolean OR
STDDEV(col) Full Standard deviation
VARIANCE(col) Full Variance
PERCENTILE_CONT Full Continuous percentile
PERCENTILE_DISC Full Discrete percentile

Window Functions

Function Support Notes
ROW_NUMBER() Full Sequential number
RANK() Full Rank with gaps
DENSE_RANK() Full Rank without gaps
NTILE(n) Full Bucket distribution
LAG(col, n) Full Previous row value
LEAD(col, n) Full Next row value
FIRST_VALUE(col) Full First in window
LAST_VALUE(col) Full Last in window
NTH_VALUE(col, n) Full Nth in window

String Functions

Function Support Notes
LENGTH(s) Full String length
LOWER(s) Full Lowercase
UPPER(s) Full Uppercase
TRIM(s) Full Remove whitespace
SUBSTRING(s, pos, len) Full Extract substring
CONCAT(s1, s2, ...) Full Concatenate
REPLACE(s, from, to) Full Replace text
SPLIT_PART(s, delim, n) Full Split and extract
REGEXP_MATCH(s, pat) Full Regex match
REGEXP_REPLACE(s, pat, rep) Full Regex replace

Date/Time Functions

Function Support Notes
NOW() Full Current timestamp
CURRENT_DATE Full Current date
CURRENT_TIME Full Current time
DATE_TRUNC(unit, ts) Full Truncate timestamp
EXTRACT(unit FROM ts) Full Extract component
AGE(ts1, ts2) Full Time difference
DATE_PART(unit, ts) Full Extract as float
TO_CHAR(ts, fmt) Full Format timestamp
TO_TIMESTAMP(s, fmt) Full Parse timestamp

JSON Functions

Function Support Notes
-> Full Get JSON element
->> Full Get as text
#> Full Get path
#>> Full Get path as text
@> Full Contains
<@ Full Contained by
? Full Key exists
jsonb_each(j) Full Expand object
jsonb_array_elements(j) Full Expand array
jsonb_set(j, path, val) Full Set value

Transaction Support

Transaction Features

Feature Support Notes
BEGIN Full Start transaction
COMMIT Full Commit changes
ROLLBACK Full Abort changes
SAVEPOINT Full Named savepoints
ROLLBACK TO Full Partial rollback

Isolation Levels

Level Support Notes
READ UNCOMMITTED Promoted Uses READ COMMITTED
READ COMMITTED Full Default level
REPEATABLE READ Full Snapshot isolation
SERIALIZABLE Full Full serialization

PostgreSQL Compatibility

Wire Protocol

Feature Support Notes
Simple Query Full Text protocol
Extended Query Full Prepared statements
COPY Protocol Full Bulk loading
Notification Partial LISTEN/NOTIFY

System Catalogs

Catalog Support Notes
pg_catalog Partial Core tables
information_schema Full Standard views
pg_tables Full Table listing
pg_indexes Full Index listing

HeliosDB Extensions

GPU Commands

Command Description
SET gpu_enabled = true/false Enable/disable GPU
SET gpu_memory_limit = 'size' Set memory limit
SHOW gpu_status Show GPU status
SHOW gpu_devices List GPU devices

Multi-Region Commands

Command Description
SET preferred_region = 'region' Set preferred region
SET consistency_level = 'level' Set consistency
SHOW REGIONS List regions
SHOW REGION STATUS Region status

Autoscaling Commands

Command Description
SET autoscale_enabled = true/false Enable/disable
SET autoscale_target_cu = n Target CU
SET autoscale_min_instances = n Min instances
SET autoscale_max_instances = n Max instances
SHOW AUTOSCALE STATUS Autoscale status
SHOW COMPUTE NODES List nodes

CDC Commands

Command Description
CREATE CHANGE DATA CAPTURE ON table ... Create stream
DROP CHANGE DATA CAPTURE name Drop stream
ALTER CDC STREAM name PAUSE/RESUME Control stream
SHOW CDC STREAMS List streams
SHOW CDC STREAM STATUS name Stream status