# PostgreSQL 11 New Features

> GA source edition · 87 pages · 2018-10-22

---

LLMS index: [llms.txt](/en/llms.txt)

---

<div class="feature-book-intro"><p class="feature-book-kicker">WITH EXAMPLES · ENGLISH SOURCE EDITION</p><p>Explore the features, SQL examples, and compatibility changes documented by <a href="https://github.com/nori-shinoda">Noriyoshi Shinoda</a>.</p><div class="feature-book-actions"><a class="feature-read-button" href="01-about-this-document/">Start reading <span aria-hidden="true">→</span></a><a href="/en/_print/11/">Print this book</a><a href="https://github.com/nori-shinoda/documents/blob/1773200c5362476c4a549222b7d17fa91f546cae/postgresql11_ga_new_features_en_20181022-1.pdf">Original PDF on GitHub</a></div></div>

<div class="feature-source-note"><span class="feature-edition-badge">GA</span><span>2018-10-22</span><span>87 PDF pages</span><span>13 chapters</span></div>

## Contents

- [1 About This Document](/en/11/01-about-this-document/)
- [2 New Features Summary](/en/11/02-new-features-summary/)
- [3 Enhancements of Parallel Query](/en/11/03-enhancements-of-parallel-query/)
- [4 Enhancements of Partitioned Table](/en/11/04-enhancements-of-partitioned-table/)
- [5 Enhancement of Logical Replication](/en/11/05-enhancement-of-logical-replication/)
- [6 Architecture](/en/11/06-architecture/)
- [7 SQL Statement](/en/11/07-sql-statement/)
- [8 PL/pgSQL Language](/en/11/08-pl-pgsql-language/)
- [9 Configuration Parameters](/en/11/09-configuration-parameters/)
- [10 Utilities](/en/11/10-utilities/)
- [11 Contrib Modules](/en/11/11-contrib-modules/)
- [12 URL List](/en/11/12-url-list/)
- [13 Change History](/en/11/13-change-history/)

## Reference indexes

