HeliosDB SQL Reference

HeliosDB SQL Reference

Version: 7.2.0 | Compatibility: PostgreSQL wire protocol | License: Apache-2.0

---

Connecting to HeliosDB

psql (PostgreSQL CLI)

psql -h localhost -p 5432 -U admin -d heliosdb

Python (psycopg2)

import psycopg2
conn = psycopg2.connect(host='localhost', port=5432, user='admin', password='admin', dbname='heliosdb')
cur = conn.cursor()
cur.execute("SELECT 1 + 2")
print(cur.fetchone())  # (3,)

HTTP API (curl)

curl -s -X POST http://localhost:18443/api/v1/query \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT 1 + 2"}'

See HTTP API Reference for full HTTP endpoint documentation.

---

Data Types

Numeric Types

Type	Aliases	Description
SMALLINT	INT2	2-byte integer (-32768 to 32767)
INTEGER	INT, INT4	4-byte integer
BIGINT	INT8	8-byte integer
SERIAL	SERIAL4	Auto-incrementing 4-byte integer
BIGSERIAL	SERIAL8	Auto-incrementing 8-byte integer
REAL	FLOAT4	4-byte floating point
DOUBLE PRECISION	FLOAT8	8-byte floating point
NUMERIC(p,s)	DECIMAL(p,s)	Arbitrary precision decimal

String Types

Type	Description
VARCHAR(n)	Variable-length string, max n characters
TEXT	Variable-length string, unlimited
CHAR(n)	Fixed-length string, space-padded

Boolean

Type	Valid Values
BOOLEAN	TRUE, FALSE, NULL

Date/Time Types

Type	Format	Example
DATE	YYYY-MM-DD	’2024-01-15’
TIME	HH:MM:SS	’14:30:00’
TIMESTAMP	YYYY-MM-DD HH:MM:SS	’2024-01-15 14:30:00’
TIMESTAMPTZ	With timezone	’2024-01-15 14:30:00+00’

Binary & Special Types

Type	Description
BYTEA	Binary data (hex format)
UUID	128-bit universally unique identifier
JSON	JSON text
JSONB	Binary JSON (parsed and indexed)

Array Types

CREATE TABLE t (ids INTEGER[], tags TEXT[]);
INSERT INTO t VALUES ('{1,2,3}', '{"a","b"}');

---

DDL (Data Definition Language)

CREATE TABLE

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    salary NUMERIC(10,2) DEFAULT 0,
    active BOOLEAN DEFAULT TRUE,
    hired_date DATE
);

CREATE TABLE IF NOT EXISTS logs (id INTEGER, message TEXT);

DROP TABLE

DROP TABLE employees;
DROP TABLE IF EXISTS employees;
DROP TABLE employees CASCADE;

TRUNCATE TABLE

TRUNCATE TABLE employees;

ALTER TABLE

ALTER TABLE employees ADD COLUMN department VARCHAR(50);
ALTER TABLE employees DROP COLUMN department;
ALTER TABLE employees RENAME COLUMN name TO full_name;

CREATE / DROP VIEW

CREATE VIEW active_employees AS
    SELECT id, name, salary FROM employees WHERE active = TRUE;

CREATE OR REPLACE VIEW active_employees AS
    SELECT id, name FROM employees WHERE active = TRUE;

DROP VIEW IF EXISTS active_employees;

SELECT * FROM active_employees;

CREATE / DROP INDEX

CREATE INDEX idx_name ON employees (name);
CREATE UNIQUE INDEX idx_email ON employees (email);
CREATE INDEX idx_lower_name ON employees (LOWER(name));  -- expression index
CREATE INDEX idx_active ON employees (salary) WHERE active = TRUE;  -- partial index
CREATE INDEX CONCURRENTLY idx_salary ON employees (salary);

DROP INDEX IF EXISTS idx_name;
REINDEX TABLE employees;
REINDEX INDEX idx_name;

---

DML (Data Manipulation Language)

INSERT

-- Single row
INSERT INTO employees (name, email, salary) VALUES ('Alice', 'alice@co.com', 75000);

