4. The SQL Language

This part describes the use of the SQL language in PostgreSQL. We start with describing the general syntax of SQL, then explain how to create the structures to hold data, how to populate the database, and how to query it. The middle part lists the available data types and functions for use in SQL commands. The rest treats several aspects that are important for tuning a database for optimal performance.

The information in this part is arranged so that a novice user can follow it start to end to gain a full understanding of the topics without having to refer forward too many times. The chapters are intended to be self-contained, so that advanced users can read the chapters individually as they choose. The information in this part is presented in a narrative fashion in topical units. Readers looking for a complete description of a particular command should see reference.

Readers of this part should know how to connect to a PostgreSQL database and issue SQL commands. Readers that are unfamiliar with these issues are encouraged to read tutorial first. SQL commands are typically entered using the PostgreSQL interactive terminal psql, but other programs that have similar functionality can be used as well.

• 4.1. SQL Syntax
  • 4.1.1. Lexical Structure
    • 4.1.1.1. Identifiers and Key Words
    • 4.1.1.2. Constants
    • 4.1.1.3. Operators
    • 4.1.1.4. Special Characters
    • 4.1.1.5. Comments
    • 4.1.1.6. Operator Precedence
  • 4.1.2. Value Expressions
    • 4.1.2.1. Column References
    • 4.1.2.2. Positional Parameters
    • 4.1.2.3. Subscripts
    • 4.1.2.4. Field Selection
    • 4.1.2.5. Operator Invocations
    • 4.1.2.6. Function Calls
    • 4.1.2.7. Aggregate Expressions
    • 4.1.2.8. Window Function Calls
    • 4.1.2.9. Type Casts
    • 4.1.2.10. Collation Expressions
    • 4.1.2.11. Scalar Subqueries
    • 4.1.2.12. Array Constructors
    • 4.1.2.13. Row Constructors
    • 4.1.2.14. Expression Evaluation Rules
  • 4.1.3. Calling Functions
    • 4.1.3.1. Using Positional Notation
    • 4.1.3.2. Using Named Notation
    • 4.1.3.3. Using Mixed Notation
• 4.2. Data Definition
  • 4.2.1. Table Basics
  • 4.2.2. Default Values
  • 4.2.3. Generated Columns
  • 4.2.4. Constraints
    • 4.2.4.1. Check Constraints
    • 4.2.4.2. Not-Null Constraints
    • 4.2.4.3. Unique Constraints
    • 4.2.4.4. Primary Keys
    • 4.2.4.5. Foreign Keys
    • 4.2.4.6. Exclusion Constraints
  • 4.2.5. System Columns
  • 4.2.6. Modifying Tables
    • 4.2.6.1. Adding a Column
    • 4.2.6.2. Removing a Column
    • 4.2.6.3. Adding a Constraint
    • 4.2.6.4. Removing a Constraint
    • 4.2.6.5. Changing a Column’s Default Value
    • 4.2.6.6. Changing a Column’s Data Type
    • 4.2.6.7. Renaming a Column
    • 4.2.6.8. Renaming a Table
  • 4.2.7. Privileges
  • 4.2.8. Row Security Policies
  • 4.2.9. Schemas
    • 4.2.9.1. Creating a Schema
    • 4.2.9.2. The Public Schema
    • 4.2.9.3. The Schema Search Path
    • 4.2.9.4. Schemas and Privileges
    • 4.2.9.5. The System Catalog Schema
    • 4.2.9.6. Usage Patterns
    • 4.2.9.7. Portability
  • 4.2.10. Inheritance
    • 4.2.10.1. Caveats
  • 4.2.11. Table Partitioning
    • 4.2.11.1. Overview
    • 4.2.11.2. Declarative Partitioning
    • 4.2.11.3. Partitioning Using Inheritance
    • 4.2.11.4. Partition Pruning
    • 4.2.11.5. Partitioning and Constraint Exclusion
    • 4.2.11.6. Best Practices for Declarative Partitioning
  • 4.2.12. Foreign Data
  • 4.2.13. Other Database Objects
  • 4.2.14. Dependency Tracking
• 4.3. Data Manipulation
  • 4.3.1. Inserting Data
  • 4.3.2. Updating Data
  • 4.3.3. Deleting Data
  • 4.3.4. Returning Data from Modified Rows
