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
- SQL Standard Compliance
- Data Types
- Query Features
- DML Operations
- DDL Operations
- Functions and Operators
- Transaction Support
- PostgreSQL Compatibility
- 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 |
