SQL reference

Note

The SQL interface of the FoundationDB Record Layer is under active development and, along with this reference, is expected to change frequently as the SQL engine develops.

The FoundationDB Record Layer includes a Relational Layer, which provides a SQL interface for interacting with record stores. It includes a full query engine with sophisticated query planning. We generally recommend using this interface over the direct-access API whenever possible, as the query planner is more capable and automatically handles decisions such as selecting appropriate indexes.

The query language closely follows standard SQL, although the Relational Layer makes some deliberate changes to accommodate the nature of its data model. Most notably, database schemas are instantiated based on schema templates and, in practice, make heavy use of indexes as well as arrays and user-defined struct data types.

• Concepts
  • Databases and schemas
  • Tables
  • Indexes
  • Subqueries
  • Joins
  • Unnesting
  • Bitmaps
• Data definition
  • CREATE DATABASE
  • CREATE FUNCTION
  • CREATE INDEX
  • CREATE SCHEMA
  • CREATE SCHEMA TEMPLATE
  • CREATE STORED QUERY
  • CREATE TABLE
  • CREATE TEMPORARY FUNCTION
  • CREATE TYPE AS ENUM
  • CREATE TYPE AS STRUCT
  • CREATE VIEW
  • DROP DATABASE
  • DROP SCHEMA
  • DROP SCHEMA TEMPLATE
  • DROP TEMPORARY FUNCTION
• Data manipulation
  • COPY
  • DELETE
  • INSERT
  • UPDATE
• Query language
  • EXPLAIN
  • WITH
  • SELECT
  • JOIN
  • WHERE
  • GROUP BY
  • ORDER BY
  • Window Functions
• Types
  • Primitive types
  • Struct types
  • Array types
  • Vector types
• Expressions
  • Function calls
  • Arithmetic operators
  • Comparison operators
  • Logical operators
  • Other expressions
  • Detailed operator reference
• Functions
  • BITMAP_BIT_POSITION
  • BITMAP_BUCKET_NUMBER
  • BITMAP_BUCKET_OFFSET
  • CARDINALITY
  • COALESCE
  • GET_VERSIONSTAMP_INCARNATION
  • GREATEST
  • LEAST
• Aggregates
  • AVG
  • BITMAP_CONSTRUCT_AGG
  • COUNT
  • MAX
  • MIN
  • SUM