PostgreSQL 18 Documentation
  • Home
  • Preface
    • What Is PostgreSQL?
    • A Brief History of PostgreSQL
    • Conventions
    • Further Information
    • Bug Reporting Guidelines
  • I. Tutorial
    • 1. Getting Started
      • 1.1 Installation
      • 1.2 Architectural Fundamentals
      • 1.3 Creating a Database
      • 1.4 Accessing a Database
    • 2. The SQL Language
      • 2.1 Introduction
      • 2.2 Concepts
      • 2.3 Creating a New Table
      • 2.4 Populating a Table With Rows
      • 2.5 Querying a Table
      • 2.6 Joins Between Tables
      • 2.7 Aggregate Functions
      • 2.8 Updates
      • 2.9 Deletions
    • 3. Advanced Features
      • 3.1 Introduction
      • 3.2 Views
      • 3.3 Foreign Keys
      • 3.4 Transactions
      • 3.5 Window Functions
      • 3.6 Inheritance
      • 3.7 Conclusion
  • II. The SQL Language
    • 4. SQL Syntax
      • 4.1. Lexical Structure
      • 4.2. Value Expressions
      • 4.3. Calling Functions
    • 5. Data Definition
      • 5.1. Table Basics
      • 5.2. Default Values
      • 5.3. Identity Columns
      • 5.4. Generated Columns
      • 5.5. Constraints
      • 5.6. System Columns
      • 5.7. Modifying Tables
      • 5.8. Privileges
      • 5.9. Row Security Policies
      • 5.10. Schemas
      • 5.11. Inheritance
      • 5.12. Table Partitioning
      • 5.13. Foreign Data
      • 5.14. Other Database Objects
      • 5.15. Dependency Tracking
    • 6. Data Manipulation
      • 6.1. Inserting Data
      • 6.2. Updating Data
      • 6.3. Deleting Data
      • 6.4. Returning Data from Modified Rows
    • 7. Queries
      • 7.1. Overview
      • 7.2. Table Expressions
      • 7.3. Select Lists
      • 7.4. Combining Queries (UNION, INTERSECT, EXCEPT)
      • 7.5. Sorting Rows (ORDER BY)
      • 7.6. LIMIT and OFFSET
      • 7.7. VALUES Lists
      • 7.8. WITH Queries (Common Table Expressions)
    • 8. Data Types
      • 8.1. Numeric Types
      • 8.2. Monetary Types
      • 8.3. Character Types
      • 8.4. Binary Data Types
      • 8.5. Date/Time Types
      • 8.6. Boolean Type
      • 8.7. Enumerated Types
      • 8.8. Geometric Types
      • 8.9. Network Address Types
      • 8.10. Bit String Types
      • 8.11. Text Search Types
      • 8.12. UUID Type
      • 8.13. XML Type
      • 8.14. JSON Types
      • 8.15. Arrays
      • 8.16. Composite Types
      • 8.17. Range Types
      • 8.18. Domain Types
      • 8.19. Object Identifier Types
      • 8.20. pg_lsn Type
      • 8.21. Pseudo-Types
    • 9. Functions and Operators
      • 9.1. Logical Operators
      • 9.2. Comparison Functions and Operators
      • 9.3. Mathematical Functions and Operators
      • 9.4. String Functions and Operators
      • 9.5. Binary String Functions and Operators
      • 9.6. Bit String Functions and Operators
      • 9.7. Pattern Matching
      • 9.8. Data Type Formatting Functions
      • 9.9. Date/Time Functions and Operators
      • 9.10. Enum Support Functions
      • 9.11. Geometric Functions and Operators
      • 9.12. Network Address Functions and Operators
      • 9.13. Text Search Functions and Operators
      • 9.14. UUID Functions
      • 9.15. XML Functions
      • 9.16. JSON Functions and Operators
      • 9.17. Sequence Manipulation Functions
      • 9.18. Conditional Expressions
      • 9.19. Array Functions and Operators
      • 9.20. Range/Multirange Functions and Operators
      • 9.21. Aggregate Functions
      • 9.22. Window Functions
      • 9.23. Merge Support Functions
      • 9.24. Subquery Expressions
      • 9.25. Row and Array Comparisons
      • 9.26. Set Returning Functions
      • 9.27. System Information Functions and Operators
      • 9.28. System Administration Functions
      • 9.29. Trigger Functions
      • 9.30. Event Trigger Functions
      • 9.31. Statistics Information Functions
    • 10. Type Conversion
      • 10.1. Overview
      • 10.2. Operators
      • 10.3. Functions
      • 10.4. Value Storage
      • 10.5. UNION, CASE, and Related Constructs
      • 10.6. SELECT Output Columns
    • 11. Indexes
      • 11.1. Introduction
      • 11.2. Index Types
      • 11.3. Multicolumn Indexes
      • 11.4. Indexes and ORDER BY
      • 11.5. Combining Multiple Indexes
      • 11.6. Unique Indexes
      • 11.7. Indexes on Expressions
      • 11.8. Partial Indexes
      • 11.9. Index-Only Scans and Covering Indexes
      • 11.10. Operator Classes and Operator Families
      • 11.11. Indexes and Collations
      • 11.12. Examining Index Usage
    • 12. Full Text Search
      • 12.1. Introduction
      • 12.2. Tables and Indexes
      • 12.3. Controlling Text Search
      • 12.4. Additional Features
      • 12.5. Parsers
      • 12.6. Dictionaries
      • 12.7. Configuration Example
      • 12.8. Testing and Debugging Text Search
      • 12.9. Preferred Index Types for Text Search
      • 12.10. psql Support
      • 12.11. Limitations
    • 13. Concurrency Control
      • 13.1. Introduction
      • 13.2. Transaction Isolation
      • 13.3. Explicit Locking
      • 13.4. Data Consistency Checks at the Application Level
      • 13.5. Serialization Failure Handling
      • 13.6. Caveats
      • 13.7. Locking and Indexes
    • 14. Performance Tips
      • 14.1. Using EXPLAIN
      • 14.2. Statistics Used by the Planner
      • 14.3. Controlling the Planner with Explicit JOIN Clauses
      • 14.4. Populating a Database
      • 14.5. Non-Durable Settings
    • 15. Parallel Query
      • 15.1. How Parallel Query Works
      • 15.2. When Can Parallel Query Be Used?
      • 15.3. Parallel Plans
      • 15.4. Parallel Safety
  • III. Server Administration
    • 16. Installation from Binaries
    • 17. Installation from Source Code
      • 17.1. Requirements
      • 17.2. Getting the Source
      • 17.3. Building and Installation with Autoconf and Make
      • 17.4. Building and Installation with Meson
      • 17.5. Post-Installation Setup
      • 17.6. Supported Platforms
      • 17.7. Platform-Specific Notes
    • 18. Server Setup and Operation
      • 18.1. The PostgreSQL User Account
      • 18.2. Creating a Database Cluster
      • 18.3. Starting the Database Server
      • 18.4. Managing Kernel Resources
      • 18.5. Shutting Down the Server
      • 18.6. Upgrading a PostgreSQL Cluster
      • 18.7. Preventing Server Spoofing
      • 18.8. Encryption Options
      • 18.9. Secure TCP/IP Connections with SSL
      • 18.10. Secure TCP/IP Connections with GSSAPI Encryption
      • 18.11. Secure TCP/IP Connections with SSH Tunnels
      • 18.12. Registering Event Log on Windows
    • 19. Server Configuration
      • 19.1. Setting Parameters
      • 19.2. File Locations
      • 19.3. Connections and Authentication
      • 19.4. Resource Consumption
      • 19.5. Write Ahead Log
      • 19.6. Replication
      • 19.7. Query Planning
      • 19.8. Error Reporting and Logging
      • 19.9. Run-time Statistics
      • 19.10. Vacuuming
      • 19.11. Client Connection Defaults
      • 19.12. Lock Management
      • 19.13. Version and Platform Compatibility
      • 19.14. Error Handling
      • 19.15. Preset Options
      • 19.16. Customized Options
      • 19.17. Developer Options
      • 19.18. Short Options
    • 20. Client Authentication
      • 20.1. The pg_hba.conf File
      • 20.2. User Name Maps
      • 20.3. Authentication Methods
      • 20.4. Trust Authentication
      • 20.5. Password Authentication
      • 20.6. GSSAPI Authentication
      • 20.7. SSPI Authentication
      • 20.8. Ident Authentication
      • 20.9. Peer Authentication
      • 20.10. LDAP Authentication
      • 20.11. RADIUS Authentication
      • 20.12. Certificate Authentication
      • 20.13. PAM Authentication
      • 20.14. BSD Authentication
      • 20.15. OAuth Authorization/Authentication
      • 20.16. Authentication Problems
    • 21. Database Roles
      • 21.1. Database Roles
      • 21.2. Role Attributes
      • 21.3. Role Membership
      • 21.4. Dropping Roles
      • 21.5. Predefined Roles
      • 21.6. Function Security
    • 22. Managing Databases
      • 22.1. Overview
      • 22.2. Creating a Database
      • 22.3. Template Databases
      • 22.4. Database Configuration
      • 22.5. Destroying a Database
      • 22.6. Tablespaces
    • 23. Localization
      • 23.1. Locale Support
      • 23.2. Collation Support
      • 23.3. Character Set Support
    • 24. Routine Database Maintenance Tasks
      • 24.1. Routine Vacuuming
      • 24.2. Routine Reindexing
      • 24.3. Log File Maintenance
    • 25. Backup and Restore
      • 25.1. SQL Dump
      • 25.2. File System Level Backup
      • 25.3. Continuous Archiving and Point-in-Time Recovery (PITR)
    • 26. High Availability, Load Balancing, and Replication
      • 26.1. Comparison of Different Solutions
      • 26.2. Log-Shipping Standby Servers
      • 26.3. Failover
      • 26.4. Hot Standby
    • 27. Monitoring Database Activity
      • 27.1. Standard Unix Tools
      • 27.2. The Cumulative Statistics System
      • 27.3. Viewing Locks
      • 27.4. Progress Reporting
      • 27.5. Dynamic Tracing
      • 27.6. Monitoring Disk Usage
    • 28. Reliability and the Write-Ahead Log
      • 28.1. Reliability
      • 28.2. Data Checksums
      • 28.3. Write-Ahead Logging (WAL)
      • 28.4. Asynchronous Commit
      • 28.5. WAL Configuration
      • 28.6. WAL Internals
    • 29. Logical Replication
      • 29.1. Publication
      • 29.2. Subscription
      • 29.3. Logical Replication Failover
      • 29.4. Row Filters
      • 29.5. Column Lists
      • 29.6. Generated Column Replication
      • 29.7. Conflicts
      • 29.8. Restrictions
      • 29.9. Architecture
      • 29.10. Monitoring
      • 29.11. Security
      • 29.12. Configuration Settings
      • 29.13. Upgrade
      • 29.14. Quick Setup
    • 30. Just-in-Time Compilation (JIT)
      • 30.1. What Is JIT compilation?
      • 30.2. When to JIT?
      • 30.3. Configuration
      • 30.4. Extensibility
    • 31. Regression Tests
      • 31.1. Running the Tests
      • 31.2. Test Evaluation
      • 31.3. Variant Comparison Files
      • 31.4. TAP Tests
      • 31.5. Test Coverage Examination
  • IV. Client Interfaces
    • 32. libpq — C Library
      • 32.1. Database Connection Control Functions
      • 32.2. Connection Status Functions
      • 32.3. Command Execution Functions
      • 32.4. Asynchronous Command Processing
      • 32.5. Pipeline Mode
      • 32.6. Retrieving Query Results in Chunks
      • 32.7. Canceling Queries in Progress
      • 32.8. The Fast-Path Interface
      • 32.9. Asynchronous Notification
      • 32.10. Functions Associated with the COPY Command
      • 32.11. Control Functions
      • 32.12. Miscellaneous Functions
      • 32.13. Notice Processing
      • 32.14. Event System
      • 32.15. Environment Variables
      • 32.16. The Password File
      • 32.17. The Connection Service File
      • 32.18. LDAP Lookup of Connection Parameters
      • 32.19. SSL Support
      • 32.20. OAuth Support
      • 32.21. Behavior in Threaded Programs
      • 32.22. Building libpq Programs
      • 32.23. Example Programs
    • 33. Large Objects
      • 33.1. Introduction
      • 33.2. Implementation Features
      • 33.3. Client Interfaces
      • 33.4. Server-Side Functions
      • 33.5. Example Program
    • 34. ECPG — Embedded SQL in C
      • 34.1. The Concept
      • 34.2. Managing Database Connections
      • 34.3. Running SQL Commands
      • 34.4. Using Host Variables
      • 34.5. Dynamic SQL
      • 34.6. pgtypes Library
      • 34.7. Using Descriptor Areas
      • 34.8. Error Handling
      • 34.9. Preprocessor Directives
      • 34.10. Processing Embedded SQL Programs
      • 34.11. Library Functions
      • 34.12. Large Objects
      • 34.13. C++ Applications
      • 34.14. Embedded SQL Commands
      • 34.15. Informix Compatibility Mode
      • 34.16. Oracle Compatibility Mode
      • 34.17. Internals
    • 35. The Information Schema
      • 35.1. The Schema
      • 35.2. Data Types
      • 35.3. information_schema_catalog_name
      • 35.4. administrable_role_​authorizations
      • 35.5. applicable_roles
      • 35.6. attributes
      • 35.7. character_sets
      • 35.8. check_constraint_routine_usage
      • 35.9. check_constraints
      • 35.10. collations
      • 35.11. collation_character_set_​applicability
      • 35.12. column_column_usage
      • 35.13. column_domain_usage
      • 35.14. column_options
      • 35.15. column_privileges
      • 35.16. column_udt_usage
      • 35.17. columns
      • 35.18. constraint_column_usage
      • 35.19. constraint_table_usage
      • 35.20. data_type_privileges
      • 35.21. domain_constraints
      • 35.22. domain_udt_usage
      • 35.23. domains
      • 35.24. element_types
      • 35.25. enabled_roles
      • 35.26. foreign_data_wrapper_options
      • 35.27. foreign_data_wrappers
      • 35.28. foreign_server_options
      • 35.29. foreign_servers
      • 35.30. foreign_table_options
      • 35.31. foreign_tables
      • 35.32. key_column_usage
      • 35.33. parameters
      • 35.34. referential_constraints
      • 35.35. role_column_grants
      • 35.36. role_routine_grants
      • 35.37. role_table_grants
      • 35.38. role_udt_grants
      • 35.39. role_usage_grants
      • 35.40. routine_column_usage
      • 35.41. routine_privileges
      • 35.42. routine_routine_usage
      • 35.43. routine_sequence_usage
      • 35.44. routine_table_usage
      • 35.45. routines
      • 35.46. schemata
      • 35.47. sequences
      • 35.48. sql_features
      • 35.49. sql_implementation_info
      • 35.50. sql_parts
      • 35.51. sql_sizing
      • 35.52. table_constraints
      • 35.53. table_privileges
      • 35.54. tables
      • 35.55. transforms
      • 35.56. triggered_update_columns
      • 35.57. triggers
      • 35.58. udt_privileges
      • 35.59. usage_privileges
      • 35.60. user_defined_types
      • 35.61. user_mapping_options
      • 35.62. user_mappings
      • 35.63. view_column_usage
      • 35.64. view_routine_usage
      • 35.65. view_table_usage
      • 35.66. views
  • V. Server Programming
    • 36. Extending SQL
      • 36.1. How Extensibility Works
      • 36.2. The PostgreSQL Type System
      • 36.3. User-Defined Functions
      • 36.4. User-Defined Procedures
      • 36.5. Query Language (SQL) Functions
      • 36.6. Function Overloading
      • 36.7. Function Volatility Categories
      • 36.8. Procedural Language Functions
      • 36.9. Internal Functions
      • 36.10. C-Language Functions
      • 36.11. Function Optimization Information
      • 36.12. User-Defined Aggregates
      • 36.13. User-Defined Types
      • 36.14. User-Defined Operators
      • 36.15. Operator Optimization Information
      • 36.16. Interfacing Extensions to Indexes
      • 36.17. Packaging Related Objects into an Extension
      • 36.18. Extension Building Infrastructure
    • 37. Triggers
      • 37.1. Overview of Trigger Behavior
      • 37.2. Visibility of Data Changes
      • 37.3. Writing Trigger Functions in C
      • 37.4. A Complete Trigger Example
    • 38. Event Triggers
      • 38.1. Overview of Event Trigger Behavior
      • 38.2. Writing Event Trigger Functions in C
      • 38.3. A Complete Event Trigger Example
      • 38.4. A Table Rewrite Event Trigger Example
      • 38.5. A Database Login Event Trigger Example
    • 39. The Rule System
      • 39.1. The Query Tree
      • 39.2. Views and the Rule System
      • 39.3. Materialized Views
      • 39.4. Rules on INSERT, UPDATE, and DELETE
      • 39.5. Rules and Privileges
      • 39.6. Rules and Command Status
      • 39.7. Rules Versus Triggers
    • 40. Procedural Languages
    • 41. PL/pgSQL — SQL Procedural Language
      • 41.1. Overview
      • 41.2. Structure of PL/pgSQL
      • 41.3. Declarations
      • 41.4. Expressions
      • 41.5. Basic Statements
      • 41.6. Control Structures
      • 41.7. Cursors
      • 41.8. Transaction Management
      • 41.9. Errors and Messages
      • 41.10. Trigger Functions
      • 41.11. PL/pgSQL under the Hood
      • 41.12. Tips for Developing in PL/pgSQL
      • 41.13. Porting from Oracle PL/SQL
    • 42. PL/Tcl — Tcl Procedural Language
      • 42.1. Overview
      • 42.2. PL/Tcl Functions and Arguments
      • 42.3. Data Values in PL/Tcl
      • 42.4. Global Data in PL/Tcl
      • 42.5. Database Access from PL/Tcl
      • 42.6. Trigger Functions in PL/Tcl
      • 42.7. Event Trigger Functions in PL/Tcl
      • 42.8. Error Handling in PL/Tcl
      • 42.9. Explicit Subtransactions in PL/Tcl
      • 42.10. Transaction Management
      • 42.11. PL/Tcl Configuration
      • 42.12. Tcl Procedure Names
    • 43. PL/Perl — Perl Procedural Language
      • 43.1. PL/Perl Functions and Arguments
      • 43.2. Data Values in PL/Perl
      • 43.3. Built-in Functions
      • 43.4. Global Values in PL/Perl
      • 43.5. Trusted and Untrusted PL/Perl
      • 43.6. PL/Perl Triggers
      • 43.7. PL/Perl Event Triggers
      • 43.8. PL/Perl Under the Hood
    • 44. PL/Python — Python Procedural Language
      • 44.1. PL/Python Functions
      • 44.2. Data Values
      • 44.3. Sharing Data
      • 44.4. Anonymous Code Blocks
      • 44.5. Trigger Functions
      • 44.6. Database Access
      • 44.7. Explicit Subtransactions
      • 44.8. Transaction Management
      • 44.9. Utility Functions
      • 44.10. Python 2 vs. Python 3
      • 44.11. Environment Variables
    • 45. Server Programming Interface
      • 45.1. Interface Functions
      • 45.2. Interface Support Functions
      • 45.3. Memory Management
      • 45.4. Transaction Management
      • 45.5. Visibility of Data Changes
      • 45.6. Examples
    • 46. Background Worker Processes
    • 47. Logical Decoding
      • 47.1. Logical Decoding Examples
      • 47.2. Logical Decoding Concepts
      • 47.3. Streaming Replication Protocol Interface
      • 47.4. Logical Decoding SQL Interface
      • 47.5. System Catalogs Related to Logical Decoding
      • 47.6. Logical Decoding Output Plugins
      • 47.7. Logical Decoding Output Writers
      • 47.8. Synchronous Replication Support for Logical Decoding
      • 47.9. Streaming of Large Transactions for Logical Decoding
      • 47.10. Two-phase Commit Support for Logical Decoding
    • 48. Replication Progress Tracking
    • 49. Archive Modules
    • 50. OAuth Validator Modules
      • 50.1. Safely Designing a Validator Module
      • 50.2. Initialization Functions
      • 50.3. OAuth Validator Callbacks
  • VI. Reference
    • I. SQL Commands
      • ABORT
      • ALTER AGGREGATE
      • ALTER COLLATION
      • ALTER CONVERSION
      • ALTER DATABASE
      • ALTER DEFAULT PRIVILEGES
      • ALTER DOMAIN
      • ALTER EVENT TRIGGER
      • ALTER EXTENSION
      • ALTER FOREIGN DATA WRAPPER
      • ALTER FOREIGN TABLE
      • ALTER FUNCTION
      • ALTER GROUP
      • ALTER INDEX
      • ALTER LANGUAGE
      • ALTER LARGE OBJECT
      • ALTER MATERIALIZED VIEW
      • ALTER OPERATOR
      • ALTER OPERATOR CLASS
      • ALTER OPERATOR FAMILY
      • ALTER POLICY
      • ALTER PROCEDURE
      • ALTER PUBLICATION
      • ALTER ROLE
      • ALTER ROUTINE
      • ALTER RULE
      • ALTER SCHEMA
      • ALTER SEQUENCE
      • ALTER SERVER
      • ALTER STATISTICS
      • ALTER SUBSCRIPTION
      • ALTER SYSTEM
      • ALTER TABLE
      • ALTER TABLESPACE
      • ALTER TEXT SEARCH CONFIGURATION
      • ALTER TEXT SEARCH DICTIONARY
      • ALTER TEXT SEARCH PARSER
      • ALTER TEXT SEARCH TEMPLATE
      • ALTER TRIGGER
      • ALTER TYPE
      • ALTER USER
      • ALTER USER MAPPING
      • ALTER VIEW
      • ANALYZE
      • BEGIN
      • CALL
      • CHECKPOINT
      • CLOSE
      • CLUSTER
      • COMMENT
      • COMMIT
      • COMMIT PREPARED
      • COPY
      • CREATE ACCESS METHOD
      • CREATE AGGREGATE
      • CREATE CAST
      • CREATE COLLATION
      • CREATE CONVERSION
      • CREATE DATABASE
      • CREATE DOMAIN
      • CREATE EVENT TRIGGER
      • CREATE EXTENSION
      • CREATE FOREIGN DATA WRAPPER
      • CREATE FOREIGN TABLE
      • CREATE FUNCTION
      • CREATE GROUP
      • CREATE INDEX
      • CREATE LANGUAGE
      • CREATE MATERIALIZED VIEW
      • CREATE OPERATOR
      • CREATE OPERATOR CLASS
      • CREATE OPERATOR FAMILY
      • CREATE POLICY
      • CREATE PROCEDURE
      • CREATE PUBLICATION
      • CREATE ROLE
      • CREATE RULE
      • CREATE SCHEMA
      • CREATE SEQUENCE
      • CREATE SERVER
      • CREATE STATISTICS
      • CREATE SUBSCRIPTION
      • CREATE TABLE
      • CREATE TABLE AS
      • CREATE TABLESPACE
      • CREATE TEXT SEARCH CONFIGURATION
      • CREATE TEXT SEARCH DICTIONARY
      • CREATE TEXT SEARCH PARSER
      • CREATE TEXT SEARCH TEMPLATE
      • CREATE TRANSFORM
      • CREATE TRIGGER
      • CREATE TYPE
      • CREATE USER
      • CREATE USER MAPPING
      • CREATE VIEW
      • DEALLOCATE
      • DECLARE
      • DELETE
      • DISCARD
      • DO
      • DROP ACCESS METHOD
      • DROP AGGREGATE
      • DROP CAST
      • DROP COLLATION
      • DROP CONVERSION
      • DROP DATABASE
      • DROP DOMAIN
      • DROP EVENT TRIGGER
      • DROP EXTENSION
      • DROP FOREIGN DATA WRAPPER
      • DROP FOREIGN TABLE
      • DROP FUNCTION
      • DROP GROUP
      • DROP INDEX
      • DROP LANGUAGE
      • DROP MATERIALIZED VIEW
      • DROP OPERATOR
      • DROP OPERATOR CLASS
      • DROP OPERATOR FAMILY
      • DROP OWNED
      • DROP POLICY
      • DROP PROCEDURE
      • DROP PUBLICATION
      • DROP ROLE
      • DROP ROUTINE
      • DROP RULE
      • DROP SCHEMA
      • DROP SEQUENCE
      • DROP SERVER
      • DROP STATISTICS
      • DROP SUBSCRIPTION
      • DROP TABLE
      • DROP TABLESPACE
      • DROP TEXT SEARCH CONFIGURATION
      • DROP TEXT SEARCH DICTIONARY
      • DROP TEXT SEARCH PARSER
      • DROP TEXT SEARCH TEMPLATE
      • DROP TRANSFORM
      • DROP TRIGGER
      • DROP TYPE
      • DROP USER
      • DROP USER MAPPING
      • DROP VIEW
      • END
      • EXECUTE
      • EXPLAIN
      • FETCH
      • GRANT
      • IMPORT FOREIGN SCHEMA
      • INSERT
      • LISTEN
      • LOAD
      • LOCK
      • MERGE
      • MOVE
      • NOTIFY
      • PREPARE
      • PREPARE TRANSACTION
      • REASSIGN OWNED
      • REFRESH MATERIALIZED VIEW
      • REINDEX
      • RELEASE SAVEPOINT
      • RESET
      • REVOKE
      • ROLLBACK
      • ROLLBACK PREPARED
      • ROLLBACK TO SAVEPOINT
      • SAVEPOINT
      • SECURITY LABEL
      • SELECT
      • SELECT INTO
      • SET
      • SET CONSTRAINTS
      • SET ROLE
      • SET SESSION AUTHORIZATION
      • SET TRANSACTION
      • SHOW
      • START TRANSACTION
      • TRUNCATE
      • UNLISTEN
      • UPDATE
      • VACUUM
      • VALUES
    • II. PostgreSQL Client Applications
      • clusterdb
      • createdb
      • createuser
      • dropdb
      • dropuser
      • ecpg
      • pg_amcheck
      • pg_basebackup
      • pgbench
      • pg_combinebackup
      • pg_config
      • pg_dump
      • pg_dumpall
      • pg_isready
      • pg_receivewal
      • pg_recvlogical
      • pg_restore
      • pg_verifybackup
      • psql
      • reindexdb
      • vacuumdb
    • III. PostgreSQL Server Applications
      • initdb
      • pg_archivecleanup
      • pg_checksums
      • pg_controldata
      • pg_createsubscriber
      • pg_ctl
      • pg_resetwal
      • pg_rewind
      • pg_test_fsync
      • pg_test_timing
      • pg_upgrade
      • pg_waldump
      • pg_walsummary
      • postgres
  • VII. Internals
    • 51. Overview of PostgreSQL Internals
      • 51.1. The Path of a Query
      • 51.2. How Connections Are Established
      • 51.3. The Parser Stage
      • 51.4. The PostgreSQL Rule System
      • 51.5. Planner/Optimizer
      • 51.6. Executor
    • 52. System Catalogs
      • 52.1. Overview
      • 52.2. pg_aggregate
      • 52.3. pg_am
      • 52.4. pg_amop
      • 52.5. pg_amproc
      • 52.6. pg_attrdef
      • 52.7. pg_attribute
      • 52.8. pg_authid
      • 52.9. pg_auth_members
      • 52.10. pg_cast
      • 52.11. pg_class
      • 52.12. pg_collation
      • 52.13. pg_constraint
      • 52.14. pg_conversion
      • 52.15. pg_database
      • 52.16. pg_db_role_setting
      • 52.17. pg_default_acl
      • 52.18. pg_depend
      • 52.19. pg_description
      • 52.20. pg_enum
      • 52.21. pg_event_trigger
      • 52.22. pg_extension
      • 52.23. pg_foreign_data_wrapper
      • 52.24. pg_foreign_server
      • 52.25. pg_foreign_table
      • 52.26. pg_index
      • 52.27. pg_inherits
      • 52.28. pg_init_privs
      • 52.29. pg_language
      • 52.30. pg_largeobject
      • 52.31. pg_largeobject_metadata
      • 52.32. pg_namespace
      • 52.33. pg_opclass
      • 52.34. pg_operator
      • 52.35. pg_opfamily
      • 52.36. pg_parameter_acl
      • 52.37. pg_partitioned_table
      • 52.38. pg_policy
      • 52.39. pg_proc
      • 52.40. pg_publication
      • 52.41. pg_publication_namespace
      • 52.42. pg_publication_rel
      • 52.43. pg_range
      • 52.44. pg_replication_origin
      • 52.45. pg_rewrite
      • 52.46. pg_seclabel
      • 52.47. pg_sequence
      • 52.48. pg_shdepend
      • 52.49. pg_shdescription
      • 52.50. pg_shseclabel
      • 52.51. pg_statistic
      • 52.52. pg_statistic_ext
      • 52.53. pg_statistic_ext_data
      • 52.54. pg_subscription
      • 52.55. pg_subscription_rel
      • 52.56. pg_tablespace
      • 52.57. pg_transform
      • 52.58. pg_trigger
      • 52.59. pg_ts_config
      • 52.60. pg_ts_config_map
      • 52.61. pg_ts_dict
      • 52.62. pg_ts_parser
      • 52.63. pg_ts_template
      • 52.64. pg_type
      • 52.65. pg_user_mapping
    • 53. System Views
      • 53.1. Overview
      • 53.2. pg_aios
      • 53.3. pg_available_extensions
      • 53.4. pg_available_extension_versions
      • 53.5. pg_backend_memory_contexts
      • 53.6. pg_config
      • 53.7. pg_cursors
      • 53.8. pg_file_settings
      • 53.9. pg_group
      • 53.10. pg_hba_file_rules
      • 53.11. pg_ident_file_mappings
      • 53.12. pg_indexes
      • 53.13. pg_locks
      • 53.14. pg_matviews
      • 53.15. pg_policies
      • 53.16. pg_prepared_statements
      • 53.17. pg_prepared_xacts
      • 53.18. pg_publication_tables
      • 53.19. pg_replication_origin_status
      • 53.20. pg_replication_slots
      • 53.21. pg_roles
      • 53.22. pg_rules
      • 53.23. pg_seclabels
      • 53.24. pg_sequences
      • 53.25. pg_settings
      • 53.26. pg_shadow
      • 53.27. pg_shmem_allocations
      • 53.28. pg_shmem_allocations_numa
      • 53.29. pg_stats
      • 53.30. pg_stats_ext
      • 53.31. pg_stats_ext_exprs
      • 53.32. pg_tables
      • 53.33. pg_timezone_abbrevs
      • 53.34. pg_timezone_names
      • 53.35. pg_user
      • 53.36. pg_user_mappings
      • 53.37. pg_views
      • 53.38. pg_wait_events
    • 54. Frontend/Backend Protocol
      • 54.1. Overview
      • 54.2. Message Flow
      • 54.3. SASL Authentication
      • 54.4. Streaming Replication Protocol
      • 54.5. Logical Streaming Replication Protocol
      • 54.6. Message Data Types
      • 54.7. Message Formats
      • 54.8. Error and Notice Message Fields
      • 54.9. Logical Replication Message Formats
      • 54.10. Summary of Changes since Protocol 2.0
    • 55. PostgreSQL Coding Conventions
      • 55.1. Formatting
      • 55.2. Reporting Errors Within the Server
      • 55.3. Error Message Style Guide
      • 55.4. Miscellaneous Coding Conventions
    • 56. Native Language Support
      • 56.1. For the Translator
      • 56.2. For the Programmer
    • 57. Writing a Procedural Language Handler
    • 58. Writing a Foreign Data Wrapper
      • 58.1. Foreign Data Wrapper Functions
      • 58.2. Foreign Data Wrapper Callback Routines
      • 58.3. Foreign Data Wrapper Helper Functions
      • 58.4. Foreign Data Wrapper Query Planning
      • 58.5. Row Locking in Foreign Data Wrappers
    • 59. Writing a Table Sampling Method
      • 59.1. Sampling Method Support Functions
    • 60. Writing a Custom Scan Provider
      • 60.1. Creating Custom Scan Paths
      • 60.2. Creating Custom Scan Plans
      • 60.3. Executing Custom Scans
    • 61. Genetic Query Optimizer
    • 62. Table Access Method Interface Definition
    • 63. Index Access Method Interface Definition
      • 63.1. Basic API Structure for Indexes
      • 63.2. Index Access Method Functions
      • 63.3. Index Scanning
      • 63.4. Index Locking Considerations
      • 63.5. Index Uniqueness Checks
      • 63.6. Index Cost Estimation Functions
    • 64. Write Ahead Logging for Extensions
      • 64.1. Generic WAL Records
      • 64.2. Custom WAL Resource Managers
    • 65. Built-in Index Access Methods
      • 65.1. B-Tree Indexes
      • 65.2. GiST Indexes
      • 65.3. SP-GiST Indexes
      • 65.4. GIN Indexes
      • 65.5. BRIN Indexes
      • 65.6. Hash Indexes
    • 66. Database Physical Storage
      • 66.1. Database File Layout
      • 66.2. TOAST
      • 66.3. Free Space Map
      • 66.4. Visibility Map
      • 66.5. The Initialization Fork
      • 66.6. Database Page Layout
      • 66.7. Heap-Only Tuples (HOT)
    • 67. Transaction Processing
    • 68. System Catalog Declarations and Initial Contents
      • 68.1. System Catalog Declaration Rules
      • 68.2. System Catalog Initial Data
      • 68.3. BKI File Format
      • 68.4. BKI Commands
      • 68.5. Structure of the Bootstrap BKI File
      • 68.6. BKI Example
    • 69. How the Planner Uses Statistics
      • 69.1. Row Estimation Examples
      • 69.2. Multivariate Statistics Examples
      • 69.3. Planner Statistics and Security
    • 70. Backup Manifest Format
  • VIII. Appendixes
    • A. PostgreSQL Error Codes
    • B. Date/Time Support
      • B.1. Date/Time Input Interpretation
      • B.2. Handling of Invalid or Ambiguous Timestamps
      • B.3. Date/Time Key Words
      • B.4. Date/Time Configuration Files
      • B.5. POSIX Time Zone Specifications
      • B.6. History of Units
      • B.7. Julian Dates
    • C. SQL Key Words
    • D. SQL Conformance
      • D.1. Supported Features
      • D.2. Unsupported Features
      • D.3. XML Limits and Conformance to SQL/XML
    • E. Release Notes
      • E.1. Release 18.3
      • E.2. Release 18.2
      • E.3. Release 18.1
      • E.4. Release 18
      • E.5. Prior Releases
    • F. Additional Supplied Modules and Extensions
      • F.1. amcheck — tools to verify table and index consistency
      • F.2. auth_delay — pause on authentication failure
      • F.3. auto_explain — log execution plans of slow queries
      • F.4. basebackup_to_shell — example "shell" pg_basebackup module
      • F.5. basic_archive — an example WAL archive module
      • F.6. bloom — bloom filter index access method
      • F.7. btree_gin — GIN operator classes with B-tree behavior
      • F.8. btree_gist — GiST operator classes with B-tree behavior
      • F.9. citext — a case-insensitive character string type
      • F.10. cube — a multi-dimensional cube data type
      • F.11. dblink — connect to other PostgreSQL databases
      • F.12. dict_int — example full-text search dictionary for integers
      • F.13. dict_xsyn — example synonym full-text search dictionary
      • F.14. earthdistance — calculate great-circle distances
      • F.15. file_fdw — access data files in the server's file system
      • F.16. fuzzystrmatch — determine string similarities and distance
      • F.17. hstore — hstore key/value datatype
      • F.18. intagg — integer aggregator and enumerator
      • F.19. intarray — manipulate arrays of integers
      • F.20. isn — data types for international standard numbers (ISBN, EAN, UPC, etc.)
      • F.21. lo — manage large objects
      • F.22. ltree — hierarchical tree-like data type
      • F.23. pageinspect — low-level inspection of database pages
      • F.24. passwordcheck — verify password strength
      • F.25. pg_buffercache — inspect PostgreSQL buffer cache state
      • F.26. pgcrypto — cryptographic functions
      • F.27. pg_freespacemap — examine the free space map
      • F.28. pg_logicalinspect — logical decoding components inspection
      • F.29. pg_overexplain — allow EXPLAIN to dump even more details
      • F.30. pg_prewarm — preload relation data into buffer caches
      • F.31. pgrowlocks — show a table's row locking information
      • F.32. pg_stat_statements — track statistics of SQL planning and execution
      • F.33. pgstattuple — obtain tuple-level statistics
      • F.34. pg_surgery — perform low-level surgery on relation data
      • F.35. pg_trgm — support for similarity of text using trigram matching
      • F.36. pg_visibility — visibility map information and utilities
      • F.37. pg_walinspect — low-level WAL inspection
      • F.38. postgres_fdw — access data stored in external PostgreSQL servers
      • F.39. seg — a datatype for line segments or floating point intervals
      • F.40. sepgsql — SELinux-, label-based mandatory access control (MAC) security module
      • F.41. spi — Server Programming Interface features/examples
      • F.42. sslinfo — obtain client SSL information
      • F.43. tablefunc — functions that return tables (crosstab and others)
      • F.44. tcn — a trigger function to notify listeners of changes to table content
      • F.45. test_decoding — SQL-based test/example module for WAL logical decoding
      • F.46. tsm_system_rows — the SYSTEM_ROWS sampling method for TABLESAMPLE
      • F.47. tsm_system_time — the SYSTEM_TIME sampling method for TABLESAMPLE
      • F.48. unaccent — a text search dictionary which removes diacritics
      • F.49. uuid-ossp — a UUID generator
      • F.50. xml2 — XPath querying and XSLT functionality
    • G. Additional Supplied Programs
      • G.1. Client Applications
      • G.2. Server Applications
    • H. External Projects
    • I. The Source Code Repository
    • J. Documentation
      • J.1. DocBook
      • J.2. Tool Sets
      • J.3. Building the Documentation with Make
      • J.4. Building the Documentation with Meson
      • J.5. Documentation Authoring
      • J.6. Style Guide
    • K. PostgreSQL Limits
    • L. Acronyms
    • M. Glossary
    • N. Color Support
    • O. Obsolete or Renamed Features
  • Bibliography
  • Index
  1. VIII. Appendixes
  2. E. Release Notes
  3. E.5. Prior Releases

E.5. Prior Releases

Prior Releases

Release notes for prior release branches can be found at https://www.postgresql.org/docs/release/

On this page

  • Prior Releases

Built with Braised Docs.