SQL Feature Checklist¶

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

In this topic:

Data Types and Values ¶

SQream DB 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 cancelled and rolled back. The database is unchanged.

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

SQream DB does not support explicit indexing, but does support clustering keys.

Read more about clustering keys and our metadata system.

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

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

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

Read more about Access Control in SQream DB.

Permissions¶
Item	Supported	Further information
Roles as users and groups	✓
Object default permissions	✓
Column / Row based permissions	✗
Object ownership	✗

Extra Functionality¶
Item	Supported	Further information
Information schema	✓	Catalog reference
Views	✓	CREATE VIEW
Window functions	✓	Window Functions
CTEs	✓	Common table expressions (CTEs)
Saved queries, Saved queries with parameters	✓	Saved Queries
Sequences	✓	Identity

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

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