Yugabyte Structured Query Language (YSQL)
Attention
This page documents an earlier version. Go to the latest (v2.3) version.Introduction
Yugabyte Structured Query Language (YSQL) is an ANSI SQL, fully-relational API that is best fit for scale-out RDBMS applications needing ultra resilience, massive write scalability and geographic data distribution. Currently, YSQL is compatible with PostgreSQL 11.2 version and is in fact built by reusing PostgreSQL's native query layer. It supports all traditional relational modeling features, such as referential integrity (such as foreign keys), JOINs, distributed transactions, partial indexes, triggers and stored procedures.
The main components of YSQL include the data definition language (DDL), the data manipulation language (DML), and the data control language (DCL). A number of elements are used to construct these components, including data types, database objects, names and qualifiers, expressions, and comments. Other components are also provided for different purposes such as system control, transaction control, and performance tuning.
Quick Start
You can explore the basics of the YSQL API using the Quick Start steps.
Data definition language (DDL)
DDL statements define the structures in a database, change their definitions, as well as remove them by using CREATE, ALTER, and DROP commands respectively.
| Statement | Description |
|---|---|
ALTER DATABASE |
Change database definition |
ALTER SEQUENCE |
Change sequence definition |
ALTER TABLE |
Change table definition |
CREATE AGGREGATE |
Create a new aggregate |
CREATE CAST |
Create a new cast |
CREATE DATABASE |
Create a new database |
CREATE EXTENSION |
Load an extension |
CREATE FUNCTION |
Create a new function |
CREATE INDEX |
Create a new index |
CREATE OPERATOR |
Create a new operator |
CREATE OPERATOR CLASS |
Create a new operator class |
CREATE PROCEDURE |
Create a new procedure |
CREATE RULE |
Create a new rule |
CREATE SCHEMA |
Create a new schema (namespace) |
CREATE SEQUENCE |
Create a new sequence generator |
CREATE TABLE |
Create a new table |
CREATE TABLE AS |
Create a new table |
CREATE TRIGGER |
Create a new trigger |
CREATE TYPE |
Create a new type |
CREATE VIEW |
Create a new view |
DROP AGGREGATE |
Delete an aggregate |
DROP CAST |
Delete a cast |
DROP DATABASE |
Delete a database from the system |
DROP EXTENSION |
Delete an extension |
DROP FUNCTION |
Delete a function |
DROP OPERATOR |
Delete an operator |
DROP OPERATOR CLASS |
Delete an operator class |
DROP PROCEDURE |
Delete a procedure |
DROP RULE |
Delete a rule |
DROP SEQUENCE |
Delete a sequence generator |
DROP TABLE |
Delete a table from a database |
DROP TYPE |
Delete a user-defined type |
DROP TRIGGER |
Delete a trigger |
TRUNCATE |
Clear all rows from a table |
Data manipulation language (DML)
DML statements modify the contents of a database.
| Statement | Description |
|---|---|
DELETE |
Delete rows from a table |
INSERT |
Insert rows into a table |
SELECT |
Select rows from a table |
UPDATE |
Update rows in a table |
Data control language (DCL)
DCL statements protect and prevent the database from corruptions.
| Statement | Description |
|---|---|
ALTER DEFAULT PRIVILEGES |
Define default privileges |
ALTER GROUP |
Alter a group |
ALTER POLICY |
Alter a row level security policy |
ALTER ROLE |
Alter a role (user or group) |
ALTER USER |
Alter a user |
CREATE GROUP |
Create a new group (role) |
CREATE POLICY |
Create a new row level security policy |
CREATE ROLE |
Create a new role (user or group) |
CREATE USER |
Create a new user (role) |
DROP GROUP |
Drop a group |
DROP POLICY |
Drop a row level security policy |
DROP ROLE |
Drop a role (user or group) |
DROP OWNED |
Drop owned objects |
DROP USER |
Drop a user |
GRANT |
Grant permissions |
REASSIGN OWNED |
Reassign owned objects |
REVOKE |
Revoke permissions |
SET ROLE |
Set a role |
SET SESSION AUTHORIZATION |
Set session authorization |
Transaction control language (TCL)
TCL statements manage transactions of operations on the database.
| Statement | Description |
|---|---|
ABORT |
Roll back a transaction |
BEGIN |
Start a transaction |
COMMIT |
Commit a transaction |
END |
Commit a transaction |
ROLLBACK |
Roll back a transaction |
SET CONSTRAINTS |
Set constraints on current transaction |
SET TRANSACTION |
Set transaction behaviors |
SHOW TRANSACTION |
Show properties of a transaction |
Session and system control
| Statement | Description |
|---|---|
RESET |
Reset a parameter to factory settings |
SET |
Set a system, session, or transactional parameter |
SHOW |
Show value of a system, session, or transactional parameter |
Performance control
| Statement | Description |
|---|---|
DEALLOCATE |
Deallocate a prepared statement |
EXECUTE |
Execute a prepared statement |
EXPLAIN |
Explain an execution plan for a statement |
PREPARE |
Prepare a statement |
Other statements
| Statement | Description |
|---|---|
COPY |
Copy data between tables and files |
DO |
Execute an anonymous code block |
Language elements
This section lists the main elements of YSQL.
- Keywords.
- Names and Qualifiers: Some names are reserved for the system. List of reserved names.
- Data types: Most PostgreSQL-compatible data types are supported. List of data types.
- Expressions
- Database Objects
- Comments