PDF page 42
This section explains new features related to SQL statements. #### 3.2.1 Data Types {#pg16-3-2-1-data-types} The following extensions have been implemented for data types: [[2ceea5a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2ceea5adb02603ef52579b568ca2c5aebed87358), [6fcda9a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6fcda9aba83449082124825b6d375c0a61e21c42), [102a5c1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=102a5c164a91d717632f3a24f1289a5fa4861973), [6dfacbf](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6dfacbf72b53b775e8442a7fd2fca7c24b139773), [faff8f8](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=faff8f8e47f18c7d589453e2e0d841d2bd96c1ac), [096dd80](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=096dd80f3ccc103c8e078fca05e6ccfb2071aa91)] ##### +infinity notation {#pg16-p42-infinity-notation} The +infinity value can now be specified for date, timestamp, and timestamp with time zone types. +infinity is considered the same as infinity. **Example 39.** \+Infinity notation ```text postgres=> SELECT '+infinity'::timestamp ; timestamp ----------- infinity (1 row) postgres=> SELECT '+infinity'::timestamptz ; timestamptz ------------- infinity (1 row) ``` ##### Integer Literals {#pg16-p42-integer-literals} Integer literals can now be represented in hexadecimal, octal, and binary. Each is represented as follows. Specify a leading '0' followed by a symbol indicating the base. The case is not sensitive. This change corresponds to the SQL:202x draft. **Table 21.** Integer literal description | Designation method | Example | Note | | --- | --- | --- | | HexadecimalOctal | 0x42F 0o273 | | | Binary number | 0b100101 | |PDF page 43
**Example 40.** Notation for integer literals ```text postgres=> SELECT * FROM data1 WHERE c1 IN ( 100, 0b11001000, 0o454, 0x190 ) ORDER BY 1 ; c1 | c2 -----+------- 100 | data1 200 | data2 300 | data3 400 | data4 (4 rows) ``` ##### JSONPATH literal {#pg16-p43-jsonpath-literal} Hexadecimal, octal, and binary representations can also be specified in JSONPATH. **Example 41.** Integer literal in the JSONPATH ```text postgres=> SELECT '0b100101'::jsonpath ; jsonpath ---------- 37 (1 row) postgres=> SELECT '0o273'::jsonpath ; jsonpath ---------- 187 (1 row) postgres=> SELECT '0x42F'::jsonpath ; jsonpath ---------- 1071 (1 row) ```PDF page 44
##### Numeric type {#pg16-p44-numeric-type} The numeric type supports hexadecimal, octal, and binary representations. However, it cannot be written below the decimal point. **Example 42.** Hexadecimal notation of type NUMERIC ```text postgres=> SELECT pg_input_is_valid('0x1234', 'numeric') ; pg_input_is_valid ------------------- t (1 row) postgres=> SELECT message FROM pg_input_error_info('0x1234.56', 'numeric(10, 3)') ; message ---------------------------------------------------- invalid input syntax for type numeric: "0x1234.56" (1 row) ``` ##### Numeric literals and underscores {#pg16-p44-numeric-literals-and-underscores} Underscores (_) can be specified in numeric literals. This change corresponds to the SQL:202x draft. **Example 43.** Specifying underscores in the numeric literals ```text postgres=> SELECT 1_000 dec, 2_000.234 dec2, 0x100_200 hex, 0o2_467 oct, 0b01_101 bin1, 0b_1001_0001 bin2 ; -[ RECORD 1 ]-- dec | 1000 dec2 | 2000.234 hex | 1049088 oct | 1335 bin1 | 13 bin2 | 145 ```PDF page 45
#### 3.2.2 COPY {#pg16-3-2-2-copy} The following features have been implemented for the COPY statement: [[9f8377f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9f8377f7a27910bf0f35bf5169a8046731948a79), [97da482](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=97da48246d34807196b404626f019c767b7af0df)] ##### Insert default value {#pg16-p45-insert-default-value} The option DEFAULT have been added to the COPY FROM statement. If the value specified for this option matches the input value, then the DEFAULT value for the column will be entered. If there is no DEFAULT specification for the column and there is data that matches the DEFAULT option of the COPY statement, an error will occur. **Example 44.** Specify the DEFAULT option ```text postgres=> CREATE TABLE data1(c1 INT PRIMARY KEY, c2 TEXT DEFAULT 'DefVal1') ; CREATE TABLE postgres=> COPY data1 FROM stdin WITH (format csv, default '\D') ; 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. >> 1,data1 >> 2,\D >> 3,data3 >> COPY 3 postgres=> SELECT * FROM data1 ; c1 | c2 ----+--------- 1 | data1 2 | DefVal1 3 | data3 (3 rows) ``` ```text This option is also available in extension file_fdw. When referencing a FOREIGN TABLE and the value in the file matches the DEFAULT option, the DEFAULT value for the column is referenced. ```PDF page 46
**Example 45.** Specifying the DEFAULT option \(file\_fdw\) ```text postgres=# \! cat /tmp/data1.csv 1,data1 2,\D 3,data3 postgres=# CREATE FOREIGN TABLE file1(c1 INT, c2 TEXT DEFAULT 'DefVal1') SERVER fsvr1 OPTIONS (filename '/tmp/data1.csv', format 'csv', default '\D') ; CREATE FOREIGN TABLE postgres=# SELECT * FROM file1 ; c1 | c2 ----+--------- 1 | data1 2 | DefVal1 3 | data3 (3 rows) ``` ##### Effective range of the batch_size option {#pg16-p46-effective-range-of-the-batch-size-option} The batch_size option of foreign server (FOREIGN SERVER) and foreign table (FOREIGN TABLE) can now be used when executing the COPY statement for a foreign table. Previously, it was valid only when executing the INSERT statement. #### 3.2.3 CREATE ROLE/USER {#pg16-3-2-3-create-role-user} Roles/users with the CREATEROLE attribute will be able to grant other users only their own attributes and roles granted with the ADMIN OPTION. The CREATEDB attribute has the same behavior as in older versions. [[f1358ca](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f1358ca52dd7b8cedd29c6f2f8c163914f03ea2e)]PDF page 47
**Example 46.** User creation by the CREATEROLE attribute user ```text postgres=# CREATE USER usradm1 CREATEROLE REPLICATION CREATEDB ; CREATE ROLE postgres=# \connect postgres usradm1 You are now connected to database "postgres" as user "usradm1". postgres=> CREATE USER user_repl1 REPLICATION ; CREATE ROLE postgres=> CREATE USER user_db1 CREATEDB ; CREATE ROLE postgres=> CREATE USER user_rls1 BYPASSRLS ; ERROR: permission denied to create role DETAIL: Only roles with the BYPASSRLS attribute may create roles with the BYPASSRLS attribute. ``` ```text The user who creates a role automatically becomes a member of the created role. However, it is not possible to automatically inherit (or SET ROLE) privileges. To change this behavior, specify SET and INHERIT in the createrole_self_grant parameter separated by a comma (,). ``` **Example 47.** SET ROLE statement for creation role ```text postgres=> CREATE ROLE role1 ; CREATE ROLE postgres=> SET ROLE role1 ; ERROR: permission denied to set role "role1" postgres=> SET createrole_self_grant = 'SET' ; SET postgres=> CREATE ROLE role2 ; CREATE ROLE postgres=> SET ROLE role2 ; SET ``` #### 3.2.4 CREATE STATISTICS {#pg16-3-2-4-create-statistics} The name of the statistic is no longer mandatory. If the name is omitted, the statistic name is automatically generated from the original table name and column name. [[624aa2a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=624aa2a13bd02dd584bb0995c883b5b93b2152df)]PDF page 48
**Example 48.** Name abbreviation in the CREATE STATISTICS statement ```text postgres=> CREATE STATISTICS ON c1, c2 FROM data1 ; CREATE STATISTICS postgres=> \d data1 Table "public.data1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | integer | | not null | c2 | character varying(10) | | | Indexes: "data1_pkey" PRIMARY KEY, btree (c1) Statistics objects: "public.data1_c1_c2_stat" ON c1, c2 FROM data1 ``` #### 3.2.5 CREATE TABLE {#pg16-3-2-5-create-table} The following new features have been added to the CREATE TABLE statement. [[784cedd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=784cedda0604ee4ac731fd0b00cd8b27e78c02d3), [b9424d0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b9424d014e195386a83b0f1fe9f5a8e5727e46ea)] ##### Specification of the STORAGE clause {#pg16-p48-specification-of-the-storage-clause} The STORAGE clause can now be specified in the CREATE TABLE statement. In previous versions, it had to be set with the ALTER TABLE statement.PDF page 49
**Example 49.** STORAGE clause of the CREATE TABLE statement ```text postgres=> CREATE TABLE data1(c1 INT PRIMARY KEY, c2 VARCHAR(10) STORAGE PLAIN, c3 TEXT STORAGE EXTENDED) ; CREATE TABLE postgres=> \d+ data1 Table "public.data1" Column | Type | Collation | Nullable | Default | Storage |… --------+-----------------------+-----------+----------+---------+----------+… c1 | integer | | not null | | plain |… c2 | character varying(10) | | | | plain |… c3 | text | | | | extended |… Indexes: "data1_pkey" PRIMARY KEY, btree (c1) Access method: heap ``` ##### DEFAULT Storage {#pg16-p49-default-storage} The STORGE clause of the CREATE TABLE and ALTER TABLE statements can be DEFAULT to indicate the default storage format. **Example 50.** STORAGE DEFAULT clause in the ALTER TABLE statement ```text postgres=> ALTER TABLE data1 ALTER COLUMN c2 SET STORAGE DEFAULT ; ALTER TABLE ``` #### 3.2.6 EXPLAIN {#pg16-3-2-6-explain} Add GENERIC_PLAN option to show the execution plan for parameterized query strings. [[3c05284](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3c05284d83b230728e59a25e828992037ef77096)]PDF page 50
**Example 51.** Setting the GENERIC\_PLAN option ```text postgres=> EXPLAIN (GENERIC_PLAN) SELECT * FROM data1 WHERE c1=$1 ; QUERY PLAN ------------------------------------------------------------------------- Index Scan using data1_pkey on data1 (cost=0.43..8.45 rows=1 width=10) Index Cond: (c1 = $1) (2 rows) postgres=> EXPLAIN (GENERIC_PLAN FALSE) SELECT * FROM data1 WHERE c1=$1 ; ERROR: there is no parameter $1 LINE 1: ...XPLAIN (GENERIC_PLAN FALSE) SELECT * FROM data1 WHERE c1=$1 ; ^ ``` #### 3.2.7 GRANT {#pg16-3-2-7-grant} The following features have been added to the GRANT statement. [[e3ce2de](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e3ce2de09d814f8770b2e3b3c152b7671bcdb83f), [3d14e17](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3d14e171e9e2236139e8976f3309a588bcc8683b), [ce6b672](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ce6b672e4455820a0348214be0da1a024c3f619f), [c44f633](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c44f6334ca6ff6d242d9eb6742441bc4e1294067)] ##### LOCK TABLE statement {#pg16-p50-lock-table-statement} The execution privilege of the LOCK TABLE statement has been simplified. Table owners, superusers, and holders of the pg_maintain role can execute the LOCK TABLE statement in any mode. The table below shows the relationship between table access privileges and permitted lock modes. **Table 22.** Access privileges and the possible lock modes | Tableaccessprivileges | Allowedlockmode | | --- | --- | | MAINTAIN, UPDATE, DELETE, TRUNCATE Any lock mode | Anylockmode | | INSERT | ROW EXCLUSIVE MODE | | SELECT | ACCESS SHARE MODE | ##### WITH INHERIT clause {#pg16-p50-with-inherit-clause} It is now possible to specify the WITH INHERIT OPTION clause (or WITH INHERI TRUE) that inherits privileges and the WITH INHERIT FALSE clause that does not inherit privileges. The default is WITH INHERIT TRUE. The specified value can be checked in the inherit_option column of the pg_auth_members catalog.PDF page 51
**Example 52.** WITH INHERIT clause ```text postgres=# GRANT pg_read_all_stats TO demo WITH INHERIT FALSE ; GRANT ROLE ``` ##### WITH SET clause {#pg16-p51-with-set-clause} It is now possible to specify the WITH SET OPTION clause (or WITH SET OPTION), which allows the use of the SET ROLE statement, and the WITH SET FALSE clause, which disallows it. The default is WITH SET TRUE. The specified value can be confirmed in the set_option column of the pg_auth_members catalog. **Example 53.** WITH SET clause ```text postgres=# GRANT role1 TO demo WITH SET FALSE ; GRANT ROLE postgres=# SET SESSION AUTHORIZATION demo ; SET postgres=> SET ROLE role1 ; ERROR: permission denied to set role "role1" ``` #### 3.2.8 JSON {#pg16-3-2-8-json} Enhanced JSON related syntax. [[7081ac4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7081ac46ace8c459966174400b53418683c9fe5c), [6ee3020](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6ee30209a6f161d0a267a33f090c70c579c87c00)] ##### JSON Constructor {#pg16-p51-json-constructor} JSON type constructors that are compliant with the SQL/JSON standard have been added. The following functions are availablePDF page 52
#### Syntax 3 - JSON Constructor {#pg16-syntax-3} ```sql json_array ( [ { value_expression [ FORMAT JSON ] } [, ...] ] [ { NULL | ABSENT } ON NULL ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]) json_arrayagg ( [ value_expression ] [ ORDER BY sort_expression ] [ { NULL | ABSENT } ON NULL ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]) json_object ( [ { key_expression { VALUE | ':' } value_expression [ FORMAT JSON [ ENCODING UTF8 ] ] }[, ...] ] [ { NULL | ABSENT } ON NULL ] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]) json_objectagg ( [ { key_expression { VALUE | ':' } value_expression } ] [ { NULL | ABSENT } ON NULL ] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]) ```PDF page 53
**Example 54.** JSON Constructor ```text postgres=> SELECT JSON_ARRAY('a', NULL, 'b' NULL ON NULL RETURNING jsonb) ; json_array ------------------ ["a", null, "b"] (1 row) postgres=> SELECT JSON_ARRAYAGG(i ORDER BY i DESC) FROM generate_series(1,5) i ; json_arrayagg ----------------- [5, 4, 3, 2, 1] (1 row) postgres=> SELECT JSON_OBJECT('a': 2 + 3) ; json_object ------------- {"a" : 5} (1 row) postgres=> SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS) FROM (VALUES (1, 1), (0, NULL), (3, NULL), (2, 2), (4, NULL)) foo(k, v) ; json_objectagg ---------------------- { "1" : 1, "2" : 2 } (1 row) ``` ##### IS JSON Syntax {#pg16-p53-is-json-syntax} Add IS JSON syntax to check conformance to JSON syntax. Multiple syntaxes are provided depending on the type of JSON format.PDF page 54
#### Syntax 4 - IS JSON clause {#pg16-syntax-4} ```sql item IS [NOT] JSON item IS JSON VALUE item IS JSON OBJECT item JS JSON ARRAY item IS JSON SCALAR item IS JSON WITHOUT UNIQUE KEYS item IS JSON WITH UNIQUE KEYS ``` **Example 55.** IS JSON Clause ```text postgres=> SELECT '[1, 2, 3]'::jsonb IS JSON ARRAY ; ?column? ---------- t (1 row) postgres=> SELECT '{"a": 1, "b": null}'::jsonb IS JSON OBJECT ; ?column? ---------- t (1 row) ``` ##### Accept fractional seconds {#pg16-p54-accept-fractional-seconds} Accept fractional seconds in jsonpath's datetime method. This fix is backported to PostgreSQL 13 and later. [[7398e27](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7398e27224f173306e5b62977672b29f5553ee76)] **Example 56.** Fractional seconds ```text postgres=> SELECT jsonb_path_query('"2017-03-10 12:34:56.789+3:10"', '$.datetime()') ; jsonb_path_query --------------------------------- "2017-03-10T12:34:56.789+03:10" ```PDF page 55
#### 3.2.9 REINDEX {#pg16-3-2-9-reindex} The database name can now be omitted in the REINDEX SYSTEM and REINDEX DATABASE statements. If the database name is omitted, the command is executed for the current database. Also, in the REINDEX DATABASE statement, REINDEX processing for the system catalog will not be executed. [[2cbc3c1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2cbc3c17a5c11d13c0ac92fe7557c56408f8f3d0)] **Example 57.** Omitting the database name ```text postgres=# REINDEX DATABASE ; REINDEX postgres=# REINDEX SYSTEM ; REINDEX ``` #### 3.2.10 VACUUM {#pg16-3-2-10-vacuum} The VACUUM statement has the following new features: [[a46a701](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a46a7011b27188af526047a111969f257aaf4db8), [d977ffd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d977ffd923d207164eef78ed107d5293aee6c660), [4211fbd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4211fbd8413b26e0abedbe4338aa7cda2cd469b4), [1cbbee0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1cbbee03385763b066ae3961fc61f2cd01a0d0d7), [1de58df](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1de58df4fec7325d91f5a8345757314be7ac05da)] ##### Database Statistics {#pg16-p55-database-statistics} Add the SKIP_DATABASE_STATS option to the VACUUM statement to suppress updating database statistics. Setting this option to TRUE suppresses scans into the pg_class catalog, improving concurrency. The ONLY_DATABASE_STATS option is now available to retrieve database statistics only. **Example 58.** Execute the VACUUM statement ```text postgres=# VACUUM (SKIP_DATABASE_STATS TRUE) ; VACUUM postgres=# VACUUM (ONLY_DATABASE_STATS TRUE) ; VACUUM ``` ##### TOAST only VACUUM {#pg16-p55-toast-only-vacuum} By specifying the PROCESS_MAIN FALSE, only TOAST data can be VACUUMed. The PROCESS_MAIN option is TRUE by default.PDF page 56
**Example 59.** TOAST only VACUUM ```text postgres=> VACUUM (PROCESS_MAIN FALSE, VERBOSE) data1 ; INFO: vacuuming "postgres.pg_toast.pg_toast_16392" INFO: finished vacuuming "postgres.pg_toast.pg_toast_16392": index scans: 0 pages: 0 removed, 0 remain, 0 scanned (100.00% of total) tuples: 0 removed, 0 remain, 0 are dead but not yet removable removable cutoff: 739, which was 0 XIDs old when operation ended new relfrozenxid: 739, which is 1 XIDs ahead of previous value frozen: 0 pages from table (100.00% of total) had 0 tuples frozen … avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s buffer usage: 2 hits, 0 misses, 0 dirtied WAL usage: 1 records, 0 full page images, 188 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s VACUUM ``` ##### Shared buffer limit {#pg16-p56-shared-buffer-limit} Add the BUFFER_USAGE_LIMIT option to specify the shared buffer usage when executing VACAUUM and ANALYZE statements. The default value if this option is not specified is determined by the parameter vacuum_buffer_usage_limit (default value 256kB). The range of possible values for this option is 128 kB to 16 GB, but the range beyond 1/8 of the parameter shared_buffers is not used. **Example 60.** The BUFFER\_USAGE\_LIMIT option ```text postgres=> SHOW vacuum_buffer_usage_limit ; -[ RECORD 1 ]-------------+------ vacuum_buffer_usage_limit | 256kB postgres=> VACUUM (BUFFER_USAGE_LIMIT '1MB') data1 ; VACUUM ``` ##### Page-level FREEZE {#pg16-p56-page-level-freeze} The FREEZE process, which used to be performed for each tuple, can now be performed for each page. This will perform WAL deduplication and reduce the amount of writes.PDF page 57
#### 3.2.11 Subquery {#pg16-3-2-11-subquery} It is no longer necessary to specify an alias name for subqueries in the FROM clause. [[bcedd8f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bcedd8f5fce0b69970cf0cee7bca560833d05869)] **Example 61.** Subquery with the FROM clause ```text postgres=> INSERT INTO data1 SELECT * FROM (SELECT * FROM data2 WHERE c1<100) ; INSERT 0 99 postgres=> SELECT COUNT(*) FROM (SELECT c2 FROM data1 WHERE c1 = 90) ; count ------- 1 (1 row) ``` #### 3.2.12 Execution Plan {#pg16-3-2-12-execution-plan} Faster execution plans are now chosen in the following cases: [[3c6fc58](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3c6fc58209f24b959ee18f5d19ef96403d08f15c), [ed1a88d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ed1a88ddaccfe883e4cf74d30319accfeae6cfe5), [16dc270](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=16dc2703c5413534d4989e08253e8f4fcb0e2aab), [9bfd282](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9bfd2822b3201f6b0de1e87305b11ee3885b36d9)] ##### SELECT DITINCT statement {#pg16-p57-select-ditinct-statement} Aggregate functions with ORDER BY or DISTINCT clauses can now run more efficiently. This behavior can be controlled with the parameter enable_presorted_aggregate parameter. Setting the value of this parameter to 'off' will make it behave the same as PostgreSQL 15. **Example 62.** PostgreSQL 16 behavior ```text postgres=> EXPLAIN SELECT COUNT(DISTINCT c1) FROM data1 ; QUERY PLAN ------------------------------------------------------------------------------ Aggregate (cost=28480.42..28480.43 rows=1 width=8) -> Index Only Scan using data1_pkey on data1 (cost=0.42..25980.42 rows=1000 000 width=4) (2 rows) ```PDF page 58
**Example 63.** PostgreSQL 15 behavior ```text postgres=> SET enable_presorted_aggregate = off ; SET postgres=> EXPLAIN SELECT COUNT(DISTINCT c1) FROM data1 ; QUERY PLAN --------------------------------------------------------------------- Aggregate (cost=17906.00..17906.01 rows=1 width=8) -> Seq Scan on data1 (cost=0.00..15406.00 rows=1000000 width=4) (2 rows) ``` ##### WINDOW functions {#pg16-p58-window-functions} The execution plans can now be shared for OVER clauses that are similar, such as ROWS UNBOUNDED PRECEDING AND CURRENT ROW and RANGE UNBOUNDED PRECEDING AND CURRENT ROW. **Example 64.** SELECT statement for the example ```sql SELECT empno, depname, ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date) rn, RANK() OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) rnk, DENSE_RANK() OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN CURRENT ROW AND CURRENT ROW) drnk FROM empsalary ; ```PDF page 59
**Example 65.** Execution plan in PostgreSQL 15 ```text QUERY PLAN ---------------------------------------------------- WindowAgg -> WindowAgg -> WindowAgg -> Sort Sort Key: depname, enroll_date -> Seq Scan on empsalary (6 rows) ``` **Example 66.** Execution plan in PostgreSQL 16 ```text QUERY PLAN ---------------------------------------- WindowAgg -> Sort Sort Key: depname, enroll_date -> Seq Scan on empsalary (4 rows) ``` ##### HASH Join / MERGE Join {#pg16-p59-hash-join-merge-join} Hash join and merge join are now supported for the RIGHT ANTI JOIN on non-NULL-valued inputs.PDF page 60
**Example 67.** Execution plan in PostgreSQL 16 ```text postgres=> EXPLAIN (COSTS OFF) SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a ; QUERY PLAN ------------------------------------------------------ Sort Sort Key: t1.a -> Hash Right Anti Join Hash Cond: (t2.b = t1.a) -> Append -> Seq Scan on prt2_adv_p1 t2_1 -> Seq Scan on prt2_adv_p2 t2_2 -> Seq Scan on prt2_adv_p3 t2_3 -> Hash -> Append -> Seq Scan on prt1_adv_p1 t1_1 Filter: (b = 0) -> Seq Scan on prt1_adv_p2 t1_2 Filter: (b = 0) -> Seq Scan on prt1_adv_p3 t1_3 Filter: (b = 0) (16 rows) ```PDF page 61
**Example 68.** Execution plan in PostgreSQL 15 ```text QUERY PLAN ------------------------------------------------------ Sort Sort Key: t1.a -> Hash Anti Join Hash Cond: (t1.a = t2.b) -> Append -> Seq Scan on prt1_adv_p1 t1_1 Filter: (b = 0) -> Seq Scan on prt1_adv_p2 t1_2 Filter: (b = 0) -> Seq Scan on prt1_adv_p3 t1_3 Filter: (b = 0) -> Hash -> Append -> Seq Scan on prt2_adv_p1 t2_1 -> Seq Scan on prt2_adv_p2 t2_2 -> Seq Scan on prt2_adv_p3 t2_3 (16 rows) ``` ##### Memoize by UNION ALL {#pg16-p61-memoize-by-union-all} Memoize can now be used in the Append operation with the UNION ALL clause.PDF page 62
**Example 69.** Execution plan in PostgreSQL 16 ```text postgres=> EXPLAIN (COSTS OFF) SELECT * FROM t1, (SELECT * FROM t2 UNION ALL SELECT * FROM t2) t3 WHERE t1.c1 = t3.c1 ; QUERY PLAN --------------------------------------------------------- Nested Loop -> Seq Scan on t1 -> Memoize Cache Key: t1.c1 Cache Mode: logical -> Append -> Index Scan using t2_c1_idx on t2 Index Cond: (c1 = t1.c1) -> Index Scan using t2_c1_idx on t2 t2_1 Index Cond: (c1 = t1.c1) (10 rows) ``` ##### Improve performance for partition lookups {#pg16-p62-improve-performance-for-partition-lookups} LIST / RANGE partition table now caches partition information if the same partition is found 16 times. Faster searches on tables with many partitions. [[a61b1f7](https://git.postgresql.org/gitweb/postgres.git?p=postgresql.git;a=commit;h=a61b1f74823c9c4f79c95226a461f1e7a367764b), [a61b1f7](https://git.postgresql.org/gitweb/postgres.git?p=postgresql.git;a=commit;h=a61b1f74823c9c4f79c95226a461f1e7a367764b)] #### 3.2.13 Functions {#pg16-3-2-13-functions} The following functions have been added/enhanced. [[888f2ea](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=888f2ea0a81ff171087bdd1c5c1eeda3b78d73d4), [2ddab01](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2ddab010c2777c6a965cea82dc1b809ddc33ecc1), [38d8176](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=38d81760c4d7e22b95252e3545596602c9e38806), [d5d5741](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d5d574146d71c362852748c82ba8b9c873f5bf24), [75bd846](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=75bd846b68b09f33a57d9de96064ded4d392ca05), [75bd846](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=75bd846b68b09f33a57d9de96064ded4d392ca05), [283129e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=283129e325b721a5a62227f20d7e3d149b379c73), [0823d06](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0823d061b0b7f1e20fbfd48bef3c2e093493dbd4), [cca1863](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cca186348929cd75f23ef1b25922386bf38cf99c), [13e0d7a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=13e0d7a603852b8b05c03b45228daabffa0cced2), [10ea0f9](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=10ea0f924a2788f9e701d6213745aaa5ca3efb8a), [1939d26](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1939d26282b27b4b264c6930830a7991ed83917a), [1939d26](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1939d26282b27b4b264c6930830a7991ed83917a), [bf03cfd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bf03cfd162176d543da79f9398131abc251ddbb9), [483bdb2](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=483bdb2afec9e33ff05fd48a00e2656e30e714b7)] ##### Array_sample / array_shuffle {#pg16-p62-array-sample-array-shuffle} Two new functions have been added: array_sample, which returns a specified number of random elements from an array, and array_shuffle, which shuffles the elements of an array. #### Syntax 5 - Array_sample / array_shuffle function {#pg16-syntax-5} ```sql anyarray array_sample(array anyarray, n integer) anyarray array_shuffle(array anyarray) ```PDF page 63
**Example 70.** Array\_sample \/ array\_shuffle function ```text postgres=> SELECT array_sample(ARRAY[1,2,3,4,5,6], 3) ; array_sample -------------- {1,3,6} (1 row) postgres=> SELECT array_shuffle(ARRAY[[1,2],[3,4],[5,6]]) ; array_shuffle --------------------- {{3,4},{1,2},{5,6}} (1 row) ``` ##### Any_value {#pg16-p63-any-value} Add aggregate function any_value. This function non-deterministically returns an arbitrary value from the input values of the aggregate. This function is part of the SQL:2023 standard. #### Syntax 6 - Any_value function {#pg16-syntax-6} ```sql anyelement any_value(anyelement) ``` **Example 71.** Any\_value function ```text postgres=> SELECT * FROM data1 ; c1 | c2 -----+------- 100 | data1 200 | data2 300 | data2 (3 rows) postgres=> SELECT ANY_VALUE(c1), MAX(c1), AVG(c1) FROM data1 ; any_value | max | avg -----------+-----+---------------------- 100 | 300 | 200.0000000000000000 (1 row) ```PDF page 64
##### Date_add / date_subtract {#pg16-p64-date-add-date-subtract} Add functions for adding and subtracting dates and times. The date_add function adds time and the date_subtract function subtracts time. The timezone parameter is optional. #### Syntax 7 - Date_add / date_subtract function {#pg16-syntax-7} ```sql timestamp with time zone date_add(timestamp with time zone, interval [, text]) timestamp with time zone date_subtract(timestamp with time zone, interval [, text] ``` **Example 72.** Date\_add \/ date\_subtract function ```text postgres=> SELECT date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw') ; date_add ------------------------ 2021-11-01 08:00:00+09 (1 row) postgres=> SELECT date_subtract('2021-11-01 00:00:00+01'::timestamptz, '1 day'::interval, 'Europe/Warsaw') ; date_subtract ------------------------ 2021-10-31 07:00:00+09 (1 row) ``` ##### Date_trunc {#pg16-p64-date-trunc} The immutability of functions of the form date_trunc(unit, timestamptz, text) has been changed from stable to immutable. ##### Erf / erfc {#pg16-p64-erf-erfc} Add erf and erfc functions to compute the error function and complementary error. #### Syntax 8 - Erf / erfc functions {#pg16-syntax-8} ```sql double precision erf(double precision) double precision erfc(double precision) ```PDF page 65
**Example 73.** Erf \/ erfc functions ```text postgres=> SELECT erf(1.0), erfc(1.0) ; erf | erfc --------------------+--------------------- 0.8427007929497149 | 0.15729920705028513 (1 row) ``` ##### Generate_series {#pg16-p65-generate-series} Added generate_series function that allows timezone name to be specified. #### Syntax 9 - Generate_series function {#pg16-syntax-9} ```sql timestamp with time zone generate_series(timestamp with time zone, timestamp with time zone, interval, text) ``` **Example 74.** Execution of generate\_series function with specified interval ```text postgres=> SELECT * FROM generate_series('2001-10-22 00:00 -04:00'::timestamptz, '2001-10-24 00:00 -05:00'::timestamptz, '1 day'::interval, 'America/New_York') ; generate_series ------------------------ 2001-10-22 13:00:00+09 2001-10-23 13:00:00+09 2001-10-24 13:00:00+09 (3 rows) ``` ##### Random_normal {#pg16-p65-random-normal} Add function random_normal to generate normally distributed random numbers. Specify the mean and standard deviation. The extension tablefunc already provided the normal_rand function, which has been modified and provided as a system function. #### Syntax 10 - Random_normal function {#pg16-syntax-10} ```sql double precision random_normal(mean double precision DEFAULT 0, stddev double precision DEFAULT 1) ```PDF page 66
**Example 75.** Random\_normal function ```text postgres=> SELECT random_normal(0.0, 1.0) ; random_normal --------------------- -0.1803089817060607 (1 row) ``` ##### Pg_read_file / pg_read_binary_file {#pg16-p66-pg-read-file-pg-read-binary-file} Add a version with a filename and the missing_ok parameter to indicate what to do if the file doesn't exist. #### Syntax 11 - Pg_read_file / Pg_read_binary_file functions {#pg16-syntax-11} ```sql text pg_read_file(filename text, missing_ok boolean) bytea pg_read_binary_file(filename text, missing_ok boolean) ``` **Example 76.** Pg\_read\_file function ```text postgres=# SELECT pg_read_file('PG_VERSION', true) ; pg_read_file -------------- 16 + (1 row) ``` ##### Pg_split_walfile_name {#pg16-p66-pg-split-walfile-name} This function returns the sequence number and timeline id from the WAL file name. #### Syntax 12 - Pg_split_walfile_name function {#pg16-syntax-12} ```sql record pg_split_walfile_name(file_name text) ``` **Example 77.** Pg\_split\_walfile\_name function ```text postgres=# SELECT * FROM pg_split_walfile_name ('000000010000000000000001') ; segment_number | timeline_id ----------------+------------- 1 | 1 (1 row) ```PDF page 67
##### Pg_stat_get_backend_subxact {#pg16-p67-pg-stat-get-backend-subxact} This function retrieves sub transaction information in a particular backend's cache. #### Syntax 13 - Pg_stat_get_backend_subxact function {#pg16-syntax-13} ```sql record pg_stat_get_backend_subxact(bid integer) ``` **Example 78.** Pg\_stat\_get\_backend\_subxact function ```text postgres=# SELECT * FROM pg_stat_get_backend_subxact(5) ; -[ RECORD 1 ]------+-- subxact_count | 0 subxact_overflowed | f ``` ##### Pg_input_is_valid {#pg16-p67-pg-input-is-valid} The pg_input_is_valid function can check whether a given value matches a data type. Overflow digits and improper date strings can be checked in advanced. The input data and data type are specified as strings. #### Syntax 14 - Pg_input_is_valid function {#pg16-syntax-14} ```sql boolean pg_input_is_valid(string text, type text) ``` **Example 79.** Pg\_input\_is\_valid function ```text postgres=> SELECT pg_input_is_valid('1234.48', 'numeric(5, 2)') ; pg_input_is_valid ------------------- f (1 row) postgres=> SELECT pg_input_is_valid('2022/12/32 09:30:60', 'timestamp') ; pg_input_is_valid ------------------- f (1 row) ```PDF page 68
##### Pg_input_error_info {#pg16-p68-pg-input-error-info} The pg_input_error_info function can get the error message, hint string, and error code when the input data is not appropriate for the specified data type. #### Syntax 15 - Pg_input_error_info function {#pg16-syntax-15} ```sql record pg_input_error_info(value text, type_name text) ``` **Example 80.** Pg\_input\_is\_valid \/ pg\_input\_error\_info function ```text postgres=> SELECT message FROM pg_input_error_info('1234.48', 'numeric(5, 2)') ; message ------------------------ numeric field overflow (1 row) postgres=> SELECT hint FROM pg_input_error_info('2022/12/32 09:30:60', 'timestamp') ; hint --------------------------------------------------- Perhaps you need a different "datestyle" setting. (1 row) ``` ##### Pg_size_bytes {#pg16-p68-pg-size-bytes} This function now accepts B (bytes) as the unit. **Example 81.** Pg\_size\_bytes function ```text postgres=> SELECT pg_size_bytes('123456B') "B", pg_size_bytes('123456bytes') "bytes" ; -[ RECORD 1 ]- B | 123456 bytes | 123456 ```PDF page 69
##### Pg_import_system_collations {#pg16-p69-pg-import-system-collations} Now available in Microsoft Windows environment. ##### System_user {#pg16-p69-system-user} The SYSTEM_USER function is a SQL standard reserved word and is a function that returns an authentication method and an authentication ID. Returns NULL for TRUST authentication. #### Syntax 16 - System_user function {#pg16-syntax-16} ```sql text system_user() ``` **Example 82.** System\_user function ```text $ psql -h dbsvr1 -d postgres -U demo Password for user demo: <PDF page 70
**Example 83.** Specify the INDENT clause ```text postgres=> SELECT xmlserialize(DOCUMENT '