# SQL Statements > SQL syntax, data types, functions, optimizer behavior, and worked examples. --- LLMS index: [llms.txt](/en/llms.txt) ---
GASource pages: PDF 32-56Original PDF on GitHub

PDF page 32

This section explains new features related to SQL statements. #### 3.2.1 ALTER TABLE {#pg12-3-2-1-alter-table} It is now possible to change the attributes of some system catalogs. **Example 26.** Alter system catalog ```text postgres=# SHOW allow_system_table_mods ; allow_system_table_mods ------------------------- on (1 row) postgres=# ALTER TABLE pg_attribute SET (autovacuum_vacuum_scale_factor=0) ; ALTER TABLE ``` #### 3.2.2 ALTER TYPE ADD VALUE {#pg12-3-2-2-alter-type-add-value} The ALTER TYPE ADD VALUE statement is now available within a transaction block. However, the added value cannot be used in the transaction block. **Example 27.** ALTER TYPE ADD VALUE statement in the transaction block ```text postgres=> BEGIN ; BEGIN postgres=> ALTER TYPE t1 ADD VALUE 'v3' ; ALTER TYPE ``` #### 3.2.3 COMMIT/ROLLBACK AND CHAIN {#pg12-3-2-3-commit-rollback-and-chain} It became possible to add CHAIN clause which start a new transaction right after committing or discarding (ROLLBACK) the transaction. Specify the AND CHAIN clause in the COMMIT statement or ROLLBACK statement. Specify "AND NO CHAIN" to explicitly deny the CHAIN clause. These statements can also be used in PROCEDUREs using PL/pgSQL.

PDF page 33

**Example 28.** COMMIT AND CHAIN ```text postgres=> BEGIN ; BEGIN postgres=> INSERT INTO data1 VALUES (100, 'data1') ; INSERT 0 1 postgres=> COMMIT AND CHAIN ; COMMIT postgres=> INSERT INTO data1 VALUES (200, 'data2') ; INSERT 0 1 postgres=> ROLLBACK AND CHAIN ; ROLLBACK postgres=> INSERT INTO data1 VALUES (300, 'data3') ; INSERT 0 1 postgres=> COMMIT ; COMMIT ``` ```text In transactions started with the CHAIN clause, attributes such as transaction isolation level are maintained from the previous transaction. ```

PDF page 34

**Example 29.** Transaction attribute ```text postgres=> SHOW transaction_isolation ; transaction_isolation ----------------------- read committed (1 row) postgres=> BEGIN ISOLATION LEVEL SERIALIZABLE ; BEGIN postgres=> COMMIT AND CHAIN ; COMMIT postgres=> SHOW transaction_isolation ; transaction_isolation ----------------------- serializable (1 row) postgres=> COMMIT ; COMMIT postgres=> SHOW transaction_isolation ; transaction_isolation ----------------------- read committed (1 row) ``` #### 3.2.4 COPY {#pg12-3-2-4-copy} The following enhancements have been made to the COPY statement. ##### COPY FROM statement {#pg12-p34-copy-from-statement} It has become possible to store only the table data that matches the conditions specified in the COPY FROM statement. **Example 30.** COPY FROM WHERE statement ```text postgres=# COPY data1 FROM '/home/postgres/data1.csv' CSV DELIMITER ',' WHERE mod(c1, 2) = 0 ; COPY 50000 ```

PDF page 35

It can be executed similarly by \copy command of psql command. **Example 31.** \\copy WHERE command ```text postgres=> \copy data1 FROM '/home/postgres/data1.csv' CSV DELIMITER ',' WHERE mod(c1, 2) = 0 ; COPY 50000 ``` ##### COPY FREEZE statement {#pg12-p35-copy-freeze-statement} Executing a COPY FREEZE statement on partitioned table results in an error. This specification also applies to PostgreSQL 11.2 and later. **Example 32.** COPY FREEZE statement on PostgreSQL 12 ```text postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY RANGE(c1) ; CREATE TABLE postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES FROM (0) TO (100) ; CREATE TABLE postgres=> CREATE TABLE part1v2 PARTITION OF part1 FOR VALUES FROM (100) TO (200) ; CREATE TABLE postgres=# COPY part1 FROM '/home/postgres/part1.csv' CSV FREEZE ; ERROR: cannot perform FREEZE on a partitioned table ``` #### 3.2.5 CREATE AGGREGATE {#pg12-3-2-5-create-aggregate} It is able to use the OR REPLACE clause in the CREATE AGGREGATE statement. Syntax ```sql CREATE [ OR REPLACE ] AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) ```

