SQL features

SQL features

Explore core SQL features in YSQL

YugabyteDB's YSQL API reuses a fork of the query layer of PostgreSQL as its starting point and runs on top of YugabyteDB's distributed storage layer called DocDB. This architecture allows YSQL to support most PostgreSQL features, such as data types, queries, expressions, operators and functions, stored procedures, triggers, extensions, and so on, all of which are expected to work identically on both database systems.


A large portion of the documentation and examples written for PostgreSQL would work against YSQL.

The following diagram shows how YugabyteDB reuses the PostgreSQL query layer, specifically the components that receive the query (postman), the query parser, rewriter, and analyzer, as well as components responsible for planning and executing the query. Some of these components have been modified to perform efficiently in a distributed SQL database.

Reusing the PostgreSQL query layer in YSQL

SQL features in YSQL

The following table lists the most important YSQL features which you would find familiar if you have worked with PostgreSQL.

YSQL Feature Description
Schemas and Tables SQL shell with ysqlsh, users, databases, tables, and schemas
Data Types String, numeric, temporal types, SERIAL pseudo type, ENUM, arrays, composite types
DDL Statements Data definition language
Queries and Joins Queries, joins, FROM, GROUP BY, HAVING clauses, common table expressions, recursive queries
Join strategies Inner join, outer join, full outer join, cross join, natural join.
Expressions and Operators Basic operators and boolean, numeric, date expressions
Indexes Support for indexes and constraints
Stored Procedures Support for stored procedures
Extensions Support for PostgreSQL extensions

Advanced features in YSQL

The following table lists the advanced features in YSQL.

YSQL Feature Description
Cursors Declaration of cursors in YSQL
Table Partitioning List, range, and hash partitioning of tables
Views Views and updatable views
Savepoints Savepoints in YSQL
Collations Collations in YSQL
Foreign data wrappers Foreign data wrappers in YSQL
Triggers Triggers (on data modification) and event triggers (on schema changes)