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

Constraints

Constraints

Item

Supported

Further information

Not null

Yes

NOT NULL

Default values

Yes

DEFAULT

AUTO INCREMENT

Yes (different name)

IDENTITY

Transactions

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.

Indexes

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.

Read more about clustering keys.

Schema Changes

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

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

Clauses

Clauses

Item

Supported

Further information

LIMIT / TOP

Yes

LIMIT with OFFSET

No

WHERE

Yes

HAVING

Yes

OVER

Yes

Table Expressions

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

Scalar Expressions

Read more about Scalar expressions.

Scalar Expressions

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, ~, >>, <<

Permissions

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

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