PDF page 36

#### 3.2.6 CREATE COLLATION {#pg12-3-2-6-create-collation} DETERMINISTIC can be specified as an additional option in the CREATE COLLATION statement. The default value for this parameter is TRUE. Syntax ```sql CREATE COLLATION [ IF NOT EXISTS] name ( [ LOCALE = locale, ] [ LC_COLLATE = lc_collate, ] [ LC_CTYPE = lc_ctype, ] [ PROVIDER = provider, ] [ DETERMINISTIC = boolean, ] [ VERSION = version ] ) ``` #### 3.2.7 CREATE INDEX {#pg12-3-2-7-create-index} ##### Creation of GiST index {#pg12-p36-creation-of-gist-index} Covering indexes are now available for GiST indexes. **Example 33.** Covering index for GiST index ```text postgres=> CREATE TABLE data1(c1 INT, c2 box, c3 VARCHAR(10)) ; CREATE TABLE postgres=> CREATE INDEX idx1_data1 ON data1 USING gist (c2) INCLUDE (c1) ; CREATE INDEX postgres=> \d data1 Table "public.data1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | integer | | | c2 | box | | | c3 | character varying(10) | | | Indexes: "idx1_data1" gist (c2) INCLUDE (c1) ```

PDF page 37

##### GiST Index and VACUUM {#pg12-p37-gist-index-and-vacuum} Free pages are now reused by VACUUM. ##### WAL when creating GIN index {#pg12-p37-wal-when-creating-gin-index} The amount of WAL output at indexing time has been significantly reduced. #### 3.2.8 CREATE STATISTICS {#pg12-3-2-8-create-statistics} The mcv clause can be used in the CREATE STATISTICS statement. This value indicates Multivariate most-common values (MCV). It expands the regular MCV list and tracks the most frequent value combinations. The acquired statistics are stored in the stxmcv column of the pg_statistic_ext_data catalog. **Example 34.** Create MCV statistics ```text postgres=> CREATE TABLE stat1 (c1 NUMERIC, c2 NUMERIC, c3 VARCHAR(10)) ; CREATE TABLE postgres=> CREATE STATISTICS mcv_stat1(mcv) ON c1, c2 FROM stat1 ; ``` #### 3.2.9 CREATE TABLE {#pg12-3-2-9-create-table} The following enhancements have been made to the CREATE TABLE statement. ##### GENERATED column {#pg12-p37-generated-column} The generated column is the column defined as the column based on calculation results to the table. At the time of column definition, specify GENERATED ALWAYS AS (calculation expression) STORED clause following data type.

PDF page 38

**Example 35.** GENERATED column definition ```text postgres=> CREATE TABLE gen1(c1 VARCHAR(10), c2 VARCHAR(10), c3 VARCHAR(20) GENERATED ALWAYS AS (c1 || c2) STORED) ; CREATE TABLE postgres=> \d gen1 Table "public.gen1" Column | Type | Collation | Nullable |Default --------+-----------------------+-----------+----------+---------- --------- c1 | character varying(10) | | | c2 | character varying(10) | | | c3 | character varying(20) | | | generated always as (((c1::text || c2::text))) stored ``` ```sql Values cannot be specified directly for generated columns in INSERT and UPDATE statements. Only the DEFAULT clause is valid. ``` **Example 36.** Update directly for GENERATED column ```text postgres=> INSERT INTO gen1 VALUES ('AB', 'CD', 'EF') ; psql: ERROR: cannot insert into column "c3" DETAIL: Column "c3" is a generated column. postgres=> INSERT INTO gen1 VALUES ('AB', 'CD', DEFAULT) ; INSERT 0 1 ``` ```sql The generated column values are calculated when executing an INSERT or UPDATE statement, and the calculated values are physically stored. ``` **Example 37.** Stored the GENERATED column value ```text postgres=# SELECT heap_page_items(get_raw_page('gen1', 0)) ; heap_page_items --------------------------------------------------------------------- 1,8152,1,35,524,0,0,"(0,1)",3,2050,24,,,"\\x0741420743440b41424344") (1 row) ```

PDF page 39