• 4.4. Queries
  • 4.4.1. Overview
  • 4.4.2. Table Expressions
    • 4.4.2.1. The FROM Clause
    • 4.4.2.2. The WHERE Clause
    • 4.4.2.3. The GROUP BY and HAVING Clauses
    • 4.4.2.4. GROUPING SETS, CUBE, and ROLLUP
    • 4.4.2.5. Window Function Processing
  • 4.4.3. Select Lists
    • 4.4.3.1. Select-List Items
    • 4.4.3.2. Column Labels
    • 4.4.3.3. DISTINCT
  • 4.4.4. Combining Queries (UNION, INTERSECT, EXCEPT)
  • 4.4.5. Sorting Rows (ORDER BY)
  • 4.4.6. LIMIT and OFFSET
  • 4.4.7. VALUES Lists
  • 4.4.8. WITH Queries (Common Table Expressions)
    • 4.4.8.1. SELECT in WITH
    • 4.4.8.2. Recursive Queries
    • 4.4.8.3. Common Table Expression Materialization
    • 4.4.8.4. Data-Modifying Statements in WITH
• 4.5. Data Types
  • 4.5.1. Numeric Types
    • 4.5.1.1. Integer Types
    • 4.5.1.2. Arbitrary Precision Numbers
    • 4.5.1.3. Floating-Point Types
    • 4.5.1.4. Serial Types
  • 4.5.2. Monetary Types
  • 4.5.3. Character Types
  • 4.5.4. Binary Data Types
    • 4.5.4.1. bytea Hex Format
    • 4.5.4.2. bytea Escape Format
  • 4.5.5. Date/Time Types
    • 4.5.5.1. Date/Time Input
    • 4.5.5.2. Date/Time Output
    • 4.5.5.3. Time Zones
    • 4.5.5.4. Interval Input
    • 4.5.5.5. Interval Output
  • 4.5.6. Boolean Type
  • 4.5.7. Enumerated Types
    • 4.5.7.1. Declaration of Enumerated Types
    • 4.5.7.2. Ordering
    • 4.5.7.3. Type Safety
    • 4.5.7.4. Implementation Details
  • 4.5.8. Geometric Types
    • 4.5.8.1. Points
    • 4.5.8.2. Lines
    • 4.5.8.3. Line Segments
    • 4.5.8.4. Boxes
    • 4.5.8.5. Paths
    • 4.5.8.6. Polygons
    • 4.5.8.7. Circles
  • 4.5.9. Network Address Types
    • 4.5.9.1. inet
    • 4.5.9.2. cidr
    • 4.5.9.3. inet vs. cidr
    • 4.5.9.4. macaddr
    • 4.5.9.5. macaddr8
  • 4.5.10. Bit String Types
  • 4.5.11. Text Search Types
    • 4.5.11.1. tsvector
    • 4.5.11.2. tsquery
  • 4.5.12. UUID Type
  • 4.5.13. XML Type
    • 4.5.13.1. Creating XML Values
    • 4.5.13.2. Encoding Handling
    • 4.5.13.3. Accessing XML Values
  • 4.5.14. Domain Types
  • 4.5.15. Object Identifier Types
  • 4.5.16. pg_lsn Type
  • 4.5.17. Pseudo-Types
