PDF page 35
This section explains new features related to SQL statements. #### 3.2.1 NUMERIC data type {#pg15-3-2-1-numeric-data-type} Negative numbers can now be specified for NUMERIC type scales. If a negative number is specified, it will be rounded to the specified digit. Also, the range of values that can be specified for the scale has been extended from -1,000 to 1,000. **Example 37.** Negative number for NUMERIC ```text postgres=> CREATE TABLE type1( c1 NUMERIC(5, -2) ) ; CREATE TABLE postgres=> INSERT INTO type1 VALUES (12345.678) ; INSERT 0 1 postgres=> INSERT INTO type1 VALUES (1234567.89) ; INSERT 0 1 postgres=> INSERT INTO type1 VALUES (12345678.901) ; ERROR: numeric field overflow DETAIL: A field with precision 5, scale -2 must round to an absolute value less than 10^7. postgres=> INSERT INTO type1 VALUES (-1234567.89) ; INSERT 0 1 postgres=> SELECT * FROM type1 ; c1 ---------- 12300 1234600 ``` ```text https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=085f931f52494e1f304e35571924efa ``` ```text -1234600 ``` ```text 6fcdc2b44 ``` ```text (3 rows) ```PDF page 36
#### 3.2.2 ALTER DATABASE {#pg15-3-2-2-alter-database} The syntax for tracking the version of a collation has been added to the ALTER DATABASE statement. Syntax ```sql ALTER DATABASE database_name REFRESH COLLATION VERSION ``` Added a new function pg_database_collation_actual_version to get the collation version and a datcollversion column for the version in the pg_database view. If it detects that the collation version of the database is different from the operating system, a warning will be output when connecting to the corresponding database. Below is an example of migration from Red Hat Enterprise Linux 7.8 to Red Hat Enterprise Linux 8.6. **Example 38.** Refresh collation version ```sql $ psql demodb WARNING: database "demodb" has a collation version mismatch DETAIL: The database was created using collation version 58.0.6.50, but the operating system provides version 153.80. HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE demodb REFRESH COLLATION VERSION, or build PostgreSQL with the right library version. psql (15.0) Type "help" for help. demodb=# ALTER DATABASE demodb REFRESH COLLATION VERSION ; NOTICE: changing version from 58.0.6.50 to 153.80 ALTER DATABASE ``` #### 3.2.3 ALTER TABLE {#pg15-3-2-3-alter-table} The SET ACCESS METHOD clause can now be specified in ALTER TABLE statements that change the access method for a table; the SET ACCESS METHOD clause can also be specified in ALTER MATERIALIZED VIEW statements.PDF page 37
Syntax - ALTER TABLE table_name SET ACCESS METHOD method_name - ALTER MATERIALIZED VIEW view_name SET ACCESS METHOD method_name **Example 39.** Execute ALTER TABLE statement ```text postgres=> ALTER TABLE data1 SET ACCESS METHOD heap ; ALTER TABLE ``` ```sql A table_rewrite trigger is fired when you execute an ALTER TABLE SET ACCESS METHOD statement on a materialized view. ``` #### 3.2.4 COPY {#pg15-3-2-4-copy} The following enhancements have been added to the COPY statement. ##### COPY TO statement {#pg15-p37-copy-to-statement} The format 'text' and header 'true' can now be used simultaneously in the COPY TO statement options. At the same time, the file_fdw extension can now enable headers in text format. **Example 40.** Text Format and Header ```text postgres=# CREATE EXTENSION file_fdw ; CREATE EXTENSION postgres=# CREATE SERVER textsvr FOREIGN DATA WRAPPER file_fdw ; CREATE SERVER postgres=# CREATE FOREIGN TABLE text1(c1 INT, c2 VARCHAR(10)) SERVER textsvr OPTIONS (filename '/tmp/data.txt', format 'text', header 'true') ; CREATE FOREIGN TABLE postgres=# COPY data1 TO '/home/postgres/data1.txt' (FORMAT text, HEADER true) ; COPY 100 postgres=# \! cat data1.txt c1 c2 1 data1 … ```PDF page 38
##### COPY FROM statement {#pg15-p38-copy-from-statement} The HEADER option can now be specified as 'match'. When this option is specified, COPY FROM checks that the header row and column names match when the COPY FROM statement is executed. In the example below, the first row of the CSV file and the column names in the table are different, resulting in an error. **Example 41.** Header check ```text postgres=# \! cat /tmp/csvlog1.csv c1,c3 1,data1 2,data2 3,data3 postgres=# COPY csvlog1 FROM '/tmp/csvlog1.csv' WITH (format 'csv', header 'match') ; ERROR: column name mismatch in header line field 2: got "c3", expected "c2" CONTEXT: COPY csvlog1, line 1: "c1,c3" ``` #### 3.2.5 CLUSTER {#pg15-3-2-5-cluster} The CLUSTER statements can now be executed on partitioned tables. **Example 42.** Clustering of partition tables ```text postgres=> CREATE TABLE part1 (c1 INT PRIMARY KEY, c2 VARCHAR(10)) PARTITION BY LIST(c1) ; CREATE TABLE postgres=> CLUSTER part1 USING part1_pkey ; CLUSTER ``` #### 3.2.6 CREATE DATABASE {#pg15-3-2-6-create-database} The following enhancements have been implemented for the CREATE DATABASE statement ##### ICU Locale {#pg15-p38-icu-locale} The CREATE DATABASE statement now includes a LOCALE_PROVIDER clause to specify the locale provider, an ICU_LOCALE clause to specify the ICU locale, and a COLLATION_VERSION clause to specify the collation version.PDF page 39
**Example 43.** COLLATION\_VERSION の指定 ```text postgres=# CREATE DATABASE icudb1 LOCALE_PROVIDER=icu ICU_LOCALE=en COLLATION_VERSION='58.0.6.50' TEMPLATE=template0 ; CREATE DATABASE ``` ##### STRATEGY clause {#pg15-p39-strategy-clause} The STRATEGY clause has been added to the CREATE DATABASE statement to specify how the database should be created; specifying WAL_LOG in the STRATEGY clause will cause the new database to be copied from the template block by block, with information for each block output to WAL. This is an effective method when the template is small and is the default value. Specify FILE_COPY to perform the behavior of the previous version. **Example 44.** Specify STRATEGY clause ```text postgres=# CREATE DATABASE strategydb1 STRATEGY WAL_LOG ; CREATE DATABASE ``` ```text The option --strategy, which corresponds to the STRATEGY clause, has also been added to the createdb command. ``` ##### OID clause {#pg15-p39-oid-clause} The object ID (OID) of the database to be created can now be specified; the OID can be any free number greater than 16384. **Example 45.** Specify OID clause ```text postgres=# CREATE DATABASE oiddb1 OID=17000 ; CREATE DATABASE ``` #### 3.2.7 CREATE TABLE {#pg15-3-2-7-create-table} Column lists can now be specified in the ON DELETE SET NULL and SET DEFAULT clauses of foreign key definitions in CREATE TABLE and ALTER TABLE statements. In the previous version, it was not possible to specify columns. The confdelsetcols column has been added to the pg_constraint catalog.PDF page 40
**Example 46.** ON Specify DELETE SET NULL clause ```text postgres=> CREATE TABLE pktable1 ( tid INT, id INT, name1 VARCHAR(10), PRIMARY KEY (tid, id)) ; CREATE TABLE postgres=> CREATE TABLE fktable1 ( tid INT, id INT, fk_id_del_set_null INT, fk_id_del_set_default INT DEFAULT 0, FOREIGN KEY (tid, fk_id_del_set_null) REFERENCES pktable1 ON DELETE SET NULL (fk_id_del_set_null), FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES pktable1 ON DELETE SET DEFAULT (fk_id_del_set_default) ) ; CREATE TABLE ``` #### 3.2.8 CREATE UNIQUE INDEX {#pg15-3-2-8-create-unique-index} It is now possible to specify NULLS DISTINCT and NULLS NOT DISTINCT for the unique index attribute. Multiple null values can be stored in a unique index with the NULLS DISTINCT clause. If nothing is specified, the NULLS DISTINCT specification is the default. An index with the NULLS NOT DISTINCT clause cannot contain multiple null values. An indnullsnotdistinct column has been added to the pg_index catalog to indicate this attribute. Syntax - CREATE UNIQUE INDEX index_name ON … NULLS [NOT] DISTINCT - CONSTRAINT constraint_name NULLS [NOT] DISTINCTPDF page 41
**Example 47.** Create UNIQUE INDEX with NULLS DISTINCT clause ```text postgres=> CREATE TABLE data1(c1 INT, c2 INT, c3 VARCHAR(10)) ; CREATE TABLE postgres=> CREATE UNIQUE INDEX idx1_data1 ON data1(c1) NULLS DISTINCT ; CREATE INDEX postgres=> CREATE UNIQUE INDEX idx2_data1 ON data1(c2) NULLS NOT DISTINCT ; CREATE INDEX postgres=> INSERT INTO data1 VALUES (1, 1, 'data1') ; INSERT 0 1 postgres=> INSERT INTO data1 VALUES (2, NULL, 'data2') ; INSERT 0 1 postgres=> INSERT INTO data1 VALUES (NULL, 3, 'data3') ; INSERT 0 1 postgres=> INSERT INTO data1 VALUES (NULL, NULL, 'data4') ; ERROR: duplicate key value violates unique constraint "idx2_data1" DETAIL: Key (c2)=(null) already exists. postgres=> INSERT INTO data1 VALUES (NULL, 5, 'data5') ; INSERT 0 1 ``` ```sql Similar specifications can be made for the unique constraints of the CREATE TABLE and ALTER TABLE statements. ``` **Example 48.** UNIQUE constraint ```text postgres=> CREATE TABLE const1(c1 INT UNIQUE NULLS NOT DISTINCT, c2 INT) ; CREATE TABLE postgres=> ALTER TABLE const1 ADD CONSTRAINT uniq1 UNIQUE NULLS DISTINCT (c2) ; ALTER TABLE ``` #### 3.2.9 CREATE SEQUENCE {#pg15-3-2-9-create-sequence} The UNLOGGED clause can now be specified in the CREATE SEQUENCE statement to suppress WAL output. The sequence created by the CREATE UNLOGGED SEQUENCE statement has the repersistence column in the pg_class catalog as 'u'. The attributes of the sequence can be changed with the ALTER SEQUENCE SET LOGGED statement or the ALTER SEQUENCE SET UNLOGGED statement.PDF page 42
**Example 49.** Create UNLOGGED SEQUENCE ```text postgres=> CREATE UNLOGGED SEQUENCE seq1 ; CREATE SEQUENCE postgres=> \d seq1 Unlogged sequence "public.seq1" Type | Start | Minimum | Maximum | Increment | Cycles? | Cache --------+-------+---------+---------------------+-----------+---------+------- bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1 postgres=> SELECT relname, relpersistence FROM pg_class WHERE relname = 'seq1' ; relname | relpersistence ---------+---------------- seq1 | u (1 row) postgres=> ALTER SEQUENCE seq1 SET LOGGED ; ALTER SEQUENCE ``` ```text If the instance crashes, UNLOGGED SEQUENCE will be reset to its default value. ``` #### 3.2.10 CREATE VIEW {#pg15-3-2-10-create-view} The security_invoker option has been added to the CREATE VIEW statement. By default, access to the table from which the view is based is enforced with the privileges of the view owner. When the security_invoker option is set to True for a view, access to the table is subject to the privileges of the user accessing the view. This option can also be specified in the ALTER VIEW statement. Syntax - CREATE VIEW view_name WITH (security_invoker = true|false) AS sql_clause - ALTER VIEW view_name SET (security_invoker = true|false) - ALTER VIEW view_name RESET (security_invoker) **Example 50.** Security\_invoker option ```text postgres=> CREATE VIEW view1 WITH(security_invoker) AS SELECT * FROM data1 ; CREATE VIEW ```PDF page 43
#### 3.2.11 EXPLAIN {#pg15-3-2-11-explain} I/O time for temporary files is now output when BUFFERS and VERBOSE are specified in JSON format. **Example 51.** Output of temporary file I\/O time ```text postgres=# SET track_io_timing = on ; SET postgres=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) SELECT * FROM data1 ; QUERY PLAN ---------------------------------------------------- [ + … "I/O Write Time": 0.000, + "Temp I/O Read Time": 0.000, + "Temp I/O Write Time": 0.000 + } … ``` #### 3.2.12 GRANT {#pg15-3-2-12-grant} Roles can now be granted permission to change specific parameters through the ALTER SYSTEM statement. Syntax ```text GRANT {ALTER SYSTEM | SET} ON PARAMETER parameter_name TO role_spec REVOKE {ALTER SYSTEM | SET} ON PARAMETER parameter_name FROM role_spec ```PDF page 44
**Example 52.** Execute GRANT ALTER SYSTEM statement ```text postgres=# GRANT ALTER SYSTEM ON PARAMETER log_statement TO demo ; GRANT postgres=# SELECT * FROM pg_parameter_acl ; -[ RECORD 1 ]----------------------------------- oid | 16385 parname | log_statement paracl | {postgres=sA/postgres,demo=A/postgres} postgres=# \connect postgres demo You are now connected to database "postgres" as user "demo". postgres=> ALTER SYSTEM SET log_statement = 'all' ; ALTER SYSTEM ``` #### 3.2.13 MERGE {#pg15-3-2-13-merge} The MERGE statement that simultaneously executes INSERT / DELETE / UPDATE statements on a table from a join condition is now supported. Tuples matching the join condition can be specified in the WHEN MATCHED clause to update (UPDATE) or delete (DELETE) or do nothing (DO NOTHING). Additional conditions may be specified. The behavior of tuples that do not match the condition is specified in the WHEN NOT MATCHED clause. Syntax ```sql MERGE INTO target_table [AS alias] USING source_table [AS alias] ON join_clause WHEN MATCHED [AND condition] THEN UPDATE SET … | DELETE | DO NOTHING WHEN NOT MATCHED INSERT VALUES … | DO NOTHING ```PDF page 45
**Example 53.** Execute MERGE statement ```text postgres=> MERGE INTO dst1 AS d USING src1 AS s ON d.c1 = s.c1 WHEN MATCHED THEN UPDATE SET c2 = s.c2 WHEN NOT MATCHED THEN INSERT VALUES (s.c1, s.c2) ; MERGE 2 postgres=> MERGE INTO dst1 AS d USING src1 AS s ON d.c1 = s.c1 WHEN MATCHED AND s.c1 < 1000 THEN DELETE WHEN NOT MATCHED THEN DO NOTHING ; MERGE 2 ``` ```sql The MERGE statement cannot be used in the WITH clause or at the same time as the COPY statement. ``` **Example 54.** Execute MERGE statement and WITH clause ```text postgres=> WITH data1 AS ( MERGE INTO dst1 USING src1 ON (true) WHEN MATCHED THEN DELETE ) SELECT * FROM data1 ; ERROR: MERGE not supported in WITH query LINE 1: WITH data1 AS ( ^ ``` #### 3.2.14 VACUUM {#pg15-3-2-14-vacuum} The execution log of the VACUUM VERBOSE statement has changed significantly. Average read rate, buffer usage, WAL output information, etc. have been added. Details on how pg_class.relfrozenxid and pg_class.relminmxid have progressed are also now reported.PDF page 46
**Example 55.** Execute VACUUM VERBOSE statement ```text postgres=> VACUUM VERBOSE data1 ; INFO: vacuuming "postgres.public.data1" INFO: finished vacuuming "postgres.public.data1": index scans: 1 pages: 0 removed, 5406 remain, 5406 scanned (100.00% of total) tuples: 500000 removed, 500000 remain, 0 are dead but not yet removable removable cutoff: 757, which was 0 XIDs old when operation ended … avg read rate: 0.000 MB/s, avg write rate: 0.068 MB/s buffer usage: 18975 hits, 0 misses, 1 dirtied WAL usage: 18953 records, 0 full page images, 4007392 bytes system usage: CPU: user: 0.11 s, system: 0.00 s, elapsed: 0.11 s INFO: vacuuming "postgres.pg_toast.pg_toast_32787" … ``` #### 3.2.15 Functions {#pg15-3-2-15-functions} The following functions have been added/extended. ##### MAX / MIN {#pg15-p46-max-min} MAX function and MIN function for xid8 type have been implemented. ##### Replication related functions {#pg15-p46-replication-related-functions} The following function to get replication information has been added. These functions can be executed by a user with the SUPERUSER attribute or the pg_monitor role. **Table 15.** Added functions | Function name | Description | | --- | --- | | pg_ls_logicalmapdir | Returns information in the pg_logical/mappings directory | | pg_ls_logicalsnapdir | Returns information in the pg_logical/snapshots directory | | pg_ls_replslotdir | Returns information in the pg_replslot/SLOTNAMEdirectory |PDF page 47
**Example 56.** Get Logical Replication information ```text postgres=# SELECT pg_create_physical_replication_slot('slot1') ; pg_create_physical_replication_slot ------------------------------------- (slot1,) (1 row) postgres=# SELECT * FROM pg_ls_replslotdir('slot1') ; name | size | modification -------+------+------------------------ state | 200 | 2022-10-14 09:52:04+09 (1 row) postgres=# SELECT * FROM pg_ls_logicalsnapdir() ; name | size | modification ----------------+------+------------------------ 0-3005558.snap | 128 | 2022-10-14 00:01:29+09 (1 row1) postgres=# SELECT * FROM pg_ls_logicalmapdir() ; name | size | modification ------+------+-------------- (0 rows) ``` ```text Internally, it uses the READ_REPLICATION_SLOT command to read the replication slot information. ``` ##### Pg_log_backend_memory_contexts {#pg15-p47-pg-log-backend-memory-contexts} The check for SUPERUSER permission has been removed, and it can now be used by general users by granting permission using the GRANT statement. ##### Regular expression functions {#pg15-p47-regular-expression-functions} The regexp_count, regexp_instr, and regexp_substr have been added as functions that handle regular expressions. Parameters that can be specified have been added to the regexp_replace function.PDF page 48
**Table 16.** Added\/changed functions | Function name | Description | | --- | --- | | regexp_count | Returns the number of times the pattern is matched. | | regexp_instr | Returns the location that matches the pattern. | | regexp_like | Returns whether it matches the pattern. | | regexp_replace | Replace the part that matches the pattern. Added start and flags options. | | regexp_substr | Returns the part that matches the pattern. | Syntax - integer REGEXP_COUNT (string text, pattern text [, start integer [, flags ```text text ]]) ``` - integer REGEXP_INSTR (string text, pattern text [, start integer [, N integer ```text [, endpos integer [, flags text [, subexpr integer ]]]]]) ``` - boolean REGEXP_LIKE (string text, pattern text [, flags text ]) - text REGEXP_REPLACE (string text, pattern text, replace text start integer, ```text N integer [, flags text]) ``` - text REGEXP_SUBSTR (string text, pattern text [, start integer [, N intger ```text [, flags text [, subexpr integer ]]]]) ```PDF page 49
**Example 57.** Execution of regular expression functions ```text postgres=> SELECT regexp_count('ABCABC', 'Abc', 1, 'i') ; -[ RECORD 1 ]+-- regexp_count | 2 postgres=> SELECT regexp_like('PostgreSQL', 'Postgre(s|S)QL') ; -[ RECORD 1 ]-- regexp_like | t postgres=> SELECT regexp_instr('ABCDEFGHT', 'D.F') ; -[ RECORD 1 ]+-- regexp_instr | 4 postgres=> SELECT regexp_replace('PostgreSQL', 'a|e|i|o|u', 'X', 1, 2) ; -[ RECORD 1 ]--+----------- regexp_replace | PostgrXSQL postgres=> SELECT regexp_substr('ABCDEFGHT', 'D.F', 2) ; -[ RECORD 1 ]-+---- regexp_substr | DEF ``` ##### Unnest {#pg15-p49-unnest} The MULTIRANGE type can now be specified for UNNEST functions. Syntax ```text setof anyrange UNNNEST(anymultirange) ``` **Example 58.** Execution of the unnest function ```text postgres=> SELECT unnest(int4multirange(int4range('5', '6'), int4range('1', '2'))) ; unnest -------- [1,2) [5,6) (2 rows) ```PDF page 50
##### Pg_size_bytes / pg_size_pretty {#pg15-p50-pg-size-bytes-pg-size-pretty} It now supports up to petabytes. **Example 59.** Execution of the pg\_size\_pretty function ```text postgres=> SELECT pg_size_pretty(pg_size_bytes('10.5 PB')) ; pg_size_pretty ---------------- 11 PB (1 row) ``` ##### Starts_with {#pg15-p50-starts-with} The starts_with function can now use BTREE indexes. At the same time, the ^@ operator will do the same. **Example 60.** Execution of the starts\_with function ```text postgres=> EXPLAIN SELECT * FROM data1 WHERE c2 ^@ '999' ; QUERY PLAN ------------------------------------------------------------------------------ Index Scan using idx2_data1 on data1 (cost=0.42..1337.98 rows=100 width=12) Index Cond: ((c2 >= '999'::text) AND (c2 < '99:'::text)) Filter: (c2 ^@ '999'::text) (3 rows) postgres=> EXPLAIN SELECT * FROM data1 WHERE starts_with(c2, '999') ; QUERY PLAN ------------------------------------------------------------------------------ Index Scan using idx2_data1 on data1 (cost=0.42..1337.98 rows=100 width=12) Index Cond: ((c2 >= '999'::text) AND (c2 < '99:'::text)) Filter: starts_with(c2, '999'::text) (3 rows) ``` ##### Pg_settings_get_flags {#pg15-p50-pg-settings-get-flags} Attribute information of GUC is returned as an array of text. If a non-existent GUC name is specified, NULL is returned.PDF page 51
**Example 61.** Execution of the pg\_settings\_get\_flags function ```text postgres=> SELECT * FROM pg_settings_get_flags('session_authorization') ; pg_settings_get_flags ------------------------------------------ {NO_RESET_ALL,NO_SHOW_ALL,NOT_IN_SAMPLE} (1 row) postgres=> SELECT * FROM pg_settings_get_flags('bad_guc_name') ; pg_settings_get_flags ----------------------- null (1 row) ``` ##### Time specified character string {#pg15-p51-time-specified-character-string} OF, TZH and TZM can now be used in lowercase. **Example 62.** Specify \&\#x27\;of\&\#x27\;\, \&\#x27\;tzh\&\#x27\;\, \&\#x27\;tzm\&\#x27\; ```text postgres=> SELECT to_char(current_date, 'of / tzh / tzm') ; to_char ---------------- +09 / +09 / 00 (1 row) ``` ##### Poly_distance {#pg15-p51-poly-distance} The poly_distance function has been implemented. This function existed in previous versions, but when executed it returned an ERRCODE_FEATURE_NOT_SUPPORTED error. **Example 63.** Execute the poly\_distance function ```text postgres=> SELECT poly_distance('((10, 10),(20,20))'::polygon, '((30,30),(40,40))'::polygon) ; poly_distance -------------------- 14.142135623730951 (1 row) ```