In the above example, it is displayed that the c1 column is 'AB' (= 0x4142), the c2 column is 'CD' (= 0x4344), and the c3 column is 'ABCD' (= 0x41424344). Information on generated columns can be found by storing "s" in the attgenerated column added to the pg_attrdef catalog. In addition, information_schema column_column_usage table is newly added. **Example 38.** GENERATED column information ```text postgres=> SELECT * FROM information_schema.column_column_usage ; table_catalog | table_schema | table_name | column_name | dependent_column ---------------+--------------+------------+-------------+----------- ------ postgres | public | gen1 | c1 | c3 postgres | public | gen1 | c2 | c3 (2 rows) postgres=> SELECT attname, attgenerated FROM pg_attribute WHERE attname IN ('c1', 'c2', 'c3') ; attname | attgenerated ---------+-------------- c1 | c2 | c3 | s (3 rows) ``` ```text A generated column cannot be specified as a partitioning key. In addition, it is not possible to define a generated column that depends on the other generated columns. ```

PDF page 40

**Example 39.** Restriction of GENERATED column ```text postgres=> CREATE TABLE pgen1(c1 INT, c2 INT, c3 INT GENERATED ALWAYS AS (c1 + c2) STORED) PARTITION BY RANGE(c3) ; psql: ERROR: cannot use generated column in partition key LINE 1: ...NERATED ALWAYS AS (c1 + c2) STORED) PARTITION BY RANGE(c3) ; ^ DETAIL: Column "c3" is a generated column. postgres=> CREATE TABLE gen2 (c1 INT, c2 INT GENERATED ALWAYS AS (c1*2) STORED, c3 INT GENERATED ALWAYS AS (c2*2) STORED) ; psql: ERROR: cannot use generated column "c2" in column generation expression LINE 1: ...AYS AS (c1*2) STORED, c3 INT GENERATED ALWAYS AS (c2*2) STOR... ``` ##### TABLESPACE clause of partition table definition {#pg12-p40-tablespace-clause-of-partition-table-definition} TABLESPACE clause is now enabled when creating the partition table. In previous versions, the TABLESPACE clause was ignored. Also, the value of the TABLESPACE clause of the partitioned table becomes the default tablespace at the time of partition creation.

PDF page 41

**Example 40.** Partitioned Table Creation and TABLESPACE Clause ```text postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY LIST(c1) TABLESPACE ts1 ; CREATE TABLE postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES IN (100) ; CREATE TABLE postgres=> \d part1 Partitioned table "public.part1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | | c2 | character varying(10) | | | Partition key: LIST (c1) Number of partitions: 1 (Use \d+ to list them.) Tablespace: "ts1" postgres=> \d part1v1 Table "public.part1v1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | | c2 | character varying(10) | | | Partition of: part1 FOR VALUES IN ('100') Tablespace: "ts1" ``` ##### FOR VALUES clause of partition table {#pg12-p41-for-values-clause-of-partition-table} It is now possible to specify a formula or function instead of a literal in the FOR VALUES clause of a partition. The specified formula is executed only once when the CREATE TABLE statement is executed, and the calculated value is stored in the table definition.

PDF page 42

**Example 41.** FOR VALUES clause of partition table ```text postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES IN (power(2, 3)) ; CREATE TABLE postgres=> \d part1v1 Table "public.part1v1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | | c2 | character varying(10) | | | Partition of: part1 FOR VALUES IN ('8') Tablespace: "ts1" ``` ##### A foreign key reference to the partition table {#pg12-p42-a-foreign-key-reference-to-the-partition-table} The partition table can be referenced as a foreign key. **Example 42.** Refer to partition table as reference table ```text postgres=> CREATE TABLE fkey1(c1 INT PRIMARY KEY, c2 VARCHAR(10)) PARTITION BY RANGE(c1) ; CREATE TABLE postgres=> CREATE TABLE fkey1v1 PARTITION OF fkey1 FOR VALUES FROM (0) TO (1000000) ; CREATE TABLE postgres=> CREATE TABLE fkey1v2 PARTITION OF fkey1 FOR VALUES FROM (1000000) TO (2000000) ; CREATE TABLE postgres=> CREATE TABLE ref1(c1 INT REFERENCES fkey1(c1), c2 VARCHAR(10)) ; ``` ```sql In PostgreSQL 11, when trying to create the ref1 table, "ERROR: cannot reference partitioned table" fkey1 "error occurred. ``` ##### VACUUM processing for index {#pg12-p42-vacuum-processing-for-index} By specifying the VACUUM_INDEX_CLEANUP = OFF in WITH clause, it will be able to disable

