---
title: "HeliosDB SQL Reference"
---

# HeliosDB SQL Reference

**Compatibility**: PostgreSQL wire protocol | **License**: SSPL-1.0

---

## Connecting to HeliosDB

### psql (PostgreSQL CLI)
```bash
psql -h localhost -p 5432 -U admin -d heliosdb
```

### Python (psycopg2)
```python
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)
```bash
curl -s -X POST http://localhost:8443/api/v1/query \
  -H "Authorization: Bearer $HELIOSDB_API_KEY" \
  -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
```sql
CREATE TABLE t (ids INTEGER[], tags TEXT[]);
INSERT INTO t VALUES ('{1,2,3}', '{"a","b"}');
```

---

## DDL (Data Definition Language)

### CREATE TABLE
```sql
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
```sql
DROP TABLE employees;
DROP TABLE IF EXISTS employees;
DROP TABLE employees CASCADE;
```

### TRUNCATE TABLE
```sql
TRUNCATE TABLE employees;
```

### ALTER TABLE
```sql
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
```sql
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
```sql
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
```sql
-- 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
```sql
UPDATE employees SET salary = 85000 WHERE name = 'Alice';
UPDATE employees SET salary = salary * 1.10 WHERE department = 'Engineering';
```

### DELETE
```sql
DELETE FROM employees WHERE active = FALSE;
DELETE FROM employees WHERE id = 5;
```

---

## SELECT

### Basic SELECT
```sql
SELECT * FROM employees;
SELECT name, salary FROM employees;
SELECT DISTINCT department FROM employees;
SELECT 1 + 2 AS result;
```

### WHERE Clause
```sql
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
```sql
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
```sql
SELECT * FROM employees ORDER BY id LIMIT 10;
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;
```

### GROUP BY / HAVING
```sql
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

```sql
-- 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

```sql
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

```sql
-- 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)

```sql
-- 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

```sql
-- 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

```sql
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`.

### Conditional Functions
```sql
-- 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

```sql
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

```sql
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
```sql
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
```sql
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
```sql
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
```sql
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
```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
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*