• 4.6. Functions and Operators
  • 4.6.1. Logical Operators
  • 4.6.2. Comparison Functions and Operators
  • 4.6.3. Mathematical Functions and Operators
  • 4.6.4. String Functions and Operators
    • 4.6.4.1. format
  • 4.6.5. Binary String Functions and Operators
  • 4.6.6. Bit String Functions and Operators
  • 4.6.7. Pattern Matching
    • 4.6.7.1. LIKE
    • 4.6.7.2. SIMILAR TO Regular Expressions
    • 4.6.7.3. POSIX Regular Expressions
  • 4.6.8. Data Type Formatting Functions
  • 4.6.9. Date/Time Functions and Operators
    • 4.6.9.1. EXTRACT, date_part
    • 4.6.9.2. date_trunc
    • 4.6.9.3. date_bin
    • 4.6.9.4. AT TIME ZONE
    • 4.6.9.5. Current Date/Time
    • 4.6.9.6. Delaying Execution
  • 4.6.10. Enum Support Functions
  • 4.6.11. Geometric Functions and Operators
  • 4.6.12. Network Address Functions and Operators
  • 4.6.13. Text Search Functions and Operators
  • 4.6.14. UUID Functions
  • 4.6.15. XML Functions
    • 4.6.15.1. Producing XML Content
    • 4.6.15.2. XML Predicates
    • 4.6.15.3. Processing XML
    • 4.6.15.4. Mapping Tables to XML
  • 4.6.16. JSON Functions and Operators
    • 4.6.16.1. Processing and Creating JSON Data
    • 4.6.16.2. The SQL/JSON Path Language
  • 4.6.17. Sequence Manipulation Functions
  • 4.6.18. Conditional Expressions
    • 4.6.18.1. CASE
    • 4.6.18.2. COALESCE
    • 4.6.18.3. NULLIF
    • 4.6.18.4. GREATEST and LEAST
  • 4.6.19. Array Functions and Operators
  • 4.6.20. Range/Multirange Functions and Operators
  • 4.6.21. Aggregate Functions
  • 4.6.22. Window Functions
  • 4.6.23. Subquery Expressions
    • 4.6.23.1. EXISTS
    • 4.6.23.2. IN
    • 4.6.23.3. NOT IN
    • 4.6.23.4. ANY/SOME
    • 4.6.23.5. ALL
    • 4.6.23.6. Single-Row Comparison
  • 4.6.24. Row and Array Comparisons
    • 4.6.24.1. IN
    • 4.6.24.2. NOT IN
    • 4.6.24.3. ANY/SOME (array)
    • 4.6.24.4. ALL (array)
    • 4.6.24.5. Row Constructor Comparison
    • 4.6.24.6. Composite Type Comparison
  • 4.6.25. Set Returning Functions
  • 4.6.26. System Information Functions and Operators
  • 4.6.27. System Administration Functions
    • 4.6.27.1. Configuration Settings Functions
    • 4.6.27.2. Server Signaling Functions
    • 4.6.27.3. Backup Control Functions
    • 4.6.27.4. Recovery Control Functions
    • 4.6.27.5. Snapshot Synchronization Functions
    • 4.6.27.6. Replication Management Functions
    • 4.6.27.7. Database Object Management Functions
    • 4.6.27.8. Index Maintenance Functions
    • 4.6.27.9. Generic File Access Functions
    • 4.6.27.10. Advisory Lock Functions
  • 4.6.28. Trigger Functions
  • 4.6.29. Event Trigger Functions
    • 4.6.29.1. Capturing Changes at Command End
    • 4.6.29.2. Processing Objects Dropped by a DDL Command
    • 4.6.29.3. Handling a Table Rewrite Event
  • 4.6.30. Statistics Information Functions
    • 4.6.30.1. Inspecting MCV Lists
• 4.7. Type Conversion
  • 4.7.1. Overview
  • 4.7.2. Operators
    • 4.7.2.1. Square Root Operator Type Resolution
    • 4.7.2.2. String Concatenation Operator Type Resolution
    • 4.7.2.3. Absolute-Value and Negation Operator Type Resolution
    • 4.7.2.4. Array Inclusion Operator Type Resolution
    • 4.7.2.5. Custom Operator on a Domain Type
  • 4.7.3. Functions
    • 4.7.3.1. Rounding Function Argument Type Resolution
    • 4.7.3.2. Variadic Function Resolution
    • 4.7.3.3. Substring Function Type Resolution
  • 4.7.4. Value Storage
    • 4.7.4.1. character Storage Type Conversion
  • 4.7.5. UNION, CASE, and Related Constructs
    • 4.7.5.1. Type Resolution with Underspecified Types in a Union
    • 4.7.5.2. Type Resolution in a Simple Union
    • 4.7.5.3. Type Resolution in a Transposed Union
    • 4.7.5.4. Type Resolution in a Nested Union
  • 4.7.6. SELECT Output Columns
• 4.8. Indexes
  • 4.8.1. Introduction
  • 4.8.2. Index Types
    • 4.8.2.1. B-Tree
    • 4.8.2.2. Hash
    • 4.8.2.3. GiST
    • 4.8.2.4. SP-GiST
    • 4.8.2.5. GIN
    • 4.8.2.6. BRIN
  • 4.8.3. Multicolumn Indexes
  • 4.8.4. Indexes and ORDER BY
  • 4.8.5. Combining Multiple Indexes
  • 4.8.6. Unique Indexes
  • 4.8.7. Indexes on Expressions
  • 4.8.8. Partial Indexes
  • 4.8.9. Index-Only Scans and Covering Indexes
  • 4.8.10. Operator Classes and Operator Families
  • 4.8.11. Indexes and Collations
  • 4.8.12. Examining Index Usage