PDF page 43

the VACUUM process for the index. The default value is 'ON', and VACUUM will be performed as before. Specify TOAST.VACUUM_INDEX_CLEANUP = OFF for the TOAST table. **Example 43.** Suppressing VACUUM on indexes ```text postgres=> CREATE TABLE vacuum1(c1 INT, c2 VARCHAR(10)) WITH (VACUUM_INDEX_CLEANUP = OFF) ; CREATE TABLE postgres=> \d+ vacuum1 Table "public.vacuum1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-----------------------+-----------+----------+---------+----------+--- -----------+- c1 | integer | | | | plain | | c2 | character varying(10) | | | | extended | | Access method: heap Options: vacuum_index_cleanup=off ``` ##### Truncate empty block at the end of the table {#pg12-p43-truncate-empty-block-at-the-end-of-the-table} VACUUM_TRUNCATE has been added to the attributes of the table. Determines whether the truncate empty block at the end of the table is released when performing VACUUM. The default value is 'ON', which truncate empty space as before. When it is specified as 'OFF', this operation is not performed.

PDF page 44

**Example 44.** Truncate empty block at the end of the table ```text postgres=> CREATE TABLE vacuum1(c1 INT, c2 VARCHAR(10)) WITH (VACUUM_TRUNCATE = OFF) ; CREATE TABLE postgres=> \d+ vacuum1 Table "public.vacuum1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-----------------------+-----------+----------+--------- +--------------+-----------------------+-----------+----------+-- -------+----+------------- c1 | integer | | | | plain | | c2 | character varying(10) | | | | extended | | Access method: heap Options: vacuum_truncate=off ``` #### 3.2.10 EXPLAIN {#pg12-3-2-10-explain} The SETTINGS ON option can now be specified in the EXPLAIN statement. This option outputs information on parameters related to the execution plan being changed from the default value. **Example 45.** EXPLAIN \(SETTINGS ON\) ```text postgres=> SET random_page_cost = 1.0 ; SET postgres=> EXPLAIN (SETTINGS ON) SELECT * FROM data1 WHERE c1=100 ; QUERY PLAN ----------------------------------------------------------------------- Index Scan using idx1_data1 on data1 (cost=0.29..2.31 rows=1 width=12) Index Cond: (c1 = '100'::numeric) Settings: random_page_cost = '1' (3 rows) ```

PDF page 45

#### 3.2.11 REINDEX CONCURRENTLY {#pg12-3-2-11-reindex-concurrently} The CONCURRENTLY option can now be added to the REINDEX statement. By reducing the lock range, application operation can be made to coexist with re-indexing. This feature is realized by temporarily creating a new index ({index_name}_ccnew) and replacing it with the old index. **Example 46.** REINDEX CONCURRENTLY statement ```text postgres=> REINDEX (VERBOSE) TABLE CONCURRENTLY data1 ; psql: INFO: index "public.idx1_data1" was reindexed psql: INFO: index "pg_toast.pg_toast_16385_index" was reindexed psql: INFO: table "public.data1" was reindexed DETAIL: CPU: user: 3.25 s, system: 0.69 s, elapsed: 13.27 s. REINDEX ``` ```text In accordance with the change of REINDEX statement, --concurrently option was also added to the reindexdb command. ``` **Example 47.** reindexdb command ```sql $ reindexdb --dbname postgres --echo --concurrently SELECT pg_catalog.set_config('search_path', '', false); REINDEX DATABASE CONCURRENTLY postgres; WARNING: cannot reindex system catalogs concurrently, skipping all $ ``` #### 3.2.12 PL/pgSQL additional check {#pg12-3-2-12-pl-pgsql-additional-check} The following values can now be specified for the parameter plpgsql.extra_warnings: Both can output additional warnings or errors when the function is executed. ##### Strict_multi_assignment setting {#pg12-p45-strict-multi-assignment-setting} A warning is output when the number of columns output by the SELECT INTO statement does not match the number of input variables. In the example below, two warnings occur in the function.

PDF page 46

