SQL feature support

YugabyteDB supports most standard SQL features.

This page highlights the important differences in feature support between YSQL and SQL.

Data types

Data type Documentation
ARRAY Array data types
BINARY Binary data types
BIT,BYTES
BOOLEAN Boolean data types
CHAR, VARCHAR, TEXT Character data types
COLLATE Collations
DATE, TIME, TIMESTAMP, INTERVAL Date and time data types
DEC, DECIMAL, NUMERIC Fixed point numbers
ENUM Enumerations
FLOAT, REAL, DOUBLE PRECISION Floating-point numbers
JSON, JSONB JSON data types
MONEY Money data types
SERIAL, SMALLSERIAL, BIGSERIAL Serial data types
SMALLINT, INT, INTEGER, BIGINT Integers
INT4RANGE, INT8RANGE, NUMRANGE, TSRANGE, TSTZRANGE, DATERANGE Range data types
UUID UUID data type
XML
TSVECTOR
UDT(Base, Enumerated, Range, Composite, Array, Domain types)

Schema operations

Operation Documentation
Altering tables ALTER TABLE
Altering databases ALTER DATABASE
Altering a column's name
Altering a column's default value
Altering a column's data type
Adding columns ADD COLUMN
Removing columns DROP COLUMN
Adding constraints ADD CONSTRAINT
Removing constraints DROP CONSTRAINT
Altering indexes
Adding indexes CREATE INDEX
Removing indexes
Adding a primary key
Dropping a primary key
Altering a primary key
Adding user-defined schemas CREATE SCHEMA
Removing user-defined schemas
Altering user-defined schemas

Constraints

Feature Documentation
Check Check constraint
Unique Unique constraint
Not Null Not Null constraint
Primary Key Primary keys
Foreign Key Foreign keys
Default Value
Deferrable Foreign Key constraints
Deferrable Primary Key and Unique constraints
Exclusion constraints

Indexes

Component Documentation
Indexes Indexes and constraints
GIN indexes GIN indexes
Partial indexes Partial indexes
Expression indexes Expression indexes
Multi-column indexes Multi-column indexes
Covering indexes Covering indexes
GiST indexes
BRIN indexes
B-tree indexes B-tree index is treated as an LSM index.

Transactions

Feature Documentation
Transactions Transactions
BEGIN BEGIN
COMMIT COMMIT
ROLLBACK ROLLBACK
SAVEPOINT SAVEPOINT
ROLLBACK TO SAVEPOINT ROLLBACK TO SAVEPOINT
PREPARE TRANSACTION (XA)

Roles and Permissions

Component Details
Users Manage users and roles
Roles Manage users and roles
Object ownership
Privileges Grant privileges
Default privileges
Row level security
Column level security

Queries

Component Details
FROM, WHERE, GROUP BY, HAVING, DISTINCT, LIMIT/OFFSET, WITH queries Group data
EXPLAIN query plans Analyze queries with EXPLAIN
JOINs (INNER/OUTER, LEFT/RIGHT) Join columns
Expressions and Operators Expressions and operators
Common Table Expressions (CTE) and Recursive Queries Recursive queries and CTEs
Upserts (INSERT ... ON CONFLICT DO NOTHING/UPDATE) Upsert

Advanced SQL

Component Details
Stored procedures Stored procedures
User-defined functions Functions
Cursors Cursors
Row-level triggers (BEFORE, AFTER, INSTEAD OF)
Statement-level triggers (BEFORE, AFTER, INSTEAD OF)
Deferrable triggers
Transition tables (REFERENCING clause for triggers)
Sequences Auto-Increment column values
Identity columns
Views Views
Materialized views Materialized views
Window functions Window functions
Common table expressions
Extensions PostgreSQL extensions
Foreign data wrappers Foreign data wrappers