SQL Feature Checklist

To understand which ANSI SQL and other SQL features SQreamDB supports, use the tables below.

Data Types and Values 

Data Types and Values
Item	Supported	Further information
`BOOL`	Yes	Boolean values
`TINTINT`	Yes	Unsigned 1 byte integer (0 - 255)
`SMALLINT`	Yes	2 byte integer (-32,768 - 32,767)
`INT`	Yes	4 byte integer (-2,147,483,648 - 2,147,483,647)
`BIGINT`	Yes	8 byte integer (-9,223,372,036,854,775,808 - 9,223,372,036,854,775,807)
`REAL`	Yes	4 byte floating point
`DOUBLE`, `FLOAT`	Yes	8 byte floating point
`DECIMAL`, `NUMERIC`	Yes	Fixed-point numbers.
`TEXT`	Yes	Variable length string - UTF-8 encoded
`DATE`	Yes	Date
`DATETIME`, `TIMESTAMP`	Yes	Date and time
`NULL`	Yes	`NULL` values
`TIME`	No	Can be stored as a text string or as part of a `DATETIME`

SQreamDB treats each statement as an auto-commit transaction. Each transaction is isolated from other transactions with serializable isolation.

If a statement fails, the entire transaction is canceled and rolled back. The database is unchanged.

SQreamDB has a range-index collected on all columns as part of the metadata collection process.

SQreamDB does not support explicit indexing, but does support clustering keys.

Schema Changes
Item	Supported	Further information
`ALTER TABLE`	Yes	ALTER TABLE - Add column, alter column, drop column, rename column, rename table, modify clustering keys
Rename database	No
Rename table	Yes	RENAME TABLE
Rename column	Yes	RENAME COLUMN
Add column	Yes	ADD COLUMN
Remove column	Yes	DROP COLUMN
Alter column data type	No
Add / modify clustering keys	Yes	CLUSTER BY
Drop clustering keys	Yes	DROP CLUSTERING KEY
Add / Remove constraints	No
Rename schema	Yes	RENAME SCHEMA
Drop schema	Yes	DROP SCHEMA
Alter default schema per user	Yes	ALTER DEFAULT SCHEMA

Statements
Item	Supported	Further information
SELECT	Yes	SELECT
CREATE TABLE	Yes	CREATE TABLE
CREATE FOREIGN / EXTERNAL TABLE	Yes	CREATE FOREIGN TABLE
DELETE	Yes	Deleting Data
INSERT	Yes	INSERT, COPY FROM
TRUNCATE	Yes	TRUNCATE
UPDATE	Yes
VALUES	Yes	VALUES

Table Expressions
Item	Supported	Further information
Tables, Views	Yes
Aliases, `AS`	Yes
`JOIN` - `INNER`, `LEFT [ OUTER ]`, `RIGHT [ OUTER ]`, `CROSS`	Yes
Table expression subqueries	Yes
Scalar subqueries	No

Read more about Access Control in SQreamDB.

Permissions
Item	Supported	Further information
Roles as users and groups	Yes
Object default permissions	Yes
Column / Row based permissions	No
Object ownership	No

Extra Functionality
Item	Supported	Further information
Information schema	Yes	Catalog Reference
Views	Yes	CREATE VIEW
Window functions	Yes	Window Functions
CTEs	Yes	Common Table Expressions
Saved queries, Saved queries with parameters	Yes	Saved Queries
Sequences	Yes	Identity

Item	Supported	Further information
Common functions	Yes	`CURRENT_TIMESTAMP`, `SUBSTRING`, `TRIM`, `EXTRACT`, etc.
Comparison operators	Yes	`<`, `<=`, `>`, `>=`, `=`, `<>, !=`, `IS`, `IS NOT`
Boolean operators	Yes	`AND`, `NOT`, `OR`
Conditional expressions	Yes	`CASE .. WHEN`
Conditional functions	Yes	`COALESCE`
Pattern matching	Yes	`LIKE`, `RLIKE`, `ISPREFIXOF`, `CHARINDEX`, `PATINDEX`
REGEX POSIX pattern matching	Yes	`RLIKE`, `REGEXP_COUNT`, `REGEXP_INSTR`, `REGEXP_SUBSTR`,
`EXISTS`	No
`IN`, `NOT IN`	Partial	Literal values only
Bitwise arithmetic	Yes	`&`, `\|`, `XOR`, `~`, `>>`, `<<`