-- Multiple rows
INSERT INTO employees (name, email, salary) VALUES
    ('Bob', 'bob@co.com', 80000),
    ('Charlie', 'charlie@co.com', 65000);

-- With RETURNING
INSERT INTO employees (name, salary) VALUES ('Diana', 90000) RETURNING id, name;

-- Upsert (ON CONFLICT)
INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 80000)
    ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary;

INSERT INTO employees (id, name) VALUES (1, 'Alice')
    ON CONFLICT DO NOTHING;

UPDATE

UPDATE employees SET salary = 85000 WHERE name = 'Alice';
UPDATE employees SET salary = salary * 1.10 WHERE department = 'Engineering';

DELETE

DELETE FROM employees WHERE active = FALSE;
DELETE FROM employees WHERE id = 5;

---

SELECT

Basic SELECT

SELECT * FROM employees;
SELECT name, salary FROM employees;
SELECT DISTINCT department FROM employees;
SELECT 1 + 2 AS result;

WHERE Clause

SELECT * FROM employees WHERE salary > 70000;
SELECT * FROM employees WHERE name = 'Alice' AND active = TRUE;
SELECT * FROM employees WHERE department IN ('Engineering', 'Sales');
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000;
SELECT * FROM employees WHERE name LIKE 'A%';
SELECT * FROM employees WHERE name ILIKE '%alice%';  -- case-insensitive
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE manager_id IS NOT NULL;

ORDER BY

SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY department ASC, salary DESC;
SELECT * FROM employees ORDER BY salary NULLS FIRST;
SELECT * FROM employees ORDER BY salary DESC NULLS LAST;

LIMIT / OFFSET

SELECT * FROM employees ORDER BY id LIMIT 10;
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;

GROUP BY / HAVING

SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM employees
GROUP BY department;

-- GROUP BY expression
SELECT UPPER(department) AS dept, COUNT(*) FROM employees GROUP BY UPPER(department);

-- HAVING
SELECT department, COUNT(*) AS cnt
FROM employees
GROUP BY department
HAVING COUNT(*) > 5 AND AVG(salary) > 60000;

---

Joins

-- INNER JOIN
SELECT e.name, d.name AS dept
FROM employees e INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN (includes unmatched left rows)
SELECT e.name, d.name AS dept
FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;

-- RIGHT JOIN
SELECT e.name, d.name AS dept
FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id;

-- FULL OUTER JOIN
SELECT e.name, d.name AS dept
FROM employees e FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- CROSS JOIN
SELECT e.name, d.name FROM employees e CROSS JOIN departments d;

-- Self-Join
SELECT e.name AS employee, m.name AS manager
FROM employees e JOIN employees m ON e.manager_id = m.id;

-- Multi-table Join
SELECT e.name, d.name AS dept, l.city
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN locations l ON d.location_id = l.id;

---

Set Operations

SELECT name FROM table_a UNION ALL SELECT name FROM table_b;     -- all rows
SELECT name FROM table_a UNION SELECT name FROM table_b;          -- deduplicated
SELECT name FROM table_a INTERSECT SELECT name FROM table_b;      -- common rows
SELECT name FROM table_a EXCEPT SELECT name FROM table_b;         -- in A not in B

---

Subqueries

-- IN subquery
SELECT * FROM employees WHERE dept_id IN (SELECT id FROM departments WHERE name = 'Engineering');

-- NOT IN subquery
SELECT * FROM employees WHERE id NOT IN (SELECT employee_id FROM terminated);

-- EXISTS subquery
SELECT * FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

-- Scalar subquery
SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

---

CTEs (Common Table Expressions)

-- Basic CTE
WITH engineering AS (
    SELECT * FROM employees WHERE department = 'Engineering'
)
SELECT name, salary FROM engineering WHERE salary > 80000;

-- Recursive CTE (generate series)
WITH RECURSIVE nums(n) AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

-- Recursive CTE (hierarchy)
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 0 AS level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree;

---

Window Functions

-- ROW_NUMBER
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees;

-- RANK (with gaps for ties)
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;

-- DENSE_RANK (no gaps)
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;

-- PARTITION BY
SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- LAG / LEAD
SELECT name, salary,
    LAG(salary, 1) OVER (ORDER BY id) AS prev_salary,
    LEAD(salary, 1) OVER (ORDER BY id) AS next_salary
