PostgreSQL 18 Documentation
🌙
Dark
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
I. Tutorial
3. Advanced Features
3. Advanced Features