**Example 48.** strict\_multi\_assignment setting ```text postgres=> SET plpgsql.extra_warnings = 'strict_multi_assignment' ; SET postgres=> CREATE OR REPLACE FUNCTION strict1() RETURNS void LANGUAGE plpgsql AS $$ DECLARE x INTEGER ; y INTEGER ; BEGIN SELECT 1 INTO x, y ; SELECT 1, 2, 3 INTO x, y ; END ; $$ ; CREATE FUNCTION postgres=> SELECT strict1() ; psql: WARNING: number of source and target fields in assignment do not match DETAIL: strict_multi_assignment check of extra_warnings is active. HINT: Make sure the query returns the exact list of columns. psql: WARNING: number of source and target fields in assignment do not match DETAIL: strict_multi_assignment check of extra_warnings is active. HINT: Make sure the query returns the exact list of columns. strict1 --------- (1 row) ``` ##### Too_many_rows setting {#pg12-p46-too-many-rows-setting} An error is generated when multiple records are returned by the SELECT INTO statement, and execution of the procedure is stopped.

PDF page 47

**Example 49.** too\_many\_rows setting ```text postgres=> SET plpgsql.extra_errors to 'too_many_rows' ; SET postgres=> DO $$ DECLARE x INTEGER ; BEGIN SELECT generate_series(1,2) INTO x ; RAISE NOTICE 'test output' ; END ; $$ ; psql: ERROR: query returned more than one row HINT: Make sure the query returns a single row, or use LIMIT 1 CONTEXT: PL/pgSQL function inline_code_block line 4 at SQL statement ``` #### 3.2.13 VACUUM / ANALYZE {#pg12-3-2-13-vacuum-analyze} The following features have been added to the VACUUM and ANALYZE statements. ##### SKIP_LOCKED clause {#pg12-p47-skip-locked-clause} When executing a VACUUM or ANALYZE statement on a locked table, it has been waiting for the lock to be released. In PostgreSQL 12, an option SKIP_LOCKED clause has been added to skip locked tables. When processing is skipped, a log of WARNING level (LOG level for automatic VACUUM) is output. SQLSTATEs are considered successful even if skipped. **Example 50.** Lock table ```text postgres=> BEGIN ; BEGIN postgres=> LOCK TABLE lock1 IN EXCLUSIVE MODE ; LOCK TABLE ```

PDF page 48

**Example 51.** Skip VACUUM for locked table ```text postgres=> VACUUM (SKIP_LOCKED) lock1 ; psql: WARNING: skipping vacuum of "lock1" --- lock not available VACUUM postgres=> \echo :SQLSTATE 00000 ``` ##### Option specification syntax {#pg12-p48-option-specification-syntax} In the VACUUM statement and ANALYZE statement, it is now possible to specify the action to be performed with TRUE/FALSE or ON/OFF. **Example 52.** Operation specification by ON\/OFF ```text postgres=> VACUUM (VERBOSE OFF, FULL ON, ANALYZE ON) data1 ; VACUUM postgres=> VACUUM (VERBOSE TRUE, FULL TRUE, ANALYZE FALSE) data1 ; psql: INFO: vacuuming "public.data1" psql: INFO: "data1": found 0 removable, 1000000 nonremovable row versions in 5406 pages DETAIL: 0 dead row versions cannot be removed yet. CPU: user: 0.23 s, system: 0.28 s, elapsed: 0.72 s. VACUUM ``` ##### Suppress VACUUM for index option {#pg12-p48-suppress-vacuum-for-index-option} By specifying the OFF to INDEX_CLEANUP clause in VACUUM statement, it will be able to suppress the VACUUM process for the index. If this specification is omitted, the operation depends on the VACUUM_INDEX_CLEANUP attribute of the table.

PDF page 49

**Example 53.** Suppress VACUUM for index ```text postgres=> VACUUM (VERBOSE ON, INDEX_CLEANUP OFF) data1 ; INFO: vacuuming "public.data1" INFO: "data1": found 10 removable, 1730 nonremovable row versions in 10 out of 5406 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 593 There were 0 unused item identifiers. Skipped 0 pages due to buffer pins, 5396 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. … Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM ``` ##### Suppression of free page truncation processing at the end of the table {#pg12-p49-suppression-of-free-page-truncation-processing-at-the-end-of-the-table} The TRUNCATE clause has been added to the VACUUM statement. By specifying OFF for this attribute, it is possible to suppress free area deletion processing at the end of the table. If omitted, it depends on the VACUUM_TRUNCATE attribute of the table. **Example 54.** Suppress truncation processing ```text postgres=> VACUUM (VERBOSE ON, TRUNCATE OFF) data1 ; INFO: vacuuming "public.data1" INFO: scanned index "idx1_data1" to remove 10 row versions DETAIL: CPU: user: 0.06 s, system: 0.00 s, elapsed: 0.08 s INFO: "data1": removed 10 row versions in 10 pages <<途中省略>> There were 0 unused item identifiers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM ```