The opening chapter includes the first numbered table, [Table 1](/en/11/01-about-this-document/#pg11-tbl-1). The indexes below collect the book's other native numbered components.

<details class="feature-index"><summary>Tables</summary>

- [Table 1](/en/11/01-about-this-document/#pg11-tbl-1) — Version
- [Table 2](/en/11/01-about-this-document/#pg11-tbl-2) — Examples notation
- [Table 3](/en/11/01-about-this-document/#pg11-tbl-3) — Syntax rules
- [Table 4](/en/11/04-enhancements-of-partitioned-table/#pg11-tbl-4) — Related parameter name
- [Table 5](/en/11/04-enhancements-of-partitioned-table/#pg11-tbl-5) — Simple INSERT statement
- [Table 6](/en/11/04-enhancements-of-partitioned-table/#pg11-tbl-6) — Simple UPDATE statement \(No tuple movement between partitions\)
- [Table 7](/en/11/04-enhancements-of-partitioned-table/#pg11-tbl-7) — Simple DELETE statement
- [Table 8](/en/11/04-enhancements-of-partitioned-table/#pg11-tbl-8) — TRUNCATE statement
- [Table 9](/en/11/04-enhancements-of-partitioned-table/#pg11-tbl-9) — When a tuple is moved between partitions by executing an UPDATE statement
- [Table 10](/en/11/04-enhancements-of-partitioned-table/#pg11-tbl-10) — INSERT ON CONFLICT DO NOTHING \(With conflict\)
- [Table 11](/en/11/06-architecture/#pg11-tbl-11) — System catalogs with columns added
- [Table 12](/en/11/06-architecture/#pg11-tbl-12) — Catalogs with columns deleted
- [Table 13](/en/11/06-architecture/#pg11-tbl-13) — Value is now stored information\_schema catalog of the schema
- [Table 14](/en/11/06-architecture/#pg11-tbl-14) — Added ROLE
- [Table 15](/en/11/07-sql-statement/#pg11-tbl-15) — Added hash functions
- [Table 16](/en/11/09-configuration-parameters/#pg11-tbl-16) — Added parameters
- [Table 17](/en/11/09-configuration-parameters/#pg11-tbl-17) — Changed configuration parameters
- [Table 18](/en/11/09-configuration-parameters/#pg11-tbl-18) — Parameters with default values changed
- [Table 19](/en/11/09-configuration-parameters/#pg11-tbl-19) — Obsoleted parameter
- [Table 20](/en/11/09-configuration-parameters/#pg11-tbl-20) — Added parameters
- [Table 21](/en/11/10-utilities/#pg11-tbl-21) — The Variable indicating execution result of SQL statement
- [Table 22](/en/11/10-utilities/#pg11-tbl-22) — Default setting
- [Table 23](/en/11/10-utilities/#pg11-tbl-23) — \-C ORACLE setting
- [Table 24](/en/11/10-utilities/#pg11-tbl-24) — Added options for pg\_resetwal command
- [Table 25](/en/11/10-utilities/#pg11-tbl-25) — Option for pg\_controldata command
- [Table 26](/en/11/11-contrib-modules/#pg11-tbl-26) — Provided functions

</details>

<details class="feature-index"><summary>Examples</summary>

- [Example 1](/en/11/02-new-features-summary/#pg11-eg-1) — CREATE FUNCTION statement with WITH clause
- [Example 2](/en/11/02-new-features-summary/#pg11-eg-2) — TO\_NUMBER function
- [Example 3](/en/11/02-new-features-summary/#pg11-eg-3) — TO\_NUMBER function
- [Example 4](/en/11/03-enhancements-of-parallel-query/#pg11-eg-4) — Parallel Hash
- [Example 5](/en/11/03-enhancements-of-parallel-query/#pg11-eg-5) — Parallel Append
- [Example 6](/en/11/03-enhancements-of-parallel-query/#pg11-eg-6) — Parallelization of CREATE TABLE AS SELECT statement
- [Example 7](/en/11/03-enhancements-of-parallel-query/#pg11-eg-7) — Parallelization of CREATE MATERIALIZED VIEW statement
- [Example 8](/en/11/03-enhancements-of-parallel-query/#pg11-eg-8) — Parallelization of SELECT INTO statement
- [Example 9](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-9) — Create HASH Partitioned table
- [Example 10](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-10) — Create partition
- [Example 11](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-11) — Confirmation of table structure
- [Example 12](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-12) — Error when there are not enough partitions
- [Example 13](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-13) — Error in the INSERT statement
- [Example 14](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-14) — Partition elimination
- [Example 15](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-15) — Catalogs for Hash Partition table
- [Example 16](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-16) — Creating DEFAULT partition \(LIST partition\)
- [Example 17](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-17) — Attach DEFAULT partition \(LIST partition\)
- [Example 18](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-18) — DEFAULT partition restriction
- [Example 19](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-19) — UPDATE statement to move the partition \(preparation of data\)
- [Example 20](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-20) — UPDATE statement to move partitions \(update and confirm data\)
- [Example 21](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-21) — Create index
- [Example 22](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-22) — Check auto\-created index
- [Example 23](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-23) — Attaching a table
- [Example 24](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-24) — Individual deletion of automatically created index
- [Example 25](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-25) — Create the primary key in the partitioned table
- [Example 26](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-26) — Attached table and primary key
- [Example 27](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-27) — Create primary key without partition key column
- [Example 28](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-28) — Attach a table with a different primary key
- [Example 29](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-29) — Partitions and Unique Constraints with FOREIGN TABLE
- [Example 30](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-30) — Partitioned table and INSERT ON CONFLICT statement
- [Example 31](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-31) — INSERT ON CONFLICT statement across partitions
- [Example 32](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-32) — Execution plan in the default state
- [Example 33](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-33) — Execution plan with Partition\-Wise Join enabled
- [Example 34](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-34) — Execution plan with Partition\-Wise Aggregate enabled
- [Example 35](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-35) — Partitioned table and Foreign key
- [Example 36](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-36) — Executed SQL statement for test
- [Example 37](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-37) — Execution plan on PostgreSQL 10
- [Example 38](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-38) — Execution plan on PostgreSQL 11
- [Example 39](/en/11/04-enhancements-of-partitioned-table/#pg11-eg-39) — Control Partition Pruning
- [Example 40](/en/11/05-enhancement-of-logical-replication/#pg11-eg-40) — Specify TRUNCATE clause in CREATE PUBLICATION statement
- [Example 41](/en/11/05-enhancement-of-logical-replication/#pg11-eg-41) — Set replication start LSN to current LSN
- [Example 42](/en/11/06-architecture/#pg11-eg-42) — Refer pg\_stat\_activity catalog
- [Example 43](/en/11/06-architecture/#pg11-eg-43) — Refer pg\_attribute catalog
- [Example 44](/en/11/06-architecture/#pg11-eg-44) — pg\_read\_server\_files role
- [Example 45](/en/11/06-architecture/#pg11-eg-45) — Execution plan of SQL using JIT compilation features
- [Example 46](/en/11/06-architecture/#pg11-eg-46) — Test case
- [Example 47](/en/11/06-architecture/#pg11-eg-47) — Lock status on PostgreSQL 10
- [Example 48](/en/11/06-architecture/#pg11-eg-48) — Lock status on PostgreSQL 11
- [Example 49](/en/11/06-architecture/#pg11-eg-49) — pgproto definition file
- [Example 50](/en/11/06-architecture/#pg11-eg-50) — backup\_label file
- [Example 51](/en/11/07-sql-statement/#pg11-eg-51) — Execution of LOCK TABLE statement for the view
- [Example 52](/en/11/07-sql-statement/#pg11-eg-52) — Executing LOCK TABLE statement to MATERIALIZED VIEW
- [Example 53](/en/11/07-sql-statement/#pg11-eg-53) — STATISTICS value for function index column
- [Example 54](/en/11/07-sql-statement/#pg11-eg-54) — Execution of VACUUM\, ANALYZE statement for multiple tables
- [Example 55](/en/11/07-sql-statement/#pg11-eg-55) — Output \&quot\;aggressively\&quot\;
- [Example 56](/en/11/07-sql-statement/#pg11-eg-56) — Execution plan of PostgreSQL 10
- [Example 57](/en/11/07-sql-statement/#pg11-eg-57) — Execution plan of PostgreSQL 11
- [Example 58](/en/11/07-sql-statement/#pg11-eg-58) — INCLUDE clause with CREATE INDEX statement
- [Example 59](/en/11/07-sql-statement/#pg11-eg-59) — INCLUDE clause with CREATE TABLE statement
- [Example 60](/en/11/07-sql-statement/#pg11-eg-60) — The option of CREATE INDEX statement
- [Example 61](/en/11/07-sql-statement/#pg11-eg-61) — Storage option for CREATE TABLE statement
- [Example 62](/en/11/07-sql-statement/#pg11-eg-62) — Parallel query executed in EXPLAIN statement
- [Example 63](/en/11/07-sql-statement/#pg11-eg-63) — SHA512 hash function
- [Example 64](/en/11/07-sql-statement/#pg11-eg-64) — json\_to\_tsvector function
- [Example 65](/en/11/07-sql-statement/#pg11-eg-65) — websearch\_to\_tsquery function
- [Example 66](/en/11/07-sql-statement/#pg11-eg-66) — \^\@ operator
- [Example 67](/en/11/07-sql-statement/#pg11-eg-67) — Casting from JSONB type
- [Example 68](/en/11/08-pl-pgsql-language/#pg11-eg-68) — PROCEDURE creation
- [Example 69](/en/11/08-pl-pgsql-language/#pg11-eg-69) — Execute PROCEDURE \(1\)
- [Example 70](/en/11/08-pl-pgsql-language/#pg11-eg-70) — Execute PROCEDURE \(2\)
- [Example 71](/en/11/08-pl-pgsql-language/#pg11-eg-71) — List of PROCEDURE
- [Example 72](/en/11/08-pl-pgsql-language/#pg11-eg-72) — Display PROCEDURE definition
- [Example 73](/en/11/08-pl-pgsql-language/#pg11-eg-73) — Transaction control in PROCEDURE
- [Example 74](/en/11/08-pl-pgsql-language/#pg11-eg-74) — CONSTANT clause and changing value error
- [Example 75](/en/11/08-pl-pgsql-language/#pg11-eg-75) — NOT NULL clause and initial value
- [Example 76](/en/11/08-pl-pgsql-language/#pg11-eg-76) — SET TRANSACTION statement
- [Example 77](/en/11/09-configuration-parameters/#pg11-eg-77) — Parameter parallel\_leader\_participation \= on
- [Example 78](/en/11/09-configuration-parameters/#pg11-eg-78) — Parameter parallel\_leader\_participation \= off
- [Example 79](/en/11/09-configuration-parameters/#pg11-eg-79) — Parameter log\_parser\_stats \(Linux\)
- [Example 80](/en/11/10-utilities/#pg11-eg-80) — \\gdesc command
- [Example 81](/en/11/10-utilities/#pg11-eg-81) — SQL execution result variable
- [Example 82](/en/11/10-utilities/#pg11-eg-82) — String output declaration
- [Example 83](/en/11/10-utilities/#pg11-eg-83) — String output declaration
- [Example 84](/en/11/10-utilities/#pg11-eg-84) — Fetch the column data
- [Example 85](/en/11/10-utilities/#pg11-eg-85) — Specify DO CONTINUE clause
- [Example 86](/en/11/10-utilities/#pg11-eg-86) — initdb \-\-wal\-segsize option
- [Example 87](/en/11/10-utilities/#pg11-eg-87) — initdb \-\-allow\-group\-access option
- [Example 88](/en/11/10-utilities/#pg11-eg-88) — pg\_dumpall command with specifying the character encoding
- [Example 89](/en/11/10-utilities/#pg11-eg-89) — pg\_ctl command
- [Example 90](/en/11/10-utilities/#pg11-eg-90) — pg\_basebackup command
- [Example 91](/en/11/10-utilities/#pg11-eg-91) — Batch mode operation
- [Example 92](/en/11/10-utilities/#pg11-eg-92) — Usage of pg\_verify\_checksums command
- [Example 93](/en/11/10-utilities/#pg11-eg-93) — Execute pg\_verify\_checksums command
- [Example 94](/en/11/11-contrib-modules/#pg11-eg-94) — Use amcheck module
- [Example 95](/en/11/11-contrib-modules/#pg11-eg-95) — Create B\-Tree Gin index
- [Example 96](/en/11/11-contrib-modules/#pg11-eg-96) — Execution plan on PostgreSQL 10
- [Example 97](/en/11/11-contrib-modules/#pg11-eg-97) — Execution plan on PostgreSQL 11
- [Example 98](/en/11/11-contrib-modules/#pg11-eg-98) — jsonb\_plpython module
- [Example 99](/en/11/11-contrib-modules/#pg11-eg-99) — jsonb\_plperl module
- [Example 100](/en/11/11-contrib-modules/#pg11-eg-100) — bt\_metap function
- [Example 101](/en/11/11-contrib-modules/#pg11-eg-101) — strict\_word\_similarity function
- [Example 102](/en/11/11-contrib-modules/#pg11-eg-102) — Partition with FOREIGN TABLE
- [Example 103](/en/11/11-contrib-modules/#pg11-eg-103) — The behavior of PostgreSQL 10
- [Example 104](/en/11/11-contrib-modules/#pg11-eg-104) — The behavior of PostgreSQL 11
- [Example 105](/en/11/11-contrib-modules/#pg11-eg-105) — Execution plan of PostgreSQL 10
- [Example 106](/en/11/11-contrib-modules/#pg11-eg-106) — Execution plan of PostgreSQL 11

</details>

<details class="feature-index"><summary>Figures</summary>



</details>

## Source edition



The source chapter order, wording, examples, and publication credits are retained.
PDF page markers link the reading edition to the original document.
[Source identity and coverage](/sources/pg11-semantic.json) · [About the collection](/en/materials/)

---

Section pages:

- [About This Document](/en/11/01-about-this-document/): Source chapter 1 for PostgreSQL 11.
- [New Features Summary](/en/11/02-new-features-summary/): Source chapter 2 for PostgreSQL 11.
- [Enhancements of Parallel Query](/en/11/03-enhancements-of-parallel-query/): Source chapter 3.1 for PostgreSQL 11.
- [Enhancements of Partitioned Table](/en/11/04-enhancements-of-partitioned-table/): Source chapter 3.2 for PostgreSQL 11.
- [Enhancement of Logical Replication](/en/11/05-enhancement-of-logical-replication/): Source chapter 3.3 for PostgreSQL 11.
- [Architecture](/en/11/06-architecture/): Source chapter 3.4 for PostgreSQL 11.
- [SQL Statement](/en/11/07-sql-statement/): Source chapter 3.5 for PostgreSQL 11.
- [PL/pgSQL Language](/en/11/08-pl-pgsql-language/): Source chapter 3.6 for PostgreSQL 11.
- [Configuration Parameters](/en/11/09-configuration-parameters/): Source chapter 3.7 for PostgreSQL 11.
- [Utilities](/en/11/10-utilities/): Source chapter 3.8 for PostgreSQL 11.
- [Contrib Modules](/en/11/11-contrib-modules/): Source chapter 3.9 for PostgreSQL 11.
- [URL List](/en/11/12-url-list/): The source document's url list.
- [Change History](/en/11/13-change-history/): The source document's change history.