• 4.9. Full Text Search
  • 4.9.1. Introduction
    • 4.9.1.1. What Is a Document?
    • 4.9.1.2. Basic Text Matching
    • 4.9.1.3. Configurations
  • 4.9.2. Tables and Indexes
    • 4.9.2.1. Searching a Table
    • 4.9.2.2. Creating Indexes
  • 4.9.3. Controlling Text Search
    • 4.9.3.1. Parsing Documents
    • 4.9.3.2. Parsing Queries
    • 4.9.3.3. Ranking Search Results
    • 4.9.3.4. Highlighting Results
  • 4.9.4. Additional Features
    • 4.9.4.1. Manipulating Documents
    • 4.9.4.2. Manipulating Queries
    • 4.9.4.3. Triggers for Automatic Updates
    • 4.9.4.4. Gathering Document Statistics
  • 4.9.5. Parsers
  • 4.9.6. Dictionaries
    • 4.9.6.1. Stop Words
    • 4.9.6.2. Simple Dictionary
    • 4.9.6.3. Synonym Dictionary
    • 4.9.6.4. Thesaurus Dictionary
    • 4.9.6.5. Ispell Dictionary
    • 4.9.6.6. Snowball Dictionary
  • 4.9.7. Configuration Example
  • 4.9.8. Testing and Debugging Text Search
    • 4.9.8.1. Configuration Testing
    • 4.9.8.2. Parser Testing
    • 4.9.8.3. Dictionary Testing
  • 4.9.9. Preferred Index Types for Text Search
  • 4.9.10. psql Support
  • 4.9.11. Limitations
• 4.10. Concurrency Control
  • 4.10.1. Introduction
  • 4.10.2. Transaction Isolation
    • 4.10.2.1. Read Committed Isolation Level
    • 4.10.2.2. Repeatable Read Isolation Level
    • 4.10.2.3. Serializable Isolation Level
  • 4.10.3. Explicit Locking
    • 4.10.3.1. Table-Level Locks
    • 4.10.3.2. Row-Level Locks
    • 4.10.3.3. Page-Level Locks
    • 4.10.3.4. Deadlocks
    • 4.10.3.5. Advisory Locks
  • 4.10.4. Data Consistency Checks at the Application Level
    • 4.10.4.1. Enforcing Consistency with Serializable Transactions
    • 4.10.4.2. Enforcing Consistency with Explicit Blocking Locks
  • 4.10.5. Serialization Failure Handling
  • 4.10.6. Caveats
  • 4.10.7. Locking and Indexes
• 4.11. Performance Tips
  • 4.11.1. Using EXPLAIN
    • 4.11.1.1. EXPLAIN Basics
    • 4.11.1.2. EXPLAIN ANALYZE
    • 4.11.1.3. Caveats
  • 4.11.2. Statistics Used by the Planner
    • 4.11.2.1. Single-Column Statistics
    • 4.11.2.2. Extended Statistics
  • 4.11.3. Controlling the Planner with Explicit JOIN Clauses
  • 4.11.4. Populating a Database
    • 4.11.4.1. Disable Autocommit
    • 4.11.4.2. Use COPY
    • 4.11.4.3. Remove Indexes
    • 4.11.4.4. Remove Foreign Key Constraints
    • 4.11.4.5. Increase maintenance_work_mem
    • 4.11.4.6. Increase max_wal_size
    • 4.11.4.7. Disable WAL Archival and Streaming Replication
    • 4.11.4.8. Run ANALYZE Afterwards
    • 4.11.4.9. Some Notes about pg_dump
  • 4.11.5. Non-Durable Settings
• 4.12. Parallel Query
  • 4.12.1. How Parallel Query Works
  • 4.12.2. When Can Parallel Query Be Used?
  • 4.12.3. Parallel Plans
    • 4.12.3.1. Parallel Scans
    • 4.12.3.2. Parallel Joins
    • 4.12.3.3. Parallel Aggregation
    • 4.12.3.4. Parallel Append
    • 4.12.3.5. Parallel Plan Tips
  • 4.12.4. Parallel Safety
    • 4.12.4.1. Parallel Labeling for Functions and Aggregates