FROM employees;

-- NTILE
SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;

-- Aggregate windows
SELECT name, department, salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    COUNT(*) OVER (PARTITION BY department) AS dept_count
FROM employees;

---

Aggregate Functions

SELECT COUNT(*) FROM employees;
SELECT COUNT(DISTINCT department) FROM employees;
SELECT SUM(salary), AVG(salary), MIN(salary), MAX(salary) FROM employees;

-- Conditional aggregate
SELECT
    COUNT(*) AS total,
    SUM(CASE WHEN salary > 70000 THEN 1 ELSE 0 END) AS high_earners
FROM employees;

-- COUNT(DISTINCT) in GROUP BY
SELECT department, COUNT(DISTINCT title) FROM employees GROUP BY department;

---

Built-in Functions

String Functions

Function	Example	Result
UPPER(str)	UPPER('hello')	HELLO
LOWER(str)	LOWER('HELLO')	hello
LENGTH(str)	LENGTH('hello')	5
TRIM(str)	TRIM(' hi ')	hi
LTRIM(str)	LTRIM(' hi')	hi
RTRIM(str)	RTRIM('hi ')	hi
SUBSTRING(str, pos, len)	SUBSTRING('hello', 2, 3)	ell
REPLACE(str, from, to)	REPLACE('hello', 'l', 'r')	herro
POSITION(sub IN str)	POSITION('ll' IN 'hello')	3
LEFT(str, n)	LEFT('hello', 3)	hel
RIGHT(str, n)	RIGHT('hello', 3)	llo
REPEAT(str, n)	REPEAT('ab', 3)	ababab
REVERSE(str)	REVERSE('hello')	olleh
str1 || str2	'hello' || ' world'	hello world

All string functions work on both literals and column references.

Math Functions

Function	Example	Result
ABS(n)	ABS(-5)	5
ROUND(n)	ROUND(3.7)	4
ROUND(n, d)	ROUND(3.456, 2)	3.46
CEIL(n)	CEIL(3.2)	4
FLOOR(n)	FLOOR(3.8)	3
MOD(a, b)	MOD(10, 3)	1
POWER(a, b)	POWER(2, 10)	1024
SQRT(n)	SQRT(16)	4

Date/Time Functions

Function	Example	Result
NOW()	SELECT NOW()	Current timestamp
CURRENT_TIMESTAMP	SELECT CURRENT_TIMESTAMP	Current timestamp
CURRENT_DATE	SELECT CURRENT_DATE	Current date
EXTRACT(part FROM date)	EXTRACT(YEAR FROM '2024-06-15')	2024
DATE_TRUNC(unit, ts)	DATE_TRUNC('month', '2024-06-15')	2024-06-01

EXTRACT supports: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND. DATE_TRUNC supports: year, month, day, hour, minute.

Conditional Functions

-- COALESCE: first non-NULL value
SELECT COALESCE(nickname, name, 'Unknown') FROM users;

-- NULLIF: returns NULL if values are equal
SELECT NULLIF(score, 0) FROM tests;

-- CASE WHEN (searched)
SELECT name,
    CASE WHEN salary > 80000 THEN 'Senior'
         WHEN salary > 50000 THEN 'Mid'
         ELSE 'Junior' END AS level
FROM employees;

-- CASE (simple)
SELECT name,
    CASE department
        WHEN 'Engineering' THEN 'ENG'
        WHEN 'Sales' THEN 'SLS'
        ELSE 'OTHER' END AS code
FROM employees;

---

Type Casting

CAST(42 AS VARCHAR);           -- integer to string
CAST('123' AS INTEGER);        -- string to integer
CAST(3.14 AS INTEGER);         -- float to integer (truncates)
CAST('true' AS BOOLEAN);       -- string to boolean

-- PostgreSQL :: syntax
42::VARCHAR
'123'::INTEGER
3.14::INTEGER

-- BOOLEAN accepts: true/t/yes/on/1 and false/f/no/off/0
CAST('yes' AS BOOLEAN);        -- true
CAST('0' AS BOOLEAN);          -- false

