---
title: "SQL Compatibility Matrix"
---

# SQL Compatibility Matrix


---

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](#sql-standard-compliance)
2. [Data Types](#data-types)
3. [Query Features](#query-features)
4. [DML Operations](#dml-operations)
5. [DDL Operations](#ddl-operations)
6. [Functions and Operators](#functions-and-operators)
7. [Transaction Support](#transaction-support)
8. [PostgreSQL Compatibility](#postgresql-compatibility)
9. [HeliosDB Extensions](#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

:::caution[GPU acceleration: builds on request]
GPU acceleration (NVIDIA CUDA) is available in HeliosDB Full builds on request and
is not enabled in the public release build; AMD ROCm support is planned. In
the public release build, queries run on the CPU and the GPU settings and commands on
this page have no effect. Contact [sales@heliosdb.com](mailto:sales@heliosdb.com)
about a CUDA-enabled build.
:::

| 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

:::caution[Planned: clustering]
Multi-region and autoscaling are planned with Full clustering; in the current release these
statements have no effect.
:::

| Command | Description |
|---------|-------------|
| `SET preferred_region = 'region'` | Set preferred region |
| `SET consistency_level = 'level'` | Set consistency |
| `SHOW REGIONS` | List regions |
| `SHOW REGION STATE` | Region status |

### Autoscaling Commands

:::caution[Planned: clustering]
Multi-region and autoscaling are planned with Full clustering; in the current release these
statements have no effect.
:::

| 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 STATE` | Autoscale status |
| `SHOW COMPUTE NODES` | List nodes |

### CDC Commands

| Command | Description |
|---------|-------------|
| `SHOW CDC EVENTS [LIMIT n]` | Recent change events from the in-memory log |
| `CREATE CHANGE DATA CAPTURE ON table...` | Create stream (planned) |
| `DROP CHANGE DATA CAPTURE name` | Drop stream (planned) |
| `ALTER CDC STREAM name PAUSE/RESUME` | Control stream (planned) |
| `SHOW CDC STREAMS` | List streams (planned) |
| `SHOW CDC STREAM STATE name` | Stream status (planned) |

---

## Related Documentation

- [README.md](https://www.heliosdb.com/docs/full/features/sql-interface/readme/) - SQL interface overview
- [MIGRATION_TIPS.md](https://www.heliosdb.com/docs/full/features/sql-interface/migration_tips/) - Migration guide
- [SQL_COMMAND_EXTENSIONS.md](https://www.heliosdb.com/docs/full/features/sql-interface/sql_command_extensions/) - Extension reference