PDF page 45
This section explains new features related to SQL statements. #### 3.2.1 ALTER DOMAIN statement {#pg19-3-2-1-alter-domain-statement} The lock level required to execute the ALTER DOMAIN VALIDATE CONSTRAINT statement has been changed from SHARE to SHARE UPDATE EXCLUSIVE. [[16a0039](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=16a0039dc0d1d0cdfadaf38cd3a30f3c8f590c48)] #### 3.2.2 ALTER FOREIGN DATA WRAPPER statement {#pg19-3-2-2-alter-foreign-data-wrapper-statement} The CONNECTION clause, which specifies the function used to establish a new connection, has been added. [[8185bb5](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8185bb53476378443240d57f7d844347d5fae1bf)] #### Syntax 4 - ALTER FOREIGN DATA WAPPER CONNECTION statement {#pg19-syntax-4} ```sql ALTER FOREIGN DATA WAPPER fdw_name CONNECTION function_name ALTER FOREIGN DATA WAPPER fdw_name NO CONNECTION ``` #### 3.2.3 ALTER PUBLICATION statement {#pg19-3-2-3-alter-publication-statement} It is now possible to specify the SET ALL TABLES clause and the SET ALL SEQUENCES clause in an ALTER PUBLICATION statement. This is only possible for publications that specify the ALL TABLES/SEQUENCES clause or for empty publications. [[5984ea8](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5984ea868eeeb202bddedfbdaca81adfe7ea6779), [fd36606](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fd366065e06ae953c4f2d973d5c5f0474f3b87b6), [493f8c6](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=493f8c6439cf64d75883c650b5dd573d8fe0664b), [5509055](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5509055d6956745532e65ab218e15b99d87d66ce)] #### Syntax 5 - ALTER PUBLICATION statement {#pg19-syntax-5} ```sql ALTER PUBLICATION publication_name SET ALL TABLES [EXCEPT (TABLE expression)] ALTER PUBLICATION publication_name SET ALL SEQUENCES ```PDF page 46
**Example 40.** Executing the ALTER PUBLICATION statement ```text postgres=# CREATE PUBLICATION pub1 FOR ALL TABLES ; CREATE PUBLICATION postgres=# ALTER PUBLICATION pub1 SET ALL TABLES EXCEPT (TABLE data2) ; ALTER PUBLICATION postgres=# CREATE PUBLICATION pub2 ; CREATE PUBLICATION postgres=# ALTER PUBLICATION pub2 SET ALL SEQUENCES ; ALTER PUBLICATION ``` #### 3.2.4 ALTER SUBSCRIPTION statement {#pg19-3-2-4-alter-subscription-statement} The ALTER SUBSCRIPTION REFRESH SEQUENCES statement, which updates the sequence information included in the subscription, has been added. [[f0b3573](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f0b3573c3aac6c0ea4cbc278f98178516579d370)] #### Syntax 6 - ALTER SUBSCRIPTION REFRESH SEQUENCES statement {#pg19-syntax-6} ```sql ALTER SUBSCRIPTION subscription_name REFRESH SEQUENCES ``` **Example 41.** Specifying the REFRESH SEQUENCES clause ```text postgres=# ALTER SUBSCRIPTION sub1 REFRESH SEQUENCES ; ALTER SUBSCRIPTION ``` #### 3.2.5 ALTER TABLE statement {#pg19-3-2-5-alter-table-statement} The following enhancements have been added to the ALTER TABLE statement. ##### Changing the Expression for Virtual Generated Columns {#pg19-p46-changing-the-expression-for-virtual-generated-columns} It is now possible to change the expression for virtual generated columns with a CHECK constraint using the SET EXPRESSION clause. [[f80bedd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f80bedd52b1501de51b7180cd817e3e1fc31429d)] #### Syntax 7 - SET EXPRESSION clause {#pg19-syntax-7} ```sql ALTER TABLE table_name ALTER COLUMN column_name SET EXPRESSION AS expression ```PDF page 47
**Example 42.** Altering Virtual Columns with CHECK Constraints ```text postgres=> CREATE TABLE data1(col1 INT, col2 INT GENERATED ALWAYS AS (col1 * 100) CHECK (col2 < 10000)) ; CREATE TABLE postgres=> ALTER TABLE data1 ALTER COLUMN col2 SET EXPRESSION AS (col1 * 200) ; ALTER TABLE ``` ##### Setting the CHECK constraint to ENFORCED {#pg19-p47-setting-the-check-constraint-to-enforced} It is now possible to change the setting of the ENFORCED clause for CHECK constraints. [[342051d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=342051d73b386ad763e13930e15031f7ba5367f5)] #### Syntax 8 - ENFORCED clause {#pg19-syntax-8} ```sql ALTER TABLE table_name ALTER CONSTRAINT constraint_name [NOT] ENFORCED ``` **Example 43.** Setting the ENFORCED clause for CHECK constraints ```text postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY, col2 INT) ; CREATE TABLE postgres=> ALTER TABLE data1 ADD CONSTRAINT data1_col2_check1 CHECK(col2 < 1000) ; ALTER TABLE postgres=> INSERT INTO data1 SELECT g1, g1 FROM generate_series(1, 999) g1 ; INSERT 0 999 postgres=> ALTER TABLE data1 ALTER CONSTRAINT data1_col2_check1 NOT ENFORCED ; ALTER TABLE postgres=> INSERT INTO data1 VALUES (1000, 1000) ; INSERT 0 1 postgres=> ALTER TABLE data1 ALTER CONSTRAINT data1_col2_check1 ENFORCED ; ERROR: check constraint "data1_col2_check1" of relation "data1" is violated by some row postgres=> DELETE FROM data1 WHERE col2=1000 ; DELETE 1 postgres=> ALTER TABLE data1 ALTER CONSTRAINT data1_col2_check1 ENFORCED ; ALTER TABLE ```PDF page 48
##### Option autovacuum_parallel_workers {#pg19-p48-option-autovacuum-parallel-workers} The autovacuum_parallel_workers option has been added for tables. This option can also be specified in the CREATE TABLE statement. It specifies the number of parallel VACUUM worker processes. The default value for this option is -1, and it inherits the value from the autovacuum_max_parallel_workers parameter. If this option is set to 0, parallel VACUUM is disabled. [[1ff3180](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1ff3180ca0169556984ab83759477f593129794d)] **Example 44.** Configuring the number of parallel workers for automatic VACUUM ```text postgres=> ALTER TABLE data1 SET (autovacuum_parallel_workers = 4) ; ALTER TABLE ``` ##### Optimizing ALTER TABLE Statements {#pg19-p48-optimizing-alter-table-statements} When using the ALTER TABLE ADD COLUMN statement, the addition of a column with a CHECK constraint or a NOT NULL constraint on a domain can now be checked for the need to rewrite the table. If no rewrite is necessary, the table will not be rewritten. [[a0b6ef2](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a0b6ef29a51818a4073a5f390ed10ef6453d5c11)] #### 3.2.6 CHECKPOINT statement {#pg19-3-2-6-checkpoint-statement} The following options have been added to the CHECKPOINT statement. ##### MODE option {#pg19-p48-mode-option} The MODE option determines how checkpoints are executed. The MODE FAST option is the default behavior and executes checkpoints as quickly as possible. If the MODE SPREAD option is specified, checkpoints are executed with reduced load based on relevant parameters. [[2f698d7](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2f698d7f4b7b4c49c3649b2fcc063eb66f9d2e6c)] #### Syntax 9 - CHECKPOINT statement {#pg19-syntax-9} ```sql CHECKPOINT [(MODE {FAST|SPREAD})] ``` **Example 45.** MODE option ```text postgres=# CHECKPOINT (MODE FAST) ; CHECKPOINT postgres=# CHECKPOINT (MODE SPREAD) ; CHECKPOINT ``` ```text Depending on the options specified, the output log will change as follows. ```PDF page 49
**Table 21.** Output logs | Option | Log message | Note | | --- | --- | --- | | FAST | LOG: checkpoint starting: fast force wait | | | SPREAD | LOG: checkpoint starting: force wait | | ##### FLUSH_UNLOGGED option {#pg19-p49-flush-unlogged-option} Enabling the FLUSH_UNLOGGED option causes the database to also flush dirty buffers that have not been logged at the checkpoint. [[8d33fba](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8d33fbacbac93ed70757ea47cd8a4b4fae61528a), [a4f1265](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a4f126516e688736bfed332b44a0c221b8dc118a)] #### Syntax 10 - FLUSH_UNLOGGED option {#pg19-syntax-10} ```sql CHECKPOINT [(FLUSH_UNLOGGED [boolean])] ``` **Example 46.** FLUSH\_UNLOGGED option ```text postgres=# CHECKPOINT (FLUSH_UNLOGGED) ; CHECKPOINT ``` ```text Depending on the options specified, the output log will change as follows. ``` **Table 22.** Output logs | Option | Log message | | --- | --- | | No specification | LOG: checkpoint starting: fast force wait | | FLUSH_UNLOGGED | LOG: checkpoint starting: fast force wait flush-unlogged | #### 3.2.7 COPY statement {#pg19-3-2-7-copy-statement} The following new features have been implemented to the COPY statement. ##### ON_ERROR option setting {#pg19-p49-on-error-option-setting} It is now possible to specify the "set_null" for the on_error option in the COPY FROM statement. This option sets the column value to NULL if a data type conversion error occurs. [[2a525cc](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2a525cc97e19868940c533787165bc7e7de3a80a)]PDF page 50
**Example 47.** ON\_ERROR option ```text postgres=# \d data1 Table "public.data1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- col1 | integer | | | col2 | integer | | | postgres=# COPY data1 FROM stdin WITH (format 'csv', on_error 'set_null') ; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. >> 100,100 >> 200,bad_data >> 300,300 >> \. NOTICE: in 1 row, columns were set to null due to data type incompatibility COPY 3 postgres=# SELECT * FROM data1 ; col1 | col2 ------+------ 100 | 100 200 | null 300 | 300 (3 rows) ``` ##### Partition Table Support {#pg19-p50-partition-table-support} The COPY TO statement can now be executed on partition tables. In previous versions, it was necessary to execute the COPY (SELECT …) TO statement instead of using the partition table name. [[4bea91f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4bea91f21f61d01bd40a4191a4a8c82d0959fffe)]PDF page 51
**Example 48.** Specifying the partition table ```text postgres=# \d part1 Partitioned table "public.part1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- col | integer | | not null | col2 | character varying(10) | | | Partition key: RANGE (c1) Indexes: "part1_pkey" PRIMARY KEY, btree (col1) Number of partitions: 2 (Use \d+ to list them.) postgres=# COPY part1 TO '/tmp/part1.csv' CSV ; COPY 2000000 ``` ##### Number of header rows {#pg19-p51-number-of-header-rows} It is now possible to specify the number of header rows in the data. [[bc2f348](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bc2f348e87c02de63647dbe290d64ff088880dbe)] **Example 49.** Specifying the number of header rows ```text postgres=# COPY data1 FROM stdin WITH (FORMAT CSV, HEADER 2) ; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. >> HEADER1 >> HEADER2 >> 100,data1 >> 200,data2 >> \. COPY 2 ``` ##### JSON format output {#pg19-p51-json-format-output} It is now possible to specify "json" as the FORMAT option for the COPY TO statement. The additional option "force_array" can also be specified. [[7dadd38](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7dadd38cda95bf5bc0c4715d9ab71766d1693379), [4c0390a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4c0390ac53b745c2800f6aa3d9ee2515d6ab499b)]PDF page 52
**Example 50.** Output to JSON format ```text postgres=# COPY data1 TO stdout WITH (FORMAT json) ; {"col1":1,"col2":"data1"} {"col1":2,"col2":"data2"} {"col1":3,"col2":"data3"} {"col1":4,"col2":"data4"} {"col1":5,"col2":"data5"} postgres=# COPY data1 TO stdout WITH (FORMAT json, FORCE_ARRAY) ; [ {"col1":1,"col2":"data1"} ,{"col1":2,"col2":"data2"} ,{"col1":3,"col2":"data3"} ,{"col1":4,"col2":"data4"} ,{"col1":5,"col2":"data5"} ] ``` #### 3.2.8 CREATE PUBLICATION statement {#pg19-3-2-8-create-publication-statement} It is now possible to specify the FOR ALL SEQUENCES clause, which applies to all sequences. This clause requires a user with the SUPERUSER privilege to execute the CREATE PUBLICATION statement. To indicate whether this clause has been specified, the puballsequences column has been added to the pg_publication catalog. [[96b3784](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=96b37849734673e7c82fb86c4f0a46a28f500ac8)] #### Syntax 11 - FOR ALL SEQUENCES clause {#pg19-syntax-11} ```sql CREATE PUBLICATION publication_name FOR ALL SEQUENCES ```PDF page 53
**Example 51.** Specifying the FOR ALL SEQUENCES clause ```text postgres=# CREATE PUBLICATION pub1 FOR ALL SEQUENCES ; CREATE PUBLICATION postgres=# \dRp List of publications Name | Owner | All tables | All sequences | Inserts | Updates | Deletes… ------+----------+------------+---------------+---------+---------+--------… pub1 | postgres | f | t | t | t | t … (1 row) ``` ```text No tables can be added to a publication that specifies the ALL SEQUENCES clause. ``` **Example 52.** Add a table to the publication ```text postgres=# ALTER PUBLICATION pub1 ADD TABLE data1 ; ERROR: publication "pub1" is defined as FOR ALL SEQUENCES DETAIL: Tables or sequences cannot be added to or dropped from FOR ALL SEQUENCES publications. ``` #### 3.2.9 CREATE SCHEMA statement {#pg19-3-2-9-create-schema-statement} It is now possible to specify objects other than tables, views, indexes, sequences, triggers, and GRANT in the CREATE SCHEMA statement. [[a9c350d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a9c350d9ee66745aadcf7c0c95a567752a762171), [404db8f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=404db8f9edbb291bcc31542d488fb20f6a965798)] **Example 53.** The CREATE SCHEMA Statement and Creating Objects ```text postgres=# CREATE SCHEMA schema1 CREATE TABLE data1(col1 INT PRIMARY KEY, col2 VARCHAR(10)) CREATE COLLATION col1 (LOCALE="C") CREATE DOMAIN d1 AS INT ; CREATE SCHEMA ``` ```sql The following CREATE clauses can be specified: ```PDF page 54
**Table 23.** Types of objects that can be created at the same time | Object | PostgreSQL 18 | PostgreSQL 19 | Note | | --- | --- | --- | --- | | AGGREGATE | No | Yes | | | COLLATION | No | Yes | | | DOMAIN | No | Yes | | | FUNCTION | No | Yes | | | INDEX | Yes | Yes | | | OPERATOR | No | Yes | | | PROCEDURE | No | Yes | | | SEQUENCE | Yes | Yes | | | TABLE | Yes | Yes | | | TEXT SEARCH CONFIGURATION | No | Yes | | | TEXT SEARCH DICTIONARY | No | Yes | | | TEXT SEARCH PARSER | No | Yes | | | TEXT SEARCH TEMPLATE TRIGGER | No | Yes | | | | Yes | Yes | | | TYPE | No | Yes | | | VIEW | Yes | Yes | | | GRANT | Yes | Yes | | #### 3.2.10 CREATE STATISTICS statement {#pg19-3-2-10-create-statistics-statement} It is now possible to create extended statistics by specifying a virtual column. [[f7f4052](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f7f4052a4e959ded803417b14af2b45316445940)] **Example 54.** Creating Extended Statistics for Virtual Generated Columns ```text postgres=> CREATE TABLE data1(col1 INT, col2 INT GENERATED ALWAYS AS (col1 + 100) VIRTUAL) ; CREATE TABLE postgres=> CREATE STATISTICS data1_stat1 ON (col2) FROM data1 ; CREATE STATISTICS ```PDF page 55
#### 3.2.11 DELETE / UPDATE statement {#pg19-3-2-11-delete-update-statement} The FOR PORTION OF clause can now be specified in UPDATE and DELETE statements. When a range is specified in the FOR PORTION OF clause, a new tuple matching that range is created, and the updates specified in the SET clause are applied to it. The original tuple is split into tuples that fall outside the range specified in the FOR PORTION OF clause. [[8e72d91](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=8e72d914c52876525a90b28444453de8085c866f;hp=ec2f81766ad72d3ff4c45400f30d2670589aec11)] #### Syntax 12 - FOR PORTION OF clause {#pg19-syntax-12} ```sql UPDATE table_name FOR PORTION OF column FROM start TO end SET … WHERE … DELETE FROM table_name FOR PORTION OF column FROM start TO end WHERE … ``` Since the behavior can be confusing, a sample will be used to explain it. Initially, the range1 table contains two tuples: one in the col1 column (of type DATERANGE) representing the range "April 1, 2026 - May 1, 2026" and another representing the range "May 1, 2026 - June 1, 2026". **Example 55.** Tuples before the update ```text postgres=> SELECT * FROM range1 ORDER BY col1 ; col1 | col2 -------------------------+------ [2026-04-01,2026-05-01) | 100 [2026-05-01,2026-06-01) | 200 (2 rows) ``` ```text If the FOR PORTION OF clause is set to "2026/04/10-2026/04/20", the tuples to be updated are selected (tuples where col2=100). These tuples are split into the following three tuples. • 2026/04/01-2026/04/10 (The first half of the original tuple range) • 2026/04/10-2026/04/20 (Tuple to be updated; updated using a SET clause) • 2026/04/20-2026/05/01 (The second half of the original tuple range) ```PDF page 56
**Example 56.** Tuples after update ```text postgres=> UPDATE range1 FOR PORTION OF col1 FROM '2026-04-10' TO '2026-04-20' SET col2=300 ; UPDATE 1 postgres=> SELECT * FROM range1 ORDER BY col1 ; col1 | col2 -------------------------+------ [2026-04-01,2026-04-10) | 100 [2026-04-10,2026-04-20) | 300 [2026-04-20,2026-05-01) | 100 [2026-05-01,2026-06-01) | 200 (4 rows) ``` #### 3.2.12 EXPLAIN statement {#pg19-3-2-12-explain-statement} The following features have been added to the EXPLAIN statement. ##### Output of I/O Statistics {#pg19-p56-output-of-i-o-statistics} The IO option has been added to the EXPLAIN statement to output I/O statistics. The IO option must be specified together with the ANALYZE option. [[681daed](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=681daed93169ff5bed2796dd5c8c76ee2fae1ebd), [3b1117d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3b1117d6e2e47d86cdbd978b79434c630cb0ef52), [e157fe6](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e157fe6f76e0be4f05a725b3099cf53949a2c2af)] **Example 57.** Executing an EXPLAIN \(IO\) statement ```text postgres=> EXPLAIN (ANALYZE, IO, COSTS OFF) SELECT COUNT(*) FROM data1 ; QUERY PLAN ------------------------------------------------------------------------------ Aggregate (actual time=425.023..425.024 rows=1.00 loops=1) Buffers: shared hit=1099 read=52956 -> Seq Scan on data1 (actual time=0.416..235.808 rows=10000000.00 loops=1) Prefetch: avg=70.84 max=91 capacity=94 I/O: count=3314 waits=7 size=15.98 in-progress=4.54 Buffers: shared hit=1099 read=52956 Planning Time: 0.059 ms Execution Time: 425.056 ms (8 rows) ```PDF page 57
##### Output of Memoize Estimation Information {#pg19-p57-output-of-memoize-estimation-information} Estimation information (Estimates:) is now output when Memoize node is included in the execution plan. [[4bc62b8](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4bc62b86849065939a6b85273fece6b92d6e97bf)] **Example 58.** Outputting Memoize Estimate Information ```text postgres=> EXPLAIN SELECT * FROM data1 AS d1 INNER JOIN data1 AS d2 ON d1.col1 = d2.col2::numeric AND d1.col2::numeric = d2.col1 ; QUERY PLAN ----------------------------------------------------------------------------- Nested Loop (cost=0.15..10.49 rows=1 width=36) -> Seq Scan on data1 d1 (cost=0.00..1.40 rows=40 width=18) -> Memoize (cost=0.15..0.41 rows=1 width=18) Cache Key: d1.col1, (d1.col2)::numeric Cache Mode: logical Estimates: capacity=20 distinct keys=20 lookups=40 hit percent=50.00% -> Index Only Scan using idx1_data1 on data1 d2 (cost=0.14..0.40… Index Cond: (col1 = (d1.col2)::numeric) Filter: (d1.col1 = (col2)::numeric) (9 rows) ``` ##### Output of WAL FPI Byte Count {#pg19-p57-output-of-wal-fpi-byte-count} The WAL data now includes FPI bytes. [[5ab0b6a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5ab0b6a248076bf373a80bc7e83a5dfa4edf13aa)]PDF page 58
**Example 59.** Output of FPI byte information ```text postgres=> EXPLAIN (ANALYZE, WAL) UPDATE data1 SET col2='update' WHERE col1 < 100 ; QUERY PLAN ------------------------------------------------------------------------------ Update on data1 (cost=0.00..1.50 rows=0 width=0) (actual time=0.225..0.225… Buffers: shared hit=121 read=1 dirtied=3 WAL: records=80 fpi=2 bytes=9790 fpi bytes=3656 -> Seq Scan on data1 (cost=0.00..1.50 rows=40 width=38) (actual time=0.… Filter: (col1 < '100'::numeric) Buffers: shared hit=1 Planning Time: 0.281 ms Execution Time: 0.363 ms (8 rows) ``` ##### Subquery Name {#pg19-p58-subquery-name} The subquery blocks in the executed execution plan now display unique names. Previously, only numbers were displayed. [[8c49a48](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8c49a484e8ebb0199fba4bd68eaaedaf49b48ed0)]PDF page 59
**Example 60.** PostgreSQL 19 Output ```text postgres=> EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM data1 WHERE col1 = (SELECT MAX(col1) FROM data1) ; QUERY PLAN ------------------------------------------------------------------------------ Aggregate InitPlan expr_1 -> Result Replaces: MinMaxAggregate InitPlan minmax_1 -> Limit -> Index Only Scan Backward using data1_pkey on data1 data1_1 -> Index Only Scan using data1_pkey on data1 Index Cond: (col1 = (InitPlan expr_1).col1) (9 rows) ``` **Example 61.** PostgreSQL 18 Output ```text postgres=> EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM data1 WHERE col1 = (SELECT MAX(col1) FROM data1) ; QUERY PLAN ------------------------------------------------------------------------------ Aggregate InitPlan 2 -> Result InitPlan 1 -> Limit -> Index Only Scan Backward using data1_pkey on data1 data1_1 -> Index Only Scan using data1_pkey on data1 Index Cond: (col1 = (InitPlan 2).col1) (8 rows) ```PDF page 60
#### 3.2.13 GRANT statement {#pg19-3-2-13-grant-statement} It is now possible to specify a grantee other than the current user in the GRANTED BY clause of GRANT and REVOKE statements. In previous versions, this resulted in the error "ERROR: grantor must be current user.". [[dd1398f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dd1398f1378799acc60c3ed85d82439b2ff69141)] **Example 62.** Specifying the GRANTED BY clause ```text postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY, col2 VARCHAR(10)) ; CREATE TABLE postgres=> GRANT SELECT ON data1 TO demo2 WITH GRANT OPTION ; GRANT postgres=> \connect postgres postgres You are now connected to database "postgres" as user "postgres". postgres=# GRANT SELECT ON data1 TO demo3 GRANTED BY demo2 ; GRANT postgres=# \dp+ Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+-------+-------+--------------------+-------------------+---------- public | data1 | table | demo=arwdDxtm/demo+| | | | | demo2=r*/demo +| | | | | demo3=r/demo2 | | (1 row) ``` #### 3.2.14 INSERT statement {#pg19-3-2-14-insert-statement} It is now possible to specify the DO SELECT clause in an INSERT ON CONFLICT statement. [[8832709](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=88327092ff06c48676d2a603420089bf493770f3)] #### Syntax 13 - ON CONFLICT DO SELECT {#pg19-syntax-13} ```sql INSERT INTO table_name VALUES (…) ON CONFLICT (column_name) DO SELECT [FOR {UPDATE}|{SHARE}] [RETURNING …] ```PDF page 61
**Example 63.** INSERT ON CONFLICT DO SELECT statement ```text postgres=> INSERT INTO data1 VALUES (100, 'coflict100') ON CONFLICT (col1) DO SELECT FOR UPDATE RETURNING * ; col1 | col2 ------+------------ 100 | coflict100 (1 row) INSERT 0 1 ``` #### 3.2.15 REPACK statement {#pg19-3-2-15-repack-statement} The REPACK statement has been added to PostgreSQL 19. The REPACK statement is the SQL statement that executes both the VACUUM FULL and CLUSTER statements in the single command. When the REPACK statement is executed on its own, it targets all tables and materialized views for which the current user has MAINTAIN privileges. It is also possible to specify specific tables, but UNLOGGED tables and partitioned tables cannot be specified. The USING INDEX clause sorts the data based on the information in the specified index. The progress of the REPACK statement can be monitored via the pg_stat_progress_repack view. [[ac58465](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ac58465e0618941842439eb3f5a2cf8bebd5a3f1), [28d534e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=28d534e2ae0ac888b5460f977a10cd9bb017ef98), [e76d8c7](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e76d8c749c3152657711ed733f0aea61c0e36a91), [5dbb63f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5dbb63fc82b72ccc6f9e7be0361efdeee5deeb50)] #### Syntax 14 - REPACK statement {#pg19-syntax-14} ```sql REPACK [(options)] [object] [USING INDEX [index_name]] ``` The following options can be specified in a REPACK statement. **Table 24.** Options for the REPACK statement | Option | Description | | --- | --- | | ANALYZE | Execute an ANALYZE statement after the REPACK operation is complete | | CONCURRENTLY | Minimize locks and allow access to other SQL statements | | VERBOSE | Output a detailed execution log |PDF page 62
**Example 64.** Executing the REPACK statement ```text postgres=> REPACK (ANALYZE, VERBOSE) data1 ; INFO: repacking "public.data1" in physical order INFO: "public.data1": found 1666667 removable, 3333333 nonremovable row versions in 27028 pages DETAIL: 0 dead row versions cannot be removed yet. CPU: user: 0.58 s, system: 0.20 s, elapsed: 0.94 s. INFO: analyzing "public.data1" INFO: "data1": scanned 18019 of 18019 pages, containing 3333333 live rows and 0 dead rows; 30000 rows in sample, 3333333 estimated total rows INFO: finished analyzing table "postgres.public.data1" avg read rate: 1582.075 MB/s, avg write rate: 0.088 MB/s buffer usage: 23 hits, 18023 reads, 1 dirtied WAL usage: 3 records, 1 full page images, 2165 bytes, 1560 full page image bytes, 0 buffers full system usage: CPU: user: 0.06 s, system: 0.01 s, elapsed: 0.08 s REPACK ``` ```sql If the CONCURRENTY option is specified in the REPACK statement, the SHARE UPDATE EXCLUSIVE lock is used on the table. Update transactions during the REPACK statement execution generate update changes via the logical decoding feature. These update changes are ultimately applied to the new table. During the execution of a REPACK statement, a temporary logical replication slot named “repack_{ID}” is automatically generated internally. The number of replication slots reserved for REPACK is determined by the max_repack_replication_slots parameter. ``` #### 3.2.16 SELECT statement {#pg19-3-2-16-select-statement} The following features have been added to SELECT statements. ##### GROUP BY ALL clause {#pg19-p62-group-by-all-clause} The ALL clause can now be specified in the GROUP BY clause. This executes the GROUP BY operation using all output columns. [[ef38a4d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ef38a4d9756db9ae1d20f40aa39f3cf76059b81a)]PDF page 63
**Example 65.** Specifying the GROUP BY ALL clause ```text postgres=> SELECT col1, col2 FROM data1 GROUP BY ALL ORDER BY 1 ; col1 | col2 ------+------- 1 | data1 2 | data2 3 | data3 4 | data4 (4 rows) ``` ##### GROUPING SETS and Subqueries {#pg19-p63-grouping-sets-and-subqueries} Subqueries now support references to GROUPING SETS columns. The following syntax would have resulted in an error in previous versions. [[415100a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=415100aa62bff1402672eec4b68deef2cd436126)] **Example 66.** GROUPING SETS and Subqueries ```text postgres=> SELECT (SELECT col1), (SELECT col2), (SELECT GROUPING(col1, col2)), (SELECT SUM(t1.col3)), (SELECT max(t2.col3)) FROM gstest1 t1 FULL JOIN gstest2 t2 USING (col1, col2) GROUP BY GROUPING SETS ((col1, col2), ()) ; col1 | col2 | grouping | sum | max ------+------+----------+-----+----- | | 3 | 271 | 2 1 | 1 | 0 | 147 | 2 1 | 3 | 0 | 14 | 3 | 4 | 0 | 17 | 3 | 3 | 0 | 16 | 1 | 2 | 0 | 25 | 2 2 | 2 | 0 | | 2 4 | 1 | 0 | 37 | 2 | 3 | 0 | 15 | (9 rows) ```PDF page 64
#### 3.2.17 SET statement {#pg19-3-2-17-set-statement} It is now possible to specify a NULL value in SET statements and ALTER SYSTEM SET statements. [[ff4597a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ff4597acd4c3d957373335034e1ac16b6584c3e6)] **Example 67.** Specifying NULL values in the SET statement ```text postgres=> SHOW search_path ; search_path ----------------- "$user", public (1 row) postgres=> SET search_path TO NULL ; SET postgres=> SHOW search_path ; search_path ------------- (1 row) ``` #### 3.2.18 VACUUM statement {#pg19-3-2-18-vacuum-statement} The following information has been added to the output of the VACUUM VERBOSE statement. ##### Memory Information {#pg19-p64-memory-information} Memory information beginning with "memory usage:" is output. [[736f754e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=736f754eed01ca81198b6cd7421321088cbe5ded)]PDF page 65
**Example 68.** Memory Information ```text postgres=> VACUUM (VERBOSE) data1 ; INFO: vacuuming "postgres.public.data1" … WAL usage: 185293 records, 92311 full page images, 613464473 bytes, 576198816 full page image bytes, 1416 memory usage: dead item storage 2.58 MB accumulated across 1 reset (limit 64.00 MB each) system usage: CPU: user: 0.33 s, system: 0.95 s, elapsed: 32.05 s VACUUM ``` ##### Parallel Worker Information {#pg19-p65-parallel-worker-information} Information about the parallel workers used by the VACUUM statement is output. [[adcdbe9](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=adcdbe93860b16a069c6834062e56e731c54a1a1)] **Example 69.** Parallel Worker Information ```text postgres=> VACUUM (PARALLEL 2, VERBOSE) data1 ; INFO: vacuuming "postgres.public.data1" INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 2) INFO: finished vacuuming "postgres.public.data1": index scans: 1 … item identifiers removed parallel workers: index vacuum: 1 planned, 1 launched in total index "data1_pkey": pages: 27421 in total, 0 newly deleted, 0 currently deleted, 0 reusable … system usage: CPU: user: 0.11 s, system: 1.35 s, elapsed: 5.66 s VACUUM ``` #### 3.2.19 WAIT FOR LSN statement {#pg19-3-2-19-wait-for-lsn-statement} The WAIT FOR LSN statement, a new SQL syntax, has been added to PostgreSQL 19. This SQL statement is mainly executed on standby instances in a streaming replication environment. When the WAIT FOR LSN statement is executed, it waits until the specified LSN is applied. [[447aae1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=447aae13b0305780e87cac7b0dd669db6fab3d9d), [3b4e53a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3b4e53a075ea5671b075f8fd873241179f8e64af), [49a181b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=49a181b5d634340fcfb7c762c387c03f6405367e)]PDF page 66
#### Syntax 15 - WAIT FOR LSN statement {#pg19-syntax-15} ```sql WAIT FOR LSN 'lsn' [WITH (option [, …])] ``` The following options are available. ##### MODE 'mode' {#pg19-p66-mode-mode} Specify the mode for how long to wait. The following values can be specified. "primary_flush" is specified on the primary server; all others are specified on the standby server. **Table 25.** MODE Option Settings | Setting value | Description | Note | | --- | --- | --- | | primary_flushstandby_replaystandby_writestandby_flush | Primary server flush Apply WAL to the standby server Write WAL to the standby server Flush WAL on the standby server | On the primary server | ##### TIMEOUT 'timeout' {#pg19-p66-timeout-timeout} Specify the maximum wait time. If no value is specified or if zero (0) is specified, this statement will wait indefinitely. ##### NO_THROW {#pg19-p66-no-throw} Even if a timeout occurs, no error is thrown.PDF page 67
**Example 70.** Executing the WAIT FOR LSN statement ```text postgres=> WAIT FOR LSN '0/0301EF70' ; status --------- success (1 row) postgres=> WAIT FOR LSN '0/0301FF00' WITH (TIMEOUT '10s') ; ERROR: timed out while waiting for target LSN 0/0301FF00 to be replayed; current standby_replay LSN 0/0301EF70 postgres=> WAIT FOR LSN '0/0301FF00' WITH (TIMEOUT '10s', NO_THROW) ; status --------- timeout (1 row) ``` ```text The WAIT FOR LSN statement returns the following value. ``` **Table 26.** Return value of the WAIT FOR LSN statement | Return value | Description | Note | | --- | --- | --- | | success | The specified LSN has been reached | | | timeout | The wait time has exceeded the timeout | | | not in recovery | The executed instance is not in recovery state | | #### 3.2.20 Functions {#pg19-3-2-20-functions} The following functions have been added or enhanced. ##### Window Functions {#pg19-p67-window-functions} It is now possible to specify the IGNORE NULLS clause (ignore NULL values) and the RESPECT NULLS clause (include NULL values) for the window functions LEAD, LAG, FIRST_VALUE, LAST_VALUE, and NTH_VALUE. The default is RESPECT NULLS. [[25a30bb](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=25a30bbd4235a49c854036c84fe90f2bc5a87652)]PDF page 68
**Example 71.** Specifying the IGNORE NULLS clause and the RESPECT NULLS clause ```text postgres=> SELECT LEAD(col1) IGNORE NULLS OVER (), LAG(col1) RESPECT NULLS OVER () FROM data1 ; lead | lag -------+------- 2 | 3 | 1 4 | 2 5 | 3 6 | 4 … ``` ##### Function encode and decode {#pg19-p68-function-encode-and-decode} It is now possible to specify base64url and base32hex as encoding formats. [[e1d9171](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e1d917182c1953b16b32a39ed2fe38e3d0823047), [497c117](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=497c1170cb108cd84fe13db19c049177376fec5f)] **Example 72.** Base64 URL encoding and Base32 hex encoding ```text postgres=> SELECT encode('\x69b73eff', 'base64url'), decode('abc-_w', 'base64url') ; encode | decode --------+------------ abc-_w | \x69b73eff (1 row) postgres=> SELECT encode('\x1122334455', 'base32hex'), decode('24H36H2L', 'base32hex') ; encode | decode ----------+-------------- 24H36H2L | \x1122334455 (1 row) ``` ##### Function error_on_null {#pg19-p68-function-error-on-null} The error_on_null function has been added; it returns an error if the input value is NULL, and returns the input value if it is not NULL. [[2b75c38](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2b75c38b707a070922231de667a0bd08ee71b268)]PDF page 69
#### Syntax 16 - Function error_on_null {#pg19-syntax-16} ```sql anyelement error_on_null(anyelement) ``` **Example 73.** Execution of the error\_on\_null function ```text postgres=> SELECT error_on_null(NULL::INT) ; ERROR: null value not allowed ``` ##### Function random {#pg19-p69-function-random} Variations of the random function that specify maximum and minimum values now support the date, timestamp, and timestamp with time zone types. [[faf071b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=faf071b553830d39fc583beabcaf56ed65259acc)] #### Syntax 17 - Function random variations {#pg19-syntax-17} ```sql date random(min date, max date) timestamp random(min timestamp, max timestamp) timestamptz random(min timestamptz, max timestamptz) ``` **Example 74.** Variations of the random function ```text postgres=> SELECT random('2024-09-09'::date, '2025-09-09'::date), random('2024-09-09'::timestamptz, '2025-09-09'::timestamptz) ; random | random ------------+------------------------------- 2025-02-04 | 2025-02-16 07:24:48.518771+09 (1 row) ``` ##### Function range_minus_multi and multirange_minus_multi {#pg19-p69-function-range-minus-multi-and-multirange-minus-multi} The range_minus_multi and multirange_minus_multi functions have been added to remove ranges from a range and return multiple ranges, respectively. [[5eed8ce](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5eed8ce50ce9df1067b95593dde9f4fc526dfc72)] #### Syntax 18 - Function range_minus_multi and multirange_minus_multi {#pg19-syntax-18} ```sql SETOF anyrange range_minus_multi(anyrange, anyrange) SETOF anymultirange multirange_minus_multi(anymultirange, anymultirange) ```PDF page 70
**Example 75.** The range\_minus\_multi function and the multirange\_minus\_multi function ```text postgres=> SELECT range_minus_multi(numrange(1.0, 3.0), numrange(1.5, 2.0)) ; range_minus_multi ------------------- [1.0,1.5) [2.0,3.0) (2 rows) postgres=> SELECT multirange_minus_multi(nummultirange(numrange(1,4)), nummultirange(numrange(2,3))) ; multirange_minus_multi ------------------------ {[1,2),[3,4)} (1 row) ``` ##### Function tid_block and tid_offset {#pg19-p70-function-tid-block-and-tid-offset} The tid_block and tid_offset functions have been added to retrieve the block number and row offset from the CTID. [[df6949c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=df6949ccf7a6d5160e3068ed843eb46ab7bc2601)] #### Syntax 19 - Function tid_block and tid_offset {#pg19-syntax-19} ```sql bigint tid_block(tid) integer tid_offset(tid) ``` **Example 76.** Executing the tid\_block and tid\_offset functions ```text postgres=> SELECT ctid, tid_block(ctid), tid_offset(ctid), col1 FROM data1 ; ctid | tid_block | tid_offset | col1 -------+-----------+------------+------ (0,1) | 0 | 1 | 100 (0,2) | 0 | 2 | 200 (2 rows) ```PDF page 71
##### Function width_bucket {#pg19-p71-function-width-bucket} It is now possible to specify NaN as the first parameter, operand, of the width_bucket function. The following SQL statement would have caused an error in previous versions. [[7374b3a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7374b3a53635cb031b4d1bedfd80531409f54693)] **Example 77.** Executing the width\_bucket function ```text postgres=> SELECT width_bucket('NaN', 3.0, 4.0, 888) ; width_bucket -------------- 889 (1 row) ``` ##### DDL Export {#pg19-p71-ddl-export} Functions have been added to output DDL for databases, tablespaces, and roles. [[4881981](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4881981f92024e4db6249bd3dc96a3859638a665), [76e514e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=76e514ebb4b55f95b844e3e94e61fa64ab16fd0b), [b99fd9f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b99fd9fd7f363c6363f4c8a95c699b9134b0357f), [a4f774c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a4f774cf1c7e5c6cf2f3393f611e1df16cdb5a5a)] #### Syntax 20 - Export DDL functions {#pg19-syntax-20} ```sql SETOF text pg_get_database_ddl(regdatabase, VARIADIC text DEFAULT NULL::text) SETOF text pg_get_tablespace_ddl(name, VARIADIC text DEFAULT NULL::text) SETOF text pg_get_role_ddl(regrole, VARIADIC text DEFAULT NULL::text) ``` The following settings can be specified in the options. **Table 27.** Available options | Option | Data type | Description | Note | | --- | --- | --- | --- | | pretty | boolean | Formatted output | | | owner | boolean | Include owner | Cannot be specified with pg_get_role_ddl | | tablespacememberships | boolean | Include tablespace | pg_get_database_ddl only | | | boolean | Include member | pg_get_role_ddl only |PDF page 72
**Example 78.** Retrieving DDL for a database ```text postgres=> SELECT pg_get_database_ddl('postgres', 'pretty', 'true') ; pg_get_database_ddl -------------------------------------------- CREATE DATABASE postgres + WITH TEMPLATE = template0 + ENCODING = 'UTF8' + LOCALE_PROVIDER = libc + LOCALE = 'C'; ALTER DATABASE postgres OWNER TO postgres; (2 rows) ``` ##### Function pg_clear_extended_stats {#pg19-p72-function-pg-clear-extended-stats} The pg_clear_extended_stats function, which clears a table's extended statistics, has been added. [[d756fa1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d756fa1019ff6131944ab263a93a14c4e3b68c39)] #### Syntax 21 - Function pg_clear_extended_stats {#pg19-syntax-21} ```sql void pg_clear_extended_stats(schemaname text, relname text, statistics_schemaname text, statistics_name text, inherited boolean) ``` **Example 79.** Executing of the pg\_clear\_extended\_stats ```text postgres=> CREATE STATISTICS data1_stat1 ON col1,col2 FROM data1 ; CREATE STATISTICS postgres=> SELECT pg_clear_extended_stats('public', 'data1', 'public', 'data1_stat1', true) ; -[ RECORD 1 ]-----------+- pg_clear_extended_stats | ``` ##### Function pg_get_multixact_stats {#pg19-p72-function-pg-get-multixact-stats} The pg_get_multixact_stats function, which retrieves the status of multi-transactions, has been added. This function returns the following values. [[97b1017](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=97b101776ce23dd6c4abbdae213806bc24ed6133)]PDF page 73
**Table 28.** Return value of the pg\_get\_multixact\_stats function | Column name | Data type | Description | | --- | --- | --- | | num_mxids | bigint | Multi-transaction IDs in use | | num_members | bigint | Size of multi-transaction ID members in use | | members_size | bigint | Storage size used by members | | oldest_multixact | xid | Oldest multi-transaction ID | **Example 80.** Execution of the pg\_get\_multixact\_stats function ```text postgres=# SELECT * FROM pg_get_multixact_stats() ; num_mxids | num_members | members_size | oldest_multixact -----------+-------------+--------------+------------------ 1 | 2 | 0 | 1 (1 row) ``` ##### Function pg_get_sequence_data {#pg19-p73-function-pg-get-sequence-data} The page_lsn column, which indicates the LSN, has been added to the output results. [[b93172c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b93172ca59f4ae317b2b9dfbf7f342b237101256)] **Example 81.** Executing of the pg\_get\_sequence\_data function ```text postgres=> SELECT * FROM pg_get_sequence_data('seq01') ; last_value | is_called | page_lsn ------------+-----------+------------ 4 | t | 3/0701C0A0 (1 row) ``` ##### Function pg_get_publication_tables {#pg19-p73-function-pg-get-publication-tables} The pg_get_publication_tables function now includes a variant that allows table information to be specified. [[fd7a25a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fd7a25af11e2cad4f48ffc4e50f18644e657ed53)] #### Syntax 22 - Function pg_get_publication_tables with table id {#pg19-syntax-22} ```sql SETOF record pg_get_publication_tables(pubnames text[], target_relid oid) ```PDF page 74
**Example 82.** Executing of the pg\_get\_publication\_tables function ```text postgres=> SELECT * FROM pg_get_publication_tables('{pub1}', 'data1'::regclass::oid) ; pubid | relid | attrs | qual -------+-------+-------+------ 16405 | 16399 | 1 2 | (1 row) ``` ##### Function pg_replication_origin_session_setup {#pg19-p74-function-pg-replication-origin-session-setup} The pid parameter has been added to specify the worker process ID using origin. The default value is 0, which indicates the first process ID. [[5b14870](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5b148706c5c8ffffe5662fe569a0f0bcef2351d9)] [[5b14870](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5b148706c5c8ffffe5662fe569a0f0bcef2351d9)] **Example 83.** Definition of the pg\_replication\_origin\_session\_setup function ```text postgres=> \dfS pg_replication_origin_session_setup List of functions -[ RECORD 1 ]-------+-------------------------------------- Schema | pg_catalog Name | pg_replication_origin_session_setup Result data type | void Argument data types | node_name text, pid integer DEFAULT 0 Type | func ``` ##### Function pg_restore_extended_stats {#pg19-p74-function-pg-restore-extended-stats} The pg_restore_extended_stats function, which restores a table's extended statistics, has been added. [[0e80f3f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0e80f3f88deaefc03e5fd204190daab6f761e73d), [302879b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=302879bd68d1156fa27c38d29763ca9e4a1649c4), [ba97bf9](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ba97bf9cb7b4ca184ab669569be2633a46cfdd0c)] #### Syntax 23 - Function pg_restore_extended_stats {#pg19-syntax-23} ```sql boolean pg_restore_extended_stats(VARIADIC kwargs "any") ```PDF page 75
**Example 84.** Executing of the pg\_restore\_extended\_stats function ```text postgres=> CREATE STATISTICS stat1_data1 ON col1,col2 FROM data1 ; CREATE STATISTICS postgres=> SELECT pg_restore_extended_stats( 'schemaname', 'public', 'relname', 'data1', 'statistics_schemaname', 'public', 'statistics_name', 'stat1_data1', 'inherited', false, 'n_distinct', '[{"attributes" : [1,2], "ndistinct" : 4}]'::pg_ndistinct) ; -[ RECORD 1 ]-------------+-- pg_restore_extended_stats | t ``` ##### Function pg_stat_get_backend_wal {#pg19-p75-function-pg-stat-get-backend-wal} The pg_stat_get_backend_wal function now includes wal_fpi_bytes (of type numeric), which indicates the number of FPI bytes, in its return value. [[f9a09aa](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f9a09aa2952039a9956b44d929b9df74d62a4cd4)] ##### Function pg_sync_replication_slots {#pg19-p75-function-pg-sync-replication-slots} The pg_sync_replication_slots function now includes a feature that retries until synchronization is complete. [[0d2d4a0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0d2d4a0ec3eca64e7f5ce7f7630b56a561b2663c)] #### 3.2.21 JSON {#pg19-3-2-21-json} The following enhancements regarding JSON data have been implemented. ##### Added jsonpath methods {#pg19-p75-added-jsonpath-methods} The following jsonpath methods have been added. [[bd4f879](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bd4f879a9cdd11b6b683cea0ea51d7c27a8f1153)]PDF page 76
**Table 29.** Added string methods | Method name | Description | Note | | --- | --- | --- | | ltrim / rtrim / btrim lower upper initcap replace split_part | Remove leading and trailing characters Convert to lowercase Convert to uppercase Capitalize the first letter and convert the rest to lowercase Convert the specified string Return elements split by the specified string | | **Example 85.** Executing the newly added jsonpath string method ```text postgres=> SELECT jsonb_path_query('"xTRIMx"'::jsonb, '$.btrim("x")') ; -[ RECORD 1 ]----+------- jsonb_path_query | "TRIM" postgres=> SELECT jsonb_path_query('"LOWER"'::jsonb, '$.lower()') ; -[ RECORD 1 ]----+-------- jsonb_path_query | "lower" postgres=> SELECT jsonb_path_query('"upper"'::jsonb, '$.upper()') ; -[ RECORD 1 ]----+-------- jsonb_path_query | "UPPER" postgres=> SELECT jsonb_path_query('"iNITCaP"'::jsonb, '$.initcap()') ; -[ RECORD 1 ]----+---------- jsonb_path_query | "Initcap" postgres=> SELECT jsonb_path_query('"REpLACE"'::jsonb, '$.replace("p","P")') ; -[ RECORD 1 ]----+---------- jsonb_path_query | "REPLACE" postgres=> SELECT jsonb_path_query('"test@data"'::jsonb, '$.split_part("@",2)') ; -[ RECORD 1 ]----+------- jsonb_path_query | "data" ```PDF page 77
##### IS JSON predicate {#pg19-p77-is-json-predicate} It is now possible to specify the IS JSON predicate for the domain. In previous versions, the IS JSON predicate was only available for the text, json, jsonb, and bytea data types. [[3b4c2b9](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3b4c2b9db25ee1c51e5133da529668a37aa02fc3)] **Example 86.** IS JSON Predicate for the Domain ```text postgres=> CREATE DOMAIN jd1 AS jsonb CHECK ((VALUE ->'val')::text <> '3') ; CREATE DOMAIN postgres=> SELECT '{"val": 1}'::jd1 IS JSON ; ?column? ---------- t (1 row) postgres=> SELECT '{"val": 3}'::jd1 IS JSON ; ERROR: value for domain jd1 violates check constraint "jd1_check" ``` #### 3.2.22 Data Types {#pg19-3-2-22-data-types} The following extensions have been implemented for data types in PostgreSQL 19. ##### Data type regdatabase {#pg19-p77-data-type-regdatabase} The regdatabase data type, which represents a database OID, has been added. To retrieve the OID of the current database, it can be written as follows. [[bd09f02](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bd09f024a1bbdd7a7e2ca944595a9d4b6c90fb83)] **Example 87.** Querying by regdatabase data type ```text postgres=> SELECT * FROM pg_shdepend WHERE dbid = current_database()::regdatabase ; dbid | classid | objid | objsubid | refclassid | refobjid | deptype ------+---------+-------+----------+------------+----------+--------- 5 | 2615 | 2200 | 0 | 1260 | 16384 | a (1 row) ``` ##### Data type oid8 {#pg19-p77-data-type-oid8} The oid8 data type, which represents 8-byte OIDs, has been added. [[b139bd3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b139bd3b6ef000ab5d00dd47128e366a726da5f9)]PDF page 78
**Example 88.** Data type oid8 ```text postgres=> SELECT 9999999999::oid8 ; oid8 ------------ 9999999999 (1 row) ``` ##### Converting between the UUID and BYTEA types {#pg19-p78-converting-between-the-uuid-and-bytea-types} The uuid and bytea types can now be converted between each other. [[ba21f5b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=ba21f5bf8aff277aa1659a51d26109e0914df182;hp=1811f1af98fb237fdd5adb588cd4b57c433b75f8)] **Example 89.** Data type conversion between UUID and BYTEA ```text postgres=> SELECT '\x019a2f859ced7225b99d9c55044a2563'::bytea::uuid ; uuid -------------------------------------- 019a2f85-9ced-7225-b99d-9c55044a2563 (1 row) postgres=> SELECT '5b35380a-7143-4912-9b55-f322699c6770'::uuid::bytea ; bytea ------------------------------------ \x5b35380a714349129b55f322699c6770 (1 row) ``` ##### GiST Indexes of Type inet and cidr {#pg19-p78-gist-indexes-of-type-inet-and-cidr} The operator classes for GiST indexes of type inet and cidr have been changed to inet_ops and cidr_ops, respectively. As a result of this change, the btree_gist extension is no longer required. The following CREATE INDEX statements will result in an error in PostgreSQL 18 and earlier. The pg_upgrade command will fail if the older database contains the gist_inet_ops or gist_cidr_ops operator classes. [[b352d3d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b352d3d80b94269be6e627b5223902fe785d766f)]PDF page 79
**Example 90.** GiST indexes for inet and cidr types ```text postgres=> CREATE TABLE data1(col1 INET, col2 CIDR) ; CREATE TABLE postgres=> CREATE INDEX idx1_data1 ON data1 USING gist (col1) ; CREATE INDEX postgres=> CREATE INDEX idx2_data1 ON data1 USING gist (col2) ; CREATE INDEX ``` ##### Tsearch-related data types {#pg19-p79-tsearch-related-data-types} The analysis now uses the database's default collation. Previous versions used the database's CTYPE setting. [[e113f9c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e113f9c102b7e2462facf0ecffc97f8093efed54)] #### 3.2.23 PL/pgSQL {#pg19-3-2-23-pl-pgsql} The following features have been added to the PL/pgSQL module. ##### Optimizing Simple SELECT INTO Statements {#pg19-p79-optimizing-simple-select-into-statements} The execution of the SELECT INTO statement that uses a simple scalar expression to assign to a single variable is no longer treated as an SQL statement execution; instead, it is now converted into an assignment operation. Due to this update, the target SQL statement will no longer trigger hooks in the pg_stat_statements module. [[ce8d5fe](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ce8d5fe0e2802158b65699aeae1551d489948167)] ##### Reserved Word Removal {#pg19-p79-reserved-word-removal} EXECUTE and STRICT have been removed from the reserved word list. These words can now be used as variable names and for other purposes. [[999f172](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=999f172ded2bae7efbd8bf1dd6f823095395493f)] **Example 91.** Using reserved keywords as variable names ```text postgres=> DO $$ DECLARE EXECUTE INT; BEGIN EXECUTE := 100; END; $$; DO postgres=> ```PDF page 80
#### 3.2.24 PL/Python {#pg19-3-2-24-pl-python} Events trigger support has been added. [[53eff47](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=53eff471c69dc8b0c01f046d3fdcc460eb90d0e5)] #### 3.2.25 Optimizer {#pg19-3-2-25-optimizer} The faster execution plan is now selected. ##### Eager Aggregation {#pg19-p80-eager-aggregation} Eager aggregation optimization has been implemented to reduce the number of input tuples in join operations by performing partial aggregation before the join. This optimization is controlled by the enable_eager_aggregate parameter. The default value is "on", meaning it is enabled. The group size required to consider eager aggregation is specified by the min_eager_agg_group_size parameter. The default value for this parameter is 8. [[8e11859](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8e11859102f947e6145acdd809e5cdcdfbe90fa5)] **Example 92.** PostgreSQL 18 Execution Plan ```text postgres=> EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.col1, avg(t2.col3) FROM eager1 AS t1 JOIN eager2 AS t2 ON t1.col2 = t2.col2 GROUP BY t1.col1 ORDER BY t1.col1 ; QUERY PLAN ------------------------------------------------------- GroupAggregate Output: t1.col1, avg(t2.col3) Group Key: t1.col1 -> Sort Output: t1.col1, t2.col3 Sort Key: t1.col1 -> Hash Join Output: t1.col1, t2.col3 Hash Cond: (t2.col2 = t1.col2) -> Seq Scan on public.eager2 t2 Output: t2.col1, t2.col2, t2.col3 -> Hash Output: t1.col1, t1.col2 -> Seq Scan on public.eager1 t1 Output: t1.col1, t1.col2 (15 rows) ```PDF page 81
**Example 93.** PostgreSQL 19 Execution Plan ```text postgres=> EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.col1, avg(t2.col3) FROM eager1 AS t1 JOIN eager2 AS t2 ON t1.col2 = t2.col2 GROUP BY t1.col1 ORDER BY t1.col1 ; QUERY PLAN ------------------------------------------------------------------- Finalize GroupAggregate Output: t1.col1, avg(t2.col3) Group Key: t1.col1 -> Sort Output: t1.col1, (PARTIAL avg(t2.col3)) Sort Key: t1.col1 -> Hash Join Output: t1.col1, (PARTIAL avg(t2.col3)) Hash Cond: (t1.col2 = t2.col2) -> Seq Scan on public.eager1 t1 Output: t1.col1, t1.col2, t1.col3 -> Hash Output: t2.col2, (PARTIAL avg(t2.col3)) -> Partial HashAggregate Output: t2.col2, PARTIAL avg(t2.col3) Group Key: t2.col2 -> Seq Scan on public.eager2 t2 Output: t2.col1, t2.col2, t2.col3 (18 rows) ``` ##### Using Memoize with ANTI JOIN {#pg19-p81-using-memoize-with-anti-join} The memoization feature is now available for SEMI JOIN and ANTI JOIN. [[0da29e4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0da29e4cb161f78a5ef534b3fb4467756a422e25)]PDF page 82
**Example 94.** PostgreSQL 18 Execution Plan ```text postgres=> EXPLAIN (COSTS OFF, BUFFERS OFF) SELECT COUNT(*) FROM data1 d1 LEFT JOIN LATERAL (SELECT DISTINCT ON (c1) c1, c2, d1.c1 AS x FROM data1 d2) d2 ON d1.c1 + 1 = d2.c1 WHERE d2.c1 IS NULL ; QUERY PLAN ------------------------------------------------------ Aggregate -> Nested Loop Anti Join -> Seq Scan on data1 d1 -> Subquery Scan on d2 Filter: ((d1.c1 + 1) = d2.c1) -> Unique -> Sort Sort Key: d2_1.c1 -> Seq Scan on data1 d2_1 (9 rows) ```PDF page 83
**Example 95.** PostgreSQL 19 Execution Plan ```text postgres=> EXPLAIN (COSTS OFF, BUFFERS OFF) SELECT COUNT(*) FROM data1 d1 LEFT JOIN LATERAL (SELECT DISTINCT ON (c1) c1, c2, d1.c1 AS x FROM data1 d2) d2 ON d1.c1 + 1 = d2.c1 WHERE d2.c1 IS NULL ; QUERY PLAN ------------------------------------------------------------ Aggregate -> Nested Loop Anti Join -> Seq Scan on data1 d1 -> Memoize Cache Key: (d1.c1 + 1), d1.c1 Cache Mode: binary -> Subquery Scan on d2 Filter: ((d1.c1 + 1) = d2.c1) -> Unique -> Sort Sort Key: d2_1.c1 -> Seq Scan on data1 d2_1 (12 rows) ``` ##### Rewriting the COUNT function {#pg19-p83-rewriting-the-count-function} If possible, the COUNT(any) clause is rewritten as a COUNT(*) clause. This rewrite is limited to cases where a non-NULL column is specified and there is no ORDER BY or DISTINCT clause. This change reduces overhead and improves performance. [[42473b3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=42473b3b31238b15cc3c030b4416b2ee79508d8c)]PDF page 84
**Example 96.** PostgreSQL 18 Execution Plan ```text postgres=> EXPLAIN (VERBOSE) SELECT COUNT(col1) FROM data1 ; QUERY PLAN ---------------------------------------------------------------------------- Aggregate (cost=17906.00..17906.01 rows=1 width=8) Output: count(col1) -> Seq Scan on public.data1 (cost=0.00..15406.00 rows=1000000 width=4) Output: col1, col2 (4 rows) ``` **Example 97.** PostgreSQL 19 Execution Plan ```text postgres=> EXPLAIN (VERBOSE) SELECT COUNT(col1) FROM data1 ; QUERY PLAN ---------------------------------------------------------------------------- Aggregate (cost=17906.00..17906.01 rows=1 width=8) Output: count(*) -> Seq Scan on public.data1 (cost=0.00..15406.00 rows=1000000 width=0) Output: col1, col2 (4 rows) ``` ##### Parallelization of TID Range Scans {#pg19-p84-parallelization-of-tid-range-scans} It is now possible to execute TID Range Scans using parallel queries. [[0ca3b16](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0ca3b16973a8bb1c185f56e65edcadc0d9d2c406)] **Example 98.** PostgreSQL 18 Execution Plan ```text postgres=> EXPLAIN SELECT * FROM data1 WHERE ctid > '(10,0)' AND ctid < '(30,1)'; QUERY PLAN ----------------------------------------------------------------- Tid Range Scan on data1 (cost=0.01..61.00 rows=3700 width=10) TID Cond: ((ctid > '(10,0)'::tid) AND (ctid < '(30,1)'::tid)) (2 rows) ```PDF page 85
**Example 99.** PostgreSQL 19 Execution Plan ```text postgres=> EXPLAIN SELECT * FROM data1 WHERE ctid > '(10,0)' AND ctid < '(30,1)'; QUERY PLAN ------------------------------------------------------------------------------ Gather (cost=0.01..33.25 rows=3700 width=10) Workers Planned: 4 -> Parallel Tid Range Scan on data1 (cost=0.01..33.25 rows=925 width=10) TID Cond: ((ctid > '(10,0)'::tid) AND (ctid < '(30,1)'::tid)) (4 rows) ``` ##### Partial Hash Index {#pg19-p85-partial-hash-index} HASH indexes created as partial indexes can now be used in execution plans when a WHERE clause is specified that matches their conditions. This feature has been backported to PostgreSQL 14 and later. [[5528e8d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5528e8d1046062698b7db179e4280817eaeb5f30)] **Example 100.** PostgreSQL 18 Execution Plan ```text postgres=> CREATE INDEX idx1_data1 ON data1 USING hash (col2) WHERE col2=1000 ; CREATE INDEX postgres=> EXPLAIN SELECT * FROM data1 WHERE col2=2000 ; QUERY PLAN ---------------------------------------------------------- Seq Scan on data1 (cost=0.00..17906.00 rows=1 width=14) Filter: (col2 = 2000) (2 rows) ``` **Example 101.** PostgreSQL 19 Execution Plan ```text postgres=> CREATE INDEX idx1_data1 ON data1 USING hash (col2) WHERE col2=1000 ; CREATE INDEX postgres=> EXPLAIN SELECT * FROM data1 WHERE col2=1000 ; QUERY PLAN ------------------------------------------------------------------------- Index Scan using idx1_data1 on data1 (cost=0.00..8.02 rows=1 width=14) Index Cond: (col2 = 1000) (2 rows) ```PDF page 86
##### Optimization of the IS DISTINCT FROM clause {#pg19-p86-optimization-of-the-is-distinct-from-clause} The IS DISTINCT FROM clause for columns with a NOT NULL constraint is now converted to the "<>" operator. [[0a37961](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0a379612540cc51e54dc1c0cc4b9ef8797d2533c)] **Example 102.** PostgreSQL 18 Execution Plan ```text postgres=> EXPLAIN SELECT * FROM data1 WHERE col1 IS DISTINCT FROM col1 ; QUERY PLAN --------------------------------------------------------------- Seq Scan on data1 (cost=0.00..17906.00 rows=995000 width=10) Filter: (col1 IS DISTINCT FROM col1) (2 rows) ``` **Example 103.** PostgreSQL 19 Execution Plan ```text postgres=> EXPLAIN SELECT * FROM data1 WHERE col1 IS DISTINCT FROM col1 ; QUERY PLAN --------------------------------------------------------------- Seq Scan on data1 (cost=0.00..17906.00 rows=995000 width=10) Filter: (col1 <> col1) (2 rows) ``` ##### Anti-Join Transformation of the NOT IN Clause {#pg19-p86-anti-join-transformation-of-the-not-in-clause} If it is determined to be safe, the NOT IN clause uses an Anti-Join in the execution plan. [[383eb21](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=383eb21ebffe9ddd97dca03e529fa363580e7ccf)]PDF page 87
**Example 104.** PostgreSQL 18 Execution Plan ```text postgres=> EXPLAIN SELECT * FROM data1 WHERE col1 NOT IN (SELECT col1 FROM data1 WHERE col1 < 10000) ; QUERY PLAN -------------------------------------------------------------------------- Seq Scan on data1 (cost=194.99..364.97 rows=5000 width=8) Filter: (NOT (ANY (col1 = (hashed SubPlan 1).col1))) SubPlan 1 -> Seq Scan on data1 data1_1 (cost=0.00..169.99 rows=9999 width=4) Filter: (col1 < 10000) (5 rows) ``` **Example 105.** PostgreSQL 19 Execution Plan ```text postgres=> EXPLAIN SELECT * FROM data1 WHERE col1 NOT IN (SELECT col1 FROM data1 WHERE col1 < 10000) ; QUERY PLAN ---------------------------------------------------------------------------- Hash Anti Join (cost=29.98..48.71 rows=1 width=8) Hash Cond: (data1.col1 = data1_1.col1) -> Seq Scan on data1 (cost=0.00..14.99 rows=999 width=8) -> Hash (cost=17.49..17.49 rows=999 width=4) -> Seq Scan on data1 data1_1 (cost=0.00..17.49 rows=999 width=4) Filter: (col1 < 10000) (6 rows) ``` ##### Details of the Result Node {#pg19-p87-details-of-the-result-node} Information about the source of the result has been added to the Result node. As a result, information from the "Replace:" node will now be included in the execution results of the EXPLAIN statement. [[f2bae51](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f2bae51dfd5b2edc460c86071c577a45a1acbfd7)]PDF page 88
**Example 106.** PostgreSQL 19 Execution Plan ```text postgres=> EXPLAIN (COSTS OFF) SELECT * FROM data1 WHERE col1=(SELECT MAX(col1) FROM data1) ; QUERY PLAN ----------------------------------------------------------------------------… Index Scan using data1_pkey on data1 Index Cond: (col1 = (InitPlan expr_1).col1) InitPlan expr_1 -> Result Replaces: MinMaxAggregate InitPlan minmax_1 -> Limit -> Index Only Scan Backward using data1_pkey on data1 … (8 rows) ```