# SQL Reference

# Data types

Name	Description	Length constraints
INTEGER	Signed 64-bit integer value. Usually referred to as `BIGINT` in other databases.	-
BOOLEAN	A boolean value, either `TRUE` or `FALSE`	-
VARCHAR	UTF8-encoded text	Maximum number of bytes in the UTF-8 encoded representation of the string
BLOB	sequence of bytes	Maximum number of bytes in the sequence
TIMESTAMP	datetime value with microsecond precision	-

# Size constraints

Size constraint is specified with a [MAX_SIZE] suffix on the type, e.g. BLOB[16] represents a sequence of up to 16 bytes.

# NULL values

NULL values in immudb are not unique - two NULL values are considered equal on comparisons.

# Timestamp values

Timestamp values are internally stored as a 64-bit signed integer being a number of microseconds since the epoch time. Those values are not associated with any timezone, whenever a conversion is needed, it is considered to be in UTC.

# Creating tables

Common examples of CREATE TABLE statements are presented below.

CREATE TABLE IF NOT EXISTS customers (
    id            INTEGER,
    customer_name VARCHAR[60],
    email         VARCHAR[150],
    address       VARCHAR,
    city          VARCHAR,
    ip            VARCHAR[40],
    country       VARCHAR[15],
    age           INTEGER,
    active        BOOLEAN,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS products (
    id          INTEGER,
    product     VARCHAR NOT NULL,
    price       VARCHAR NOT NULL,
    created_at  TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS orders (
    id          INTEGER AUTO_INCREMENT,
    customerid  INTEGER,
    productid   INTEGER,
    created_at  TIMESTAMP,
    PRIMARY KEY id
);

CREATE TABLE customer_review(
    customerid  INTEGER,
    productid   INTEGER,
    review      VARCHAR,
    created_at  TIMESTAMP,
    PRIMARY KEY (customerid, productid)
);

# `IF NOT EXISTS`

With this clause the CREATE TABLE statement will not fail if a table with same name already exists.

Note: If the table already exists, it is not compared against the provided table definition neither it is updated to match it.

# `NOT NULL`

Columns marked as not null can not have a null value assigned.

# `PRIMARY KEY`

Every table in immudb must have a primary key. Primary key can use at least 1 and up to 8 columns.

Columns used in a primary key can not have NULL values assigned, even if those columns are not explicitly marked as NOT NULL.

Primary key creates an implicit unique index on all contained columns.

# `AUTO_INCREMENT`

A single-column PRIMARY KEY can be marked as AUTO_INCREMENT. immudb will automatically set a unique value of this column for new rows.

When inserting data into a table with an INSERT statement, the value for such primary key must be omitted. When updating data in such table with UPSERT statement, the value for such primary key is obligatory and the UPSERT statement can only update existing rows.

The type of an AUTO_INCREMENT column must be INTEGER. Internally immudb will assign sequentially increasing values for new rows ensuring this value is unique within a single table.

# Foreign keys

Explicit support for relations to foreign tables is not currently supported in immudb. It is possible however to create ordinary columns containing foreign key values that can be used in JOIN statements. Application logic is responsible for ensuring data consistency and foreign key constraints.

SELECT * FROM orders
INNER JOIN customers ON customers.id = orders.customerid
INNER JOIN products ON products.id = orders.productid;

# Altering tables

immudb supports limited table altering. The supported operations are lightweight. They do not require any changes to already written row data and there is no performance penalty on read/write operations in such altered tables.

# `ADD COLUMN`

A new column can be added to an existing table. Such column must be nullable. For rows that already existed in the table before the alter operation, the value of the newly added column will be read as NULL. New column can not be set as AUTO_INCREMENT which is only allowed for the primary key.

ALTER TABLE customers
ADD COLUMN created_time TIMESTAMP;

SELECT customer_name, created_time
FROM customers;

# `RENAME COLUMN`

An existing column can be renamed. The column with the new name must not exist in the table when performing the alter operation. If the column was previously part of an index, such index will continue working with the new column name. Renaming a column does not change column's type.

ALTER TABLE customers
RENAME COLUMN created_time TO created_at;

SELECT customer_name, created_at
FROM customers;

# Indexes

immudb indexes can be used for a quick search of rows with columns having specific values.

Certain operations such as ordering values with ORDER BY clause require columns to be indexed.

CREATE INDEX ON customers(customer_name);
CREATE INDEX ON customers(country, ip);
CREATE INDEX IF NOT EXISTS ON customers(active);
CREATE UNIQUE INDEX ON customers(email);

Index can only be added to an empty table.

Index do not have explicit name and is referenced by the ordered list of indexed columns.

# Column value constraints

Columns of BLOB or VARCHAR type must have a size limit set on them. The maximum allowed value size for one indexed column is 256 bytes.

# Unique indexes

Index can be marked as unique with extra UNIQUE keyword. Unique index will prevent insertion of new data into the table that would violate uniqueness of indexed columns within the table.

# Multi-column indexes

Index can be set on up to 8 columns. The order of columns is important when doing range scans, iterating over such index will first sort by the value of the first column, then by the second and so on.

Note: Large indexes will increase the storage requirement and will reduce the performance of data insertion. Iterating using small indexes will also be faster than with the large ones.

# `IF NOT EXISTS`

With this clause the CREATE INDEX statement will not fail if an index with same type and list of columns already exists. This includes a use case where the table is not empty which can be used to simplify database schema initialization.

Note: If the index already exists, it is not compared against the provided index definition neither it is updated to match it.

# Catalog queries

immudb provides a set of useful built-in functions that can be used to query the catalog.

# Listing databases

The DATABASES() function can be used as a source of data returning the list of databases that can be accessed by the user running the query.

SELECT * FROM DATABASES();

This source can also be constrained using the WHERE clause and the set of columns to retrieve.

SELECT name FROM DATABASES() WHERE name LIKE '.*db1.*';

# Listing tables

The TABLES() function can be used as a source of data returning the list of tables in the currently selected database.

SELECT * FROM TABLES();

This source can also be constrained using the WHERE clause and the set of columns to retrieve.

SELECT name FROM TABLES()
WHERE name like '.*est.*'

# Listing columns of a table

The COLUMNS() function returns the list of columns for a table. It takes a single argument which is the name of the table. The table will be looked up in the currently selected database.

SELECT * FROM COLUMNS('mytable');

This source can also be constrained with the WHERE clause and set of columns to retrieve.

Note: because colum names can use reserved identifiers such as table, make sure to enclose those in double-quotes.

SELECT "table", "name", "type" FROM COLUMNS('mytable');

SELECT name FROM COLUMNS('mytable') WHERE type = 'VARCHAR';

# Inserting or updating data

# `INSERT`

immudb supports standard INSERT sql statement. It can be used to add one or multiple values within the same transaction.

INSERT INTO customers (
    id, customer_name, email, address,
    city, ip, country, age, active, created_at
)
VALUES (
    1,
    'Isidro Behnen',
    'ibehnen0@mail.ru',
    'ibehnen0@chronoengine.com',
    'Arvika',
    '127.0.0.15',
    'SE',
    24,
    true,
    NOW()
);

INSERT INTO products (id, product, price, created_at)
VALUES
    ( 1, 'Juice - V8, Tomato', '$4.04', NOW() ),
    ( 2, 'Milk', '$3.24', NOW() );

INSERT INTO orders (customerid, productid, created_at)
VALUES (1, 1, NOW()), (1, 2, NOW());

INSERT INTO customer_review (customerid, productid, review, created_at)
VALUES
    (1, 1, 'Nice Juice!', NOW());

# `UPSERT`

UPSERT is an operation with a syntax similar to INSERT, the difference between those two is that UPSERT either creates a new or replaces an existing row. A new row is created if an entry with the same primary key does not yet exist in the table, otherwise the current row is replaced with the new one.

If a table contains an AUTO_INCREMENT primary key, the value for that key must be provided and the UPSERT operation will only update the existing row.

UPSERT INTO products (id, product, price)
VALUES
( 2, 'Milk', '$3.17' ),
( 3, 'Grapes - Red', '$5.03' );

UPSERT INTO orders (id, customerid, productid)
VALUES (1, 1, 3);

# `ON CONFLICT`

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or constraint error. ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action. In this case the primary key of the row is returned.

# Timestamp, NOW() and CAST() built-in function

The built-in NOW() function returns the current timestamp value as seen on the server.

The CAST function can be used to convert a string or an integer to a timestamp value.

The integer value is interpreted as a Unix timestamp (number of seconds since the epoch time).

The string value passed to the CAST function must be in one of the following formats: 2021-12-08, 2021-12-08 17:21, 2021-12-08 17:21:59, 2021-12-08 17:21:59.342516. Time components not specified in the string are set to 0.

UPSERT INTO products (id, product, price, created_at)
VALUES
( 3, 'Bread', '$1.50', NOW() ),
( 4, 'Spinach', '$0.99', CAST('2021-02-01' AS TIMESTAMP) )

SELECT * FROM products WHERE created_at < NOW()

# Querying

# Selecting all columns

All columns from all joined tables can be queried with SELECT * statement.

SELECT *
FROM products;

# Selecting specific columns

SELECT id, customer_name, ip
FROM customers;

# Filtering entries

SELECT id, customer_name, email
FROM customers
WHERE country = 'SE' AND city = 'Arvika';

# Ordering by column value

SELECT id, customer_name
FROM customers
ORDER BY customer_name ASC;

Currently only one column can be used in the ORDER BY clause.

The order may be either ascending (ASC suffix, default) or descending (DESC suffix).

Ordering rows by a value of a column requires a matching index on that column.

# `INNER JOIN`

immudb supports standard SQL INNER JOIN syntax. The INNER join type is optional.

SELECT *
FROM orders
INNER JOIN customers ON orders.customerid = customers.id;

SELECT *
FROM orders
JOIN customers ON orders.customerid = customers.id
WHERE orders.productid = 2;

SELECT * FROM orders
JOIN customers ON customers.id = orders.customerid
JOIN products ON products.id = orders.productid;

# `LIKE` operator

immudb supports the LIKE operator. Unlike in other SQL engines though, the pattern use a regexp syntax supported by the regexp library in the go language (opens new window).

A NOT prefix negates the value of the LIKE operator.

SELECT product
FROM products
WHERE product LIKE '(J.*ce|Red)';

SELECT product
FROM products
WHERE product NOT LIKE '(J.*ce|Red)';

SELECT id, product
FROM products
WHERE (id > 0 AND NOT products.id >= 10)
  AND (products.product LIKE 'J');

# `IN` operator

immudb has a basic supports for the IN operator.

A NOT prefix negates the value of the IN operator.

Note: Currently the list for the IN operator can not be calculated using a sub-query.

SELECT product
FROM products
WHERE product IN ('Milk', 'Grapes - Red');

SELECT product
FROM products
WHERE product NOT IN ('Milk', 'Grapes - Red');

SELECT id, product
FROM products
WHERE (id > 0 AND NOT products.id >= 10)
  AND (product IN ('Milk', 'Grapes - Red'));

# Column and table aliasing

SELECT c.id, c.customer_name AS name, active
FROM customers AS c
WHERE c.id <= 3 AND c.active = true;

SELECT c.id, c.customer_name AS name, active
FROM customers c
WHERE c.id <= 3 AND c.active = true;

Table name aliasing is necessary when using more than one join with the same table.

# Aggregations

Available aggregation functions:

COUNT
SUM
MAX
MIN
AVG

SELECT
    COUNT(*) AS c,
    SUM(age),
    MIN(age),
    MAX(age),
    AVG(age)
FROM customers;

# Grouping results with `GROUP BY`

Results can be grouped by a value of a single column. That column must also be used in a matching ORDER BY clause.

SELECT COUNT(*) as customer_count, country
FROM customers
GROUP BY country
ORDER BY country;

# Filtering grouped results with `HAVING`

SELECT
    active,
    COUNT(*) as c,
    MIN(age),
    MAX(age)
FROM customers
GROUP BY active
HAVING COUNT(*) > 0
ORDER BY active DESC;

# Sub-queries

The table in the SELECT or JOIN clauses can be replaced with a sub-query.

SELECT * FROM (
    SELECT id, customer_name
    FROM customers
    WHERE age < 30
)
INNER JOIN customer_review
    ON customer_review.customerid = customers.id;

SELECT * FROM (
    SELECT id, customer_name
    FROM customers
    WHERE age < 30
) AS c
INNER JOIN (
    SELECT * FROM customer_review
) AS r
    ON r.customerid = c.id;

Note: the context of a sub-query does not propagate outside, e.g. it is not possible to reference a table from a sub-query in the WHERE clause outside of the sub-query.

# Combining query results with `UNION`

It is possible to combine multiple query results with the UNION operator.

Subqueries must select the same number and type of columns. The final return will assign the same naming as in the first subquery, even if names differ.

SELECT customer_name as name
FROM customers
WHERE age < 30
UNION
SELECT seller_name
FROM sellers
WHERE age < 30

Subqueries are not constrained in any way, they can contain aggregations or joins.

Duplicate rows are excluded by default. Using UNION ALL will leave duplicate rows in place.

SELECT AVG(age) FROM customers
UNION ALL 
SELECT AVG(age) FROM sellers

# Transactions

The ACID (Atomicity, Consistency, Isolation, and Durability) compliance is complete.

Handling read-write conflicts may be necessary when dealing with concurrent transactions. Getting the error ErrTxReadConflict ("tx read conflict") means there was another transaction committed before the current one, and the data it read may have been invalidated. MVCC (opens new window) validations have not yet been implemented, therefore there may be false positives generated. In case of conflict, a new attempt may be required.

BEGIN TRANSACTION;
    UPSERT INTO products (id, price, product)
    VALUES (4, '$5.76', 'Bread');

    INSERT INTO orders(productid, customerid)
    VALUES(4, 1);
COMMIT;

# Time travel

Time travel allows you to read data from SQL as if it were in a previous state or from a specific time range. Initial and final points are optional and can be specified using either a transaction ID or a timestamp.

The temporal range can be used to filter out rows from the specified (physical) table, but it is not supported in subqueries.

The initial point can be inclusive (SINCE) or exclusive (AFTER). The final point can be inclusive (UNTIL) or exclusive (BEFORE).

SELECT id, product, price
FROM products BEFORE TX 13
WHERE id = 2;

SELECT * FROM sales SINCE '2022-01-06 11:38' UNTIL '2022-01-06 12:00'

Temporal ranges can be specified using functions and parameters

SELECT * FROM mytable SINCE TX @initialTx BEFORE now()

# SQL Reference

# Data types

# Size constraints

# NULL values

# Timestamp values

# Creating tables

# IF NOT EXISTS

# NOT NULL

# PRIMARY KEY

# AUTO_INCREMENT

# Foreign keys

# Altering tables

# ADD COLUMN

# RENAME COLUMN

# Indexes

# Column value constraints

# Unique indexes

# Multi-column indexes

# IF NOT EXISTS

# Catalog queries

# Listing databases

# Listing tables

# Listing columns of a table

# Inserting or updating data

# INSERT

# UPSERT

# ON CONFLICT

# Timestamp, NOW() and CAST() built-in function

# Querying

# Selecting all columns

# Selecting specific columns

# Filtering entries

# Ordering by column value

# INNER JOIN

# LIKE operator

# IN operator

# Column and table aliasing

# Aggregations

# Grouping results with GROUP BY

# Filtering grouped results with HAVING

# Sub-queries

# Combining query results with UNION

# Transactions

# Time travel

# `IF NOT EXISTS`

# `NOT NULL`

# `PRIMARY KEY`

# `AUTO_INCREMENT`

# `ADD COLUMN`

# `RENAME COLUMN`

# `IF NOT EXISTS`

# `INSERT`

# `UPSERT`

# `ON CONFLICT`

# `INNER JOIN`

# `LIKE` operator

# `IN` operator

# Grouping results with `GROUP BY`

# Filtering grouped results with `HAVING`

# Combining query results with `UNION`