SQL feature support

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

Data types

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

Schema operations

	Operation	Documentation
{{<icon/partial>}}	Altering tables	ALTER TABLE
{{<icon/yes>}}	Altering databases	ALTER DATABASE
{{<icon/yes>}}	Altering a column's name
{{<icon/yes>}}	Altering a column's default value
{{<icon/partial>}}	Altering a column's data type
{{<icon/yes>}}	Adding columns	ADD COLUMN
{{<icon/yes>}}	Removing columns	DROP COLUMN
{{<icon/yes>}}	Adding constraints	ADD CONSTRAINT
{{<icon/yes>}}	Removing constraints	DROP CONSTRAINT
{{<icon/no>}}	Altering indexes
{{<icon/yes>}}	Adding indexes	CREATE INDEX
{{<icon/yes>}}	Removing indexes
{{<icon/yes>}}	Adding a primary key
{{<icon/yes>}}	Dropping a primary key
{{<icon/no>}}	Altering a primary key
{{<icon/yes>}}	Adding user-defined schemas	CREATE SCHEMA
{{<icon/no>}}	Removing user-defined schemas
{{<icon/no>}}	Altering user-defined schemas
{.sno-1}

Constraints

	Feature	Documentation
{{<icon/yes>}}	Check	Check constraint
{{<icon/yes>}}	Unique	Unique constraint
{{<icon/yes>}}	Not Null	Not Null constraint
{{<icon/yes>}}	Primary Key	Primary keys
{{<icon/yes>}}	Foreign Key	Foreign keys
{{<icon/partial>}}	Default Value
{{<icon/partial>}}	Deferrable Foreign Key constraints
{{<icon/no>}}	Deferrable Primary Key and Unique constraints
{{<icon/no>}}	Exclusion constraints
{.sno-1}

Indexes

	Component	Documentation
{{<icon/yes>}}	Indexes	Indexes and constraints
{{<icon/yes>}}	GIN indexes	GIN indexes
{{<icon/yes>}}	Partial indexes	Partial indexes
{{<icon/yes>}}	Expression indexes	Expression indexes
{{<icon/yes>}}	Multi-column indexes	Multi-column indexes
{{<icon/yes>}}	Covering indexes	Covering indexes
{{<icon/no>}}	GiST indexes
{{<icon/no>}}	BRIN indexes
{{<icon/yes>}}	B-tree indexes	B-tree index is treated as an LSM index.
{.sno-1}

Transactions

	Feature	Documentation
{{<icon/yes>}}	Transactions	Transactions
{{<icon/yes>}}	BEGIN	BEGIN
{{<icon/yes>}}	COMMIT	COMMIT
{{<icon/yes>}}	ROLLBACK	ROLLBACK
{{<icon/yes>}}	SAVEPOINT	SAVEPOINT
{{<icon/yes>}}	ROLLBACK TO SAVEPOINT	ROLLBACK TO SAVEPOINT
{{<icon/no>}}	PREPARE TRANSACTION (XA)
{.sno-1}

Roles and Permissions

	Component	Details
{{<icon/yes>}}	Users	Manage users and roles
{{<icon/yes>}}	Roles	Manage users and roles
{{<icon/yes>}}	Object ownership
{{<icon/yes>}}	Privileges	Grant privileges
{{<icon/yes>}}	Default privileges
{{<icon/yes>}}	Row level security
{{<icon/yes>}}	Column level security
{.sno-1}

Queries

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

Advanced SQL

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