Advanced SQL Features

HeliosDB-Lite supports a rich SQL dialect including window functions, recursive CTEs, JSONB operations, full-text search, UPSERT, DISTINCT ON, prepared statements, and CASE expressions. This tutorial walks through each feature.

Prerequisites

HeliosDB-Lite v3.5 or later
Access to the SQL shell (REPL, PostgreSQL wire protocol, or REST API)

Setup — Create Sample Tables

CREATE TABLE sales (
    id          INT PRIMARY KEY,
    employee    TEXT,
    department  TEXT,
    amount      FLOAT8,
    sale_date   TEXT
);

INSERT INTO sales VALUES (1, 'Alice', 'East',  500,  '2025-01-15');
INSERT INTO sales VALUES (2, 'Bob',   'East',  300,  '2025-01-16');
INSERT INTO sales VALUES (3, 'Alice', 'East',  700,  '2025-02-10');
INSERT INTO sales VALUES (4, 'Carol', 'West',  400,  '2025-02-12');
INSERT INTO sales VALUES (5, 'Dave',  'West',  600,  '2025-03-01');
INSERT INTO sales VALUES (6, 'Carol', 'West',  350,  '2025-03-05');
INSERT INTO sales VALUES (7, 'Alice', 'East',  800,  '2025-03-10');
INSERT INTO sales VALUES (8, 'Bob',   'East',  450,  '2025-03-15');

1. Window Functions

Window functions compute values across a set of rows related to the current row, without collapsing the result into groups.

ROW_NUMBER

SELECT employee, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS rank
FROM sales;

RANK and DENSE_RANK

