Amazon QLDB PartiQL reference

Important

End of support notice: Existing customers will be able to use Amazon QLDB until end of support on 07/31/2025. For more details, see Migrate an Amazon QLDB Ledger to Amazon Aurora PostgreSQL.

Amazon QLDB supports a subset of the PartiQL query language. The following topics describe the QLDB implementation of PartiQL.

Note

• QLDB does not support all PartiQL operations.

• All PartiQL statements in QLDB are subject to transaction limits, as defined in Quotas and limits in Amazon QLDB.

• This reference provides basic syntax and usage examples of PartiQL statements that you manually run on the QLDB console or the QLDB shell. For code examples that show how to programmatically run similar statements using the QLDB driver, see the tutorials in Getting started with the driver.

Topics

• What is PartiQL?
• PartiQL in Amazon QLDB
• PartiQL quick tips in QLDB
• PartiQL reference conventions
• Data types
• QLDB documents
• Querying Ion with PartiQL
• PartiQL commands
• PartiQL functions
• PartiQL stored procedures
• PartiQL operators
• Reserved keywords
• Amazon Ion reference

What is PartiQL?

PartiQL provides SQL-compatible query access across multiple data stores containing structured data, semistructured data, and nested data. It's widely used within Amazon and is now available as part of many AWS services, including QLDB.

For the PartiQL specification and a tutorial on the core query language, see the PartiQL documentation.

PartiQL extends SQL-92 to support documents in the Amazon Ion data format. For information about Amazon Ion, see the Amazon Ion data format reference in Amazon QLDB.

PartiQL in Amazon QLDB

To run PartiQL queries in QLDB, you can use one of the following:

• The PartiQL editor on the AWS Management Console for QLDB

• The command line QLDB shell

• An AWS provided QLDB driver to run queries programmatically

For information about using these methods to access QLDB, see Accessing Amazon QLDB.

To learn how to control access to run each PartiQL command on specific tables, see Getting started with the standard permissions mode in Amazon QLDB.

PartiQL quick tips in QLDB

The following is a short summary of tips and best practices for working with PartiQL in QLDB:

• Understand concurrency and transaction limits – All statements, including SELECT queries, are subject to optimistic concurrency control (OCC) conflicts and transaction limits, including a 30-second transaction timeout.

• Use indexes – Use high-cardinality indexes and run targeted queries to optimize your statements and avoid full table scans. To learn more, see Optimizing query performance.

• Use equality predicates – Indexed lookups require an equality operator (= or IN). Inequality operators (<, >, LIKE, BETWEEN) don't qualify for indexed lookups and result in full table scans.

• Use inner joins only – QLDB supports inner joins only. As a best practice, join on fields that are indexed for each table that you're joining. Choose high-cardinality indexes for both the join criteria and the equality predicates.