---

Transactions

BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Rollback
BEGIN;
DELETE FROM important_data;
ROLLBACK;  -- undo the delete

-- Savepoints
BEGIN;
INSERT INTO t VALUES (1);
SAVEPOINT sp1;
INSERT INTO t VALUES (2);
ROLLBACK TO SAVEPOINT sp1;  -- undoes insert of 2
RELEASE SAVEPOINT sp1;
COMMIT;  -- only row 1 is committed

-- Isolation levels
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

---

Stored Functions & Procedures

SQL Functions

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
    SELECT a + b;
$$ LANGUAGE SQL;

SELECT add_numbers(3, 4);  -- returns 7

PL/pgSQL Functions

CREATE OR REPLACE FUNCTION classify_salary(emp_salary NUMERIC)
RETURNS VARCHAR AS $$
DECLARE
    result VARCHAR;
BEGIN
    IF emp_salary > 100000 THEN
        result := 'Executive';
    ELSIF emp_salary > 70000 THEN
        result := 'Senior';
    ELSE
        result := 'Standard';
    END IF;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT classify_salary(85000);  -- returns 'Senior'

Table-Returning Functions

CREATE OR REPLACE FUNCTION get_high_earners(min_salary NUMERIC)
RETURNS TABLE(emp_name VARCHAR, emp_salary NUMERIC) AS $$
BEGIN
    RETURN QUERY SELECT name, salary FROM employees WHERE salary >= min_salary;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_high_earners(80000);

Stored Procedures

CREATE OR REPLACE PROCEDURE give_raise(emp_id INTEGER, amount NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE employees SET salary = salary + amount WHERE id = emp_id;
END;
$$;

CALL give_raise(1, 5000);

Function Features

-- Default parameters
CREATE FUNCTION greet(name VARCHAR DEFAULT 'World')
RETURNS VARCHAR AS $$ SELECT 'Hello, ' || name || '!'; $$ LANGUAGE SQL;

SELECT greet();         -- 'Hello, World!'
SELECT greet('Alice');  -- 'Hello, Alice!'

-- Function overloading (same name, different signatures)
CREATE FUNCTION calc(a INTEGER) RETURNS INTEGER AS $$ SELECT a * 2; $$ LANGUAGE SQL;
CREATE FUNCTION calc(a INTEGER, b INTEGER) RETURNS INTEGER AS $$ SELECT a + b; $$ LANGUAGE SQL;

-- Drop function
DROP FUNCTION IF EXISTS calc(INTEGER);

---

Triggers

-- Create trigger function
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER trg_updated BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Drop trigger
DROP TRIGGER trg_updated ON employees;

Supported trigger timing: BEFORE, AFTER Supported trigger events: INSERT, UPDATE, DELETE Trigger functions receive NEW (for INSERT/UPDATE) and OLD (for UPDATE/DELETE) record variables.

---

JSON Operations

-- Store JSON
CREATE TABLE events (id INTEGER, data JSON);
INSERT INTO events VALUES (1, '{"name": "click", "count": 42}');

-- Extract field (returns JSON)
SELECT data->'name' FROM events;

-- Extract field as text
SELECT data->>'name' FROM events;

-- JSONB for binary storage
CREATE TABLE configs (id INTEGER, settings JSONB);

---

System Queries

-- List tables
SELECT * FROM information_schema.tables WHERE table_schema = 'public';

-- List columns
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'employees';

-- List indexes
SELECT * FROM pg_indexes WHERE tablename = 'employees';

-- Query plan
EXPLAIN SELECT * FROM employees WHERE salary > 70000;
EXPLAIN (FORMAT JSON) SELECT * FROM employees;

---

SERIAL / Auto-Increment

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

INSERT INTO users (name) VALUES ('Alice');  -- id = 1 (auto)
INSERT INTO users (name) VALUES ('Bob');    -- id = 2 (auto)
INSERT INTO users (id, name) VALUES (10, 'Charlie');  -- explicit id
INSERT INTO users (name) VALUES ('Diana');  -- id = 11 (continues after max)

---

Part of the HeliosDB Full workspace. See also: Feature Matrix | HTTP API Reference