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.
Designation method
Example
Note
HexadecimalOctal
0x42F 0o273
Binary number
0b100101
Table 21Integer literal description
PDF page 43
Example 40Notation for integer literals
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
Hexadecimal, octal, and binary representations can also be specified in JSONPATH.
The following features have been implemented for the COPY statement: [9f8377f, 97da482]
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 44Specify the DEFAULT option
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)
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 45Specifying the DEFAULT option (file_fdw)
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
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]
PDF page 47
Example 46User creation by the CREATEROLE attribute user
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.
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 47SET ROLE statement for creation role
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
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]
PDF page 48
Example 48Name abbreviation in the CREATE STATISTICS statement
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
The following new features have been added to the CREATE TABLE statement. [784cedd, b9424d0]
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 49STORAGE clause of the CREATE TABLE statement
The STORGE clause of the CREATE TABLE and ALTER TABLE statements can be DEFAULT to indicate the default storage format.
Example 50STORAGE DEFAULT clause in the ALTER TABLE statement
postgres=> ALTER TABLE data1 ALTER COLUMN c2 SET STORAGE DEFAULT ;
ALTER TABLE
3.2.6 EXPLAIN
Add GENERIC_PLAN option to show the execution plan for parameterized query strings. [3c05284]
PDF page 50
Example 51Setting the GENERIC_PLAN option
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 ;
^
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.
Tableaccessprivileges
Allowedlockmode
MAINTAIN, UPDATE, DELETE, TRUNCATE Any lock mode
Anylockmode
INSERT
ROW EXCLUSIVE MODE
SELECT
ACCESS SHARE MODE
Table 22Access privileges and the possible lock modes
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 52WITH INHERIT clause
postgres=# GRANT pg_read_all_stats TO demo WITH INHERIT FALSE ;
GRANT ROLE
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 53WITH SET clause
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"
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]
Example 57Omitting the database name
postgres=# REINDEX DATABASE ;
REINDEX
postgres=# REINDEX SYSTEM ;
REINDEX
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.
By specifying the PROCESS_MAIN FALSE, only TOAST data can be VACUUMed. The PROCESS_MAIN option is TRUE by default.
PDF page 56
Example 59TOAST only VACUUM
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
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 60The BUFFER_USAGE_LIMIT option
postgres=> SHOW vacuum_buffer_usage_limit ;
-[ RECORD 1 ]-------------+------
vacuum_buffer_usage_limit | 256kB
postgres=> VACUUM (BUFFER_USAGE_LIMIT '1MB') data1 ;
VACUUM
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
It is no longer necessary to specify an alias name for subqueries in the FROM clause. [bcedd8f]
Example 61Subquery with the FROM clause
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)
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 62PostgreSQL 16 behavior
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 63PostgreSQL 15 behavior
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
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.
QUERY PLAN
----------------------------------------
WindowAgg
-> Sort
Sort Key: depname, enroll_date
-> Seq Scan on empsalary
(4 rows)
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 67Execution plan in PostgreSQL 16
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 68Execution plan in PostgreSQL 15
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
Memoize can now be used in the Append operation with the UNION ALL clause.
PDF page 62
Example 69Execution plan in PostgreSQL 16
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
LIST / RANGE partition table now caches partition information if the same partition is found 16 times. Faster searches on tables with many partitions. [a61b1f7, a61b1f7]
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.
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.
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.
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.
This function retrieves sub transaction information in a particular backend’s cache.
Syntax 13 - Pg_stat_get_backend_subxact function
recordpg_stat_get_backend_subxact(bidinteger)
Example 78Pg_stat_get_backend_subxact function
postgres=# SELECT * FROM pg_stat_get_backend_subxact(5) ;
-[ RECORD 1 ]------+--
subxact_count | 0
subxact_overflowed | f
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.
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.
Example 80Pg_input_is_valid / pg_input_error_info function
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
This function now accepts B (bytes) as the unit.
Example 81Pg_size_bytes function
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
Now available in Microsoft Windows environment.
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
textsystem_user()
Example 82System_user function
$ psql -h dbsvr1 -d postgres -U demo
Password for user demo: <<PASSWORD>>
psql (16.0)
Type "help" for help.
postgres=> SELECT system_user ;
system_user
--------------------
scram-sha-256:demo
(1 row)
Xmlserialize
Add the INDENT option (and NO INDENT) to pretty (or not pretty) XML documents.
PDF page 70
Example 83Specify the INDENT clause
postgres=> SELECT xmlserialize(DOCUMENT
'<foo><bar><val x="y">42</val></bar></foo>' AS text INDENT) ;
xmlserialize
-------------------------
<foo> +
<bar> +
<val x="y">42</val>+
</bar> +
</foo> +
(1 row)
postgres=> SELECT xmlserialize(DOCUMENT
'<foo><bar><val x="y">42</val></bar></foo>' AS text NO INDENT) ;
xmlserialize
-------------------------------------------
<foo><bar><val x="y">42</val></bar></foo>
(1 row)