SELECT employee, department, amount,
       RANK()       OVER (PARTITION BY department ORDER BY amount DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS dense_rank
FROM sales;

LAG and LEAD

Access values from previous or next rows:

SELECT employee, sale_date, amount,
       LAG(amount, 1)  OVER (PARTITION BY employee ORDER BY sale_date) AS prev_amount,
       LEAD(amount, 1) OVER (PARTITION BY employee ORDER BY sale_date) AS next_amount
FROM sales;

Expected output (for Alice):

 employee | sale_date  | amount | prev_amount | next_amount
----------+------------+--------+-------------+------------
 Alice    | 2025-01-15 |  500   |             |     700
 Alice    | 2025-02-10 |  700   |     500     |     800
 Alice    | 2025-03-10 |  800   |     700     |

NTILE

Distribute rows into N roughly equal buckets:

SELECT employee, amount,
       NTILE(4) OVER (ORDER BY amount) AS quartile
FROM sales;

SUM / AVG as Window Functions

SELECT employee, amount,
       SUM(amount) OVER (PARTITION BY department ORDER BY sale_date) AS running_total,
       AVG(amount) OVER (PARTITION BY department) AS dept_avg
FROM sales;

2. Common Table Expressions (CTEs)

Non-Recursive CTE

WITH top_sellers AS (
    SELECT employee, SUM(amount) AS total
    FROM sales
    GROUP BY employee
)
SELECT * FROM top_sellers ORDER BY total DESC;

Recursive CTE

Generate a sequence of numbers:

WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT n FROM numbers;

Traverse a hierarchy:

CREATE TABLE categories (
    id        INT PRIMARY KEY,
    name      TEXT,
    parent_id INT
);

INSERT INTO categories VALUES (1, 'Electronics', NULL);
INSERT INTO categories VALUES (2, 'Computers',   1);
INSERT INTO categories VALUES (3, 'Laptops',     2);
INSERT INTO categories VALUES (4, 'Phones',      1);

WITH RECURSIVE tree(id, name, depth) AS (
    SELECT id, name, 0 FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, t.depth + 1
    FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

 id | name        | depth
----+-------------+------
  1 | Electronics |     0
  2 | Computers   |     1
  4 | Phones      |     1
  3 | Laptops     |     2

3. JSONB Operations

HeliosDB supports 20+ JSONB functions for semi-structured data.

CREATE TABLE events (
    id   INT PRIMARY KEY,
    data JSONB
);

INSERT INTO events VALUES (1, '{"type": "click", "x": 100, "y": 200, "tags": ["ui", "nav"]}');
INSERT INTO events VALUES (2, '{"type": "scroll", "offset": 450, "tags": ["ui"]}');

-- Extract a field
SELECT id, data->>'type' AS event_type FROM events;

-- Nested access
SELECT id, data->'tags'->>0 AS first_tag FROM events;

-- Containment check
SELECT * FROM events WHERE data @> '{"type": "click"}';

4. Full-Text Search

Use the @@ operator for full-text search:

CREATE TABLE articles (
    id    INT PRIMARY KEY,
    title TEXT,
    body  TEXT
);

INSERT INTO articles VALUES (1, 'Database Internals', 'How databases store and retrieve data efficiently');
INSERT INTO articles VALUES (2, 'Query Optimization', 'Techniques for making SQL queries run faster');
INSERT INTO articles VALUES (3, 'Network Protocols',  'Understanding TCP/IP and HTTP');

-- Full-text search with @@
SELECT id, title FROM articles WHERE body @@ 'database';

 id | title
----+-------------------
  1 | Database Internals

5. UPSERT (INSERT … ON CONFLICT)

Insert a row or update it if a conflict occurs on the primary key:

CREATE TABLE counters (
    name  TEXT PRIMARY KEY,
    value INT
);

INSERT INTO counters VALUES ('visits', 1);

-- Upsert: increment if exists, insert if not
INSERT INTO counters VALUES ('visits', 1)
ON CONFLICT (name) DO UPDATE SET value = counters.value + 1;

SELECT * FROM counters;

 name   | value
--------+------
 visits |     2

6. DISTINCT ON

Return only the first row for each distinct value of a column:

SELECT DISTINCT ON (department) department, employee, amount
FROM sales
ORDER BY department, amount DESC;

 department | employee | amount
------------+----------+-------
 East       | Alice    |   800
 West       | Dave     |   600

This returns the highest sale per department.

7. PREPARE / EXECUTE

Prepared statements parse and plan a query once, then execute it multiple times with different parameters:

PREPARE find_sales AS
SELECT * FROM sales WHERE employee = $1 AND amount > $2;

EXECUTE find_sales('Alice', 600);

 id | employee | department | amount | sale_date
----+----------+------------+--------+-----------
  3 | Alice    | East       |  700   | 2025-02-10
  7 | Alice    | East       |  800   | 2025-03-10

EXECUTE find_sales('Bob', 400);

 id | employee | department | amount | sale_date
----+----------+------------+--------+-----------
  8 | Bob      | East       |  450   | 2025-03-15

8. CASE Expressions

Searched CASE

SELECT employee, amount,
    CASE
        WHEN amount >= 700 THEN 'High'
        WHEN amount >= 400 THEN 'Medium'
        ELSE 'Low'
    END AS tier
FROM sales;

Simple CASE

SELECT employee, department,
    CASE department
        WHEN 'East' THEN 'Eastern Region'
        WHEN 'West' THEN 'Western Region'
        ELSE 'Other'
    END AS region_name
FROM sales;

Tips and Troubleshooting

Window function performance: Window functions with PARTITION BY benefit from indexes on the partition column. Large result sets without partitioning can be memory-intensive.
Recursive CTE depth: The default maximum recursion depth is 1,000 iterations. Configure via resource_quotas.max_recursive_cte_depth in heliosdb.toml.
JSONB indexing: For frequently queried JSONB fields, consider extracting them into regular columns for better index support.
Full-text search: The @@ operator performs a case-insensitive substring match. For more advanced ranking, combine with ORDER BY on relevance scores.
UPSERT limitations: The ON CONFLICT clause requires a unique or primary key constraint on the conflict target columns.
Prepared statement lifecycle: Prepared statements persist for the lifetime of the database connection. They are not shared across connections.
DISTINCT ON ordering: The ORDER BY clause must start with the same columns used in DISTINCT ON. Additional sort columns after the distinct columns determine which row is selected from each group.