PDF page 50

#### 3.2.14 WITH SELECT {#pg12-3-2-14-with-select} In the past, common table expressions (CTE) specified in the WITH clause were all MATERIALIZED. In PostgreSQL 12, the default behavior has been changed to NOT MATERIALIZED. In order to change these behaviors, it is possible to specify MATERIALIZED or NOT MATERIALIZED in the WITH clause. Specifying the NOT MATERIALIZED clause allows the specification of the WHERE clause to be pushed down into the WITH clause. In the example below, using the NOT MATERIALIZED clause shows that index search is selected and the cost is reduced. **Example 55.** WITH NOT MATERIALIZED ```text postgres=> EXPLAIN WITH s AS NOT MATERIALIZED (SELECT * FROM data1) SELECT * FROM s WHERE c1=100 ; QUERY PLAN ----------------------------------------------------------------- -------- Index Scan using data1_pkey on data1 (cost=0.42..8.44 rows=1 width=12) Index Cond: (c1 = '100'::numeric) (2 rows) ``` **Example 56.** WITH MATERIALIZED ```text postgres=> EXPLAIN WITH s AS MATERIALIZED (SELECT * FROM data1) SELECT * FROM s WHERE c1=100 ; QUERY PLAN ----------------------------------------------------------------- ------ CTE Scan on s (cost=15406.00..37906.00 rows=5000 width=70) Filter: (c1 = '100'::numeric) CTE s -> Seq Scan on data1 (cost=0.00..15406.00 rows=1000000 width=12) (4 rows) ``` #### 3.2.15 Functions {#pg12-3-2-15-functions} The following functions have been added/enhanced.

PDF page 51

##### SQL/JSON {#pg12-p51-sql-json} Some functions related to SQL / JSON proposed in SQL 2016 standard are provided. **Example 57.** jsonb\_path\_query\_array function ```text postgres=> SELECT jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}') ; jsonb_path_query_array ------------------------ [2, 3, 4] (1 row) ``` ```text The following functions have been added. ``` **Table 16.** JSON\/SQL functions | Function name | Description | | --- | --- | | jsonb_path_exists | Checks whether the JSON path returns any item for the specified JSON value. | | jsonb_path_match | Returns JSON path predicate result for the specified JSON value. Only the first result item is taken into account. | | jsonb_path_query | Gets all JSON items returned by JSON path for the specified JSON value. | | jsonb_path_query_array | Gets all JSON items returned by JSON path for the specified JSON value and wraps result into an array. | | jsonb_path_query_first | Gets the first JSON item returned by JSON path for the specified JSON value. | ##### Pg_partition_tree {#pg12-p51-pg-partition-tree} Pg_partition_tree is a function to display the tree structure of the partition table. It also supports hierarchical partition structure. Specify a partition table in the parameter. Specifying an object name other than a partition table or partition returns NULL.

PDF page 52

**Example 58.** pg\_partition\_tree function ```text postgres=> SELECT * FROM pg_partition_tree('part1') ; relid | parentrelid | isleaf | level ---------+-------------+--------+------- part1 | | f | 0 part1v1 | part1 | t | 1 part1v2 | part1 | t | 1 (3 rows) postgres=> SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total FROM pg_partition_tree('part1') ; total ------- 84 MB (1 row) ``` ##### Pg_partition_root {#pg12-p52-pg-partition-root} The pg_partition_root is a function that returns the top-level partition table name of a specified partition. In the following example, a sub partition is created and the pg_partition_root function is executed. **Example 59.** pg\_partition\_root function ```text postgres=> CREATE TABLE part1(c1 NUMERIC, c2 NUMERIC, c3 VARCHAR(10)) PARTITION BY LIST(c1) ; CREATE TABLE postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES IN (100) PARTITION BY LIST (c2) ; CREATE TABLE postgres=> CREATE TABLE part1v1v2 PARTITION OF part1v1 FOR VALUES IN (200) ; CREATE TABLE postgres=> SELECT pg_partition_root('part1v1v2') ; pg_partition_root ------------------- part1 (1 row) ```

PDF page 53

##### Pg_partition_ancestors {#pg12-p53-pg-partition-ancestors} The pg_partition_ancestors function prints the list towards the parent of the partition table that contains the specified partition. **Example 60.** pg\_partition\_ancestors function ```text postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY LIST(c1) ; CREATE TABLE postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES IN (100) ; CREATE TABLE postgres=> SELECT pg_partition_ancestors('part1v1') ; pg_partition_ancestors ------------------------ part1v1 part1 (2 rows) ``` ##### Pg_promote {#pg12-p53-pg-promote} This function promotes a standby instance to a primary instance. Previously, it was necessary to execute the pg_ctl promote command. Specify the parameter which determines whether to wait (default true) and the number of seconds to wait (default 60 seconds). This function returns false if processing fails or if the promotion is not completed within the waiting time, otherwise, it returns true. **Example 61.** pg\_promote function ```text postgres=# SELECT pg_promote(true, 90) ; pg_promote ------------ t (1 row) ```

PDF page 54

##### Pg_ls_tmpdir {#pg12-p54-pg-ls-tmpdir} The pg_ls_tmpdir function has been added to return a list of file names where temporary data has been saved. Specify the OID of the TABLESPACE in the parameter. If omitted, it is assumed that pg_default is specified. This function requires the SUPERUSER privilege or the pg_monitor role. **Example 62.** pg\_ls\_tmpdir function ```text postgres=# SELECT * FROM pg_ls_tmpdir() ; name | size | modification ------------------+-----------+------------------------ pgsql_tmp36911.6 | 148955136 | 2019-10-04 11:57:36-04 pgsql_tmp37050.0 | 139722752 | 2019-10-04 11:57:36-04 pgsql_tmp37051.0 | 138403840 | 2019-10-04 11:57:36-04 (3 rows) ``` ##### Pg_ls_archive_statusdir {#pg12-p54-pg-ls-archive-statusdir} The pg_ls_archive_statusdir function has been added to obtain the status of the archive file. This function searches the ${PGDATA}/pg_wal/archive_status directory and outputs the file name, size, and modification date. It does not output the actual information of archived WAL files. This function requires the SUPERUSER privilege or the pg_monitor role. **Example 63.** pg\_ls\_archive\_statusdir function ```text postgres=# SELECT * FROM pg_ls_archive_statusdir() ; name | size | modification -------------------------------+------+------------------------ 00000001000000000000000D.done | 0 | 2019-10-04 19:33:00-04 00000001000000000000000E.done | 0 | 2019-10-04 19:33:01-04 00000001000000000000000F.done | 0 | 2019-10-04 19:33:02-04 (3 rows) ``` ##### Date_trunc {#pg12-p54-date-trunc} This function can now set Timezone.

PDF page 55

**Example 64.** date\_trunc function with timezone ```text postgres=> SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2019-10-04 20:38:40+00', 'Asia/Tokyo') ; date_trunc ------------------------ 2019-10-04 11:00:00-04 (1 row) ``` ##### Hyperbolic functions {#pg12-p55-hyperbolic-functions} The following hyperbolic functions included in SQL:standards 2016 have been added **Table 17.** Hyperbolic functions | Functionname | Description | Note | | --- | --- | --- | | log10 | base 10 logarithm | | | sinh | hyperbolic sine | | | cosh | hyperbolic cosine | | | tanh | hyperbolic tangent | | | asinh | inverse hyperbolic sine | | | acosh | inverse hyperbolic cosine | | | atanh | inverse hyperbolic tangent | | **Example 65.** Hyperbolic functions ```text postgres=> SELECT log10(20), sinh(1) ; log10 | sinh --------------------+-------------------- 1.3010299956639813 | 1.1752011936438014 (1 row) ``` ##### Copy replication slot {#pg12-p55-copy-replication-slot} The function that performs a copy of an existing replication slot has been provided. The pg_copy_physical_replication_slot function and the pg_copy_logical_replication_slot function are provided according to the type of replication slot. To make a copy, it is necessary to replication slot is being used.

PDF page 56

**Example 66.** Copy replication slot ```text postgres=# SELECT pg_create_physical_replication_slot('slot_1') ; pg_create_physical_replication_slot ------------------------------------- (slot_1,) (1 row) postgres=# SELECT pg_copy_physical_replication_slot('slot_1', 'slot_c') ; ERROR: cannot copy a replication slot that doesn't reserve WAL ```