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

PDF page 44

This section explains new features related to SQL statements. #### 3.2.1 ALTER OPERATOR {#pg17-3-2-1-alter-operator} The following attributes can now be altered by the ALTER OPEARTOR statement. Previously, these could only be specified in the CREATE OPERATOR statement. However, once a flag is set, it cannot be changed. [[2b5154b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2b5154beab794eae6e624c162d497df927ec9d27)] - COMMUTATOR - NEGATOR - HASHES - MERGES **Example 36.** Attribute modification by ALTER OPERATOR statement ```text postgres=# CREATE OR REPLACE FUNCTION alter_op_test_fn_bool_real(boolean, real) RETURNS boolean AS $$ SELECT NULL::BOOLEAN; $$ LANGUAGE SQL IMMUTABLE ; CREATE FUNCTION postgres=# CREATE OPERATOR === ( LEFTARG = boolean, RIGHTARG = real, PROCEDURE = alter_op_test_fn_bool_real) ; CREATE OPERATOR postgres=# ALTER OPERATOR === (boolean, real) SET (MERGES = false) ; ALTER OPERATOR postgres=# ALTER OPERATOR === (boolean, real) SET (HASHES = false) ; ALTER OPERATOR ``` #### 3.2.2 ALTER SYSTEM {#pg17-3-2-2-alter-system} A parameter containing module names (e.g., foo.bar) used by extensions, etc., can now be specified in the ALTER SYSTEM statement. In the previous version, only parameters for which a SET statement had been executed in the past could be set with the ALTER SYSTEM statement. [[2d870b4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2d870b4aeff174ea3b8a0837bc2065c820fb43e2)]

PDF page 45

**Example 37.** ALTER SYSTEM statement to change parameters for extensions ```text postgres=# ALTER SYSTEM SET foo.bar = 100 ; ALTER SYSTEM postgres=# SELECT pg_reload_conf() ; pg_reload_conf ---------------- t (1 row) postgres=# SHOW foo.bar ; foo.bar --------- 100 (1 row) ``` #### 3.2.3 ALTER TABLE {#pg17-3-2-3-alter-table} The following enhancements have been implemented to the ALTER TABLE statement. ##### Change to default access method {#pg17-p45-change-to-default-access-method} To change to the access method specified in the parameter default_table_access_method, it is now possible to specify DEFAULT for the access method name. [[d61a6ca](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d61a6cad6418f643a5773352038d0dfe5d3535b8)] #### Syntax 3 - ALTER TABLE SET ACCESS METHOD statement {#pg17-syntax-3} ```sql ALTER TABLE table_name SET ACCESS METHOD DEFAULT ``` **Example 38.** Change access method ```text postgres=> ALTER TABLE data1 SET ACCESS METHOD DEFAULT ; ALTER TABLE ``` ##### Change Generate Column {#pg17-p45-change-generate-column} The formulas for generated columns (GENERATE clause) can now be changed. If the formula for the column is changed, the existing data in the table will be recalculated. [[5d06e99](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5d06e99a3cfc23bbc217b4d78b8c070ad52f720e)]

PDF page 46

#### Syntax 4 - ALTER TABLE SET EXPRESSION statement {#pg17-syntax-4} ```sql ALTER TABLE table_name ALTER COLUMN column_name SET EXPRESSION AS (expression) ``` **Example 39.** Specifying the SET EXPRESSION clause ```text postgres=> CREATE TABLE data1(c1 INT, c2 INT GENERATED ALWAYS AS (c1 * 2) STORED) ; CREATE TABLE postgres=> INSERT INTO data1(c1) VALUES (100) ; INSERT 0 1 postgres=> SELECT * FROM data1 ; c1 | c2 -----+----- 100 | 200 (1 row) postgres=> ALTER TABLE data1 ALTER COLUMN c2 SET EXPRESSION AS (c1 * 200) ; ALTER TABLE postgres=> SELECT * FROM data1 ; c1 | c2 -----+------- 100 | 20000 (1 row) ``` ##### Statistics Settings {#pg17-p46-statistics-settings} It is now possible to use DEFAULT when setting the volume of statistics stored in a single column to the parameter default_statistics_target. In previous versions, -1 was specified. When using the default value, the attstattarget column in the pg_attribute catalog will be set to the NULL value. Previously, the NOT NULL constraint was specified for this column. [[4f62250](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4f622503d6de975ac87448aea5cea7de4bc140d5), [5567996](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=556799660fc4dc23e187994ae5ea622933ea720f)] #### Syntax 5 - ALTER TABLE SET STATISTICS statement {#pg17-syntax-5} ```sql ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS DEFAULT ```

PDF page 47

**Example 40.** Specifying the SET STATISTICS clause ```text postgres=> ALTER TABLE data1 ALTER COLUMN c1 SET STATISTICS 110 ; ALTER TABLE postgres=> SELECT attname, attstattarget FROM pg_attribute WHERE attrelid= (SELECT oid FROM pg_class WHERE relname='data1') AND attname='c1' ; attname | attstattarget ---------+--------------- c1 | 110 (1 row) postgres=> ALTER TABLE data1 ALTER COLUMN c1 SET STATISTICS DEFAULT ; ALTER TABLE postgres=> SELECT attname,attstattarget FROM pg_attribute WHERE attrelid= (SELECT oid FROM pg_class WHERE relname='data1') AND attname='c1' ; attname | attstattarget ---------+--------------- c1 | null (1 row) ``` #### 3.2.4 CLUSTER {#pg17-3-2-4-cluster} The syntax for enclosing options in parentheses (()) is now permitted when the table name is omitted. This enhancement supports the same syntax as the REINDEX and VACUUM statements. [[cdaedfc](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cdaedfc96d1488069518d42479125ceb9a5e67d0)] **Example 41.** CLUSTER with VERBOSE clause ```text postgres=> CLUSTER (VERBOSE) ; INFO: clustering "public.data1" using index scan on "data1_pkey1" INFO: "public.data1": found 0 removable, 1000000 nonremovable row versions in 5406 pages DETAIL: 0 dead row versions cannot be removed yet. CPU: user: 0.22 s, system: 0.00 s, elapsed: 0.25 s. CLUSTER ``` #### 3.2.5 COPY {#pg17-3-2-5-copy} The following new features have been implemented in the COPY statement.

PDF page 48

##### FORCE_NULL option {#pg17-p48-force-null-option} An asterisk (*) can now be specified in the FORCE_NULL and FORCE_NOT_NULL options of the COPY statement. [[f6d4c9c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f6d4c9cf162b70f2837fb6c2a83e80a3f3410695)] **Example 42.** Specifying the FORCE\_NULL \* option ```text postgres=> COPY data1 FROM stdin WITH (FORMAT csv, FORCE_NULL *) ; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. >> 100,data1 >> 200,"" >> 300,data3 >> \. COPY 3 postgres=> SELECT * FROM data1 ; c1 | c2 -----+------- 100 | data1 200 | null 300 | data3 (3 rows) ``` ##### ON_ERROR option {#pg17-p48-on-error-option} The ON_ERROR option has been added to the COPY FROM statement. This option changes the behavior when an error occurs. The following values can be specified for this option. [[9e2d870](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9e2d8701194fa1d280b73c024759950c74c1c637), [b725b7e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b725b7eec431f7394d63abe621170efe3fcdcda4), [a6d0fa5](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a6d0fa5ef840bc98e14cb34ecf672e6e2e245517)] **Table 25.** Available value for the ON\_ERROR option | Value | Description | | --- | --- | | stop | Stop processing when an error occurs. | | ignore | Ignore the occurrence of data conversion errors and continue the processing. | Even if this option is set to 'ignore', the COPY statement will terminate abnormally if a constraint violation (primary key constraint or CHECK constraint) occurs.

PDF page 49

**Example 43.** Specifying the ON\_ERROR option ```text postgres=> CREATE TABLE data1(c1 INT, c2 VARCHAR(10)) ; CREATE TABLE postgres=> COPY data1 FROM STDIN WITH (FORMAT csv, ON_ERROR ignore) ; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. >> 100,data1 >> 200,data2 >> ABC,data3 >> 400,data4 >> \. NOTICE: 1 row were skipped due to data type incompatibility COPY 3 postgres=> SELECT * FROM data1 ; c1 | c2 -----+-------- 100 | data1 200 | data2 400 | data4 (3 rows) ``` ##### LOG_VERBOSITY option {#pg17-p49-log-verbosity-option} The LOG_VERBOSITY option has been added to the COPY statement to change the level of log output. The LOG_VERBOSITY option controls the output of errors generated by a COPY FROM statement with the ON_ERROR option set to 'ignore'. The possible values are as follows. [[f5a2278](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f5a227895e178bf528b18f82bbe554435fb3e64f)] **Table 26.** Available value for the LOG\_VERBOSITY option | Value | Description | Note | | --- | --- | --- | | default | Output standard log level. | Default | | verbose | Output verbose log level. | |

PDF page 50

**Example 44.** Specifying the LOG\_VERBOSITY option ```text postgres=> COPY data1 FROM STDIN WITH (FORMAT csv, ON_ERROR ignore, LOG_VERBOSITY default) ; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. >> 100,data1 >> 200,data2 >> ABC,data3 >> 400,data4 >> \. NOTICE: 1 row was skipped due to data type incompatibility COPY 3 postgres=> TRUNCATE TABLE data1 ; TRUNCATE TABLE postgres=> COPY data1 FROM STDIN WITH (FORMAT csv, ON_ERROR ignore, LOG_VERBOSITY verbose) ; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. >> 100,data1 >> 200,data2 >> ABC,data3 >> 400,data4 >> \. NOTICE: skipping row due to data type incompatibility at line 3 for column c1: "ABC" NOTICE: 1 row was skipped due to data type incompatibility COPY 3 ``` #### 3.2.6 CREATE TABLE {#pg17-3-2-6-create-table} The access method can now be specified for the partitioned table, using the CREATE TABLE USING or ALTER TABLE SET ACCESS METHOD statements. The specified value will be used as the default value for the access method for the partitions created under the partitioned table. The existing partitions will not be modified. [[374c7a2](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=374c7a2290429eac3217b0c7b0b485db9c2bcc72)]

PDF page 51

#### Syntax 6 - CREATE TABLE PARTITION BY USING statement {#pg17-syntax-6} ```sql CREATE TABLE table_name(column,…) PARTITION BY partition_method (column) USING table_access_method ``` **Example 45.** Specifying the access method ```text postgres=> CREATE TABLE part1 (c1 INT PRIMARY KEY, c2 VARCHAR(10)) PARTITION BY RANGE(c1) USING heap ; CREATE TABLE postgres=> ALTER TABLE part1 SET ACCESS METHOD DEFAULT ; ALTER TABLE ``` #### 3.2.7 EXPLAIN {#pg17-3-2-7-explain} The following new features have been added to the EXPLAIN statement. ##### Enhancements to JSON Format {#pg17-p51-enhancements-to-json-format} Local I/O Read Time" and "Local I/O Write Time" are now output in JSON format. To enable this output, the parameter track_io_timing must be set to 'on'. [[295c36c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=295c36c0c1fa7b6befd0a3525c7f109e838c9448)] **Example 46.** EXPLAIN statement in JSON format ```text postgres=> EXPLAIN (ANALYZE, BUFFERS, FORMAT json) SELECT * FROM data1 ; QUERY PLAN --------------------------------------- [ + { + "Plan": { + "Node Type": "Seq Scan", + "Parallel Aware": false, + … "Shared I/O Write Time": 0.000,+ "Local I/O Read Time": 0.000, + "Local I/O Write Time": 0.000, + "Temp I/O Read Time": 0.000, + … ```

PDF page 52

##### MEMORY option {#pg17-p52-memory-option} The MEMORY option has been added to the EXPLAIN statement to output the memory settings when creating an execution plan. The EXPLAIN statement with the MEMORY clause outputs information about the amount of memory allocated (allocated) and the amount of memory actually used (used). [[5de890e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5de890e3610d5a12cdaea36413d967cf5c544e20)] **Example 47.** EXPLAIN statement with MEMORY clause ```text postgres=> EXPLAIN (MEMORY) SELECT COUNT(*) 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=0) Planning: Memory: used=9kB allocated=16kB (4 rows) ``` ##### SERIALIZE option {#pg17-p52-serialize-option} The SERIALIZE clause has been added that outputs information on the amount of data and time output to the client by the query execution. This option is used in conjunction with the ANALYZE clause. The following values can be added to the option. [[0628670](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=06286709ee0637ec7376329a5aa026b7682dcfe2)] **Table 27.** Available value for the SERIALIZE option | Value | Description | Note | | --- | --- | --- | | none | Do not output serialization information | Default | | textbinary | Output in text format Output in binary format | Default for SERIALIZE only |

PDF page 53

**Example 48.** EXPLAIN statement with SERIALIZE clause ```text postgres=> EXPLAIN (ANALYZE, SERIALIZE BINARY) SELECT * FROM data1 ; QUERY PLAN ----------------------------------------------------------------------- Seq Scan on data1 (cost=0.00..15406.00 rows=1000000 width=10) (actual time=0.005..36.797 rows=1000000 loops=1) Planning Time: 0.056 ms Serialization: time=84.192 ms output=18555kB format=binary Execution Time: 154.041 ms (4 rows) ``` #### 3.2.8 MERGE {#pg17-3-2-8-merge} The following new features have been added to the MERGE statement. ##### BY SOURCE clause {#pg17-p53-by-source-clause} The condition specification ``WHEN NOT MATCHED BY SOURCE'' has been added to the MERGE statement. This condition operates on tuples that do not exist in the source table but exist in the target table. The UPDATE, DELETE, and DO NOTHING clauses can be specified as the action for the target tuple. [[0294df2](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0294df2f1f842dfb0eed79007b21016f486a3c6c)]

PDF page 54

**Example 49.** Specifying the BY SOURCE clause ```text postgres=> SELECT * FROM src1 ; c1 | c2 -----+--------- 100 | source1 200 | source2 (2 rows) postgres=> SELECT * FROM tgt1 ; c1 | c2 -----+--------- 100 | target1 300 | target3 (2 rows) postgres=> MERGE INTO tgt1 AS t USING src1 AS s ON s.c1 = t.c1 WHEN NOT MATCHED THEN INSERT VALUES (s.c1, s.c2) WHEN NOT MATCHED BY SOURCE THEN UPDATE SET c2='not matched' ; MERGE 2 postgres=> SELECT * FROM tgt1 ; c1 | c2 -----+------------- 100 | target1 200 | source2 300 | not matched (3 rows) ``` ```text The traditional WHEN NOT MATCHED clause is treated as a statement with the BY TARGET clause omitted. ``` ##### RETURNING clause {#pg17-p54-returning-clause} The MERGE statements can now specify the RETURNING clause to return the data of updated tuples, and a special function merge_action has been added to determine the type (INSERT/UPDATE/DELETE) of the tuple returned by the RETURNING clause. The output of the RETURNING clause is available as a source relation like any other DML. [[c649fa2](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c649fa24a42ba89bf5460c7110e4fc8eeca65959)]

PDF page 55

**Example 50.** MERGE statement with the RETURNING clause ```text postgres=> CREATE TABLE src1(c1 INT PRIMARY KEY, c2 VARCHAR(10)) ; CREATE TABLE postgres=> INSERT INTO src1 VALUES (100, 'data1'),(300, 'data3') ; INSERT 0 2 postgres=> CREATE TABLE tgt1(c1 INT PRIMARY KEY, c2 VARCHAR(10)) ; CREATE TABLE postgres=> INSERT INTO tgt1 VALUES (100, 'data1'),(200, 'data2') ; INSERT 0 2 postgres=> MERGE INTO tgt1 t USING src1 s ON s.c1 = t.c1 WHEN MATCHED THEN UPDATE SET c2 = 'updated' WHEN NOT MATCHED THEN INSERT (c1, c2) VALUES (s.c1, s.c2) RETURNING merge_action(), t.* ; merge_action | c1 | c2 --------------+-----+--------- UPDATE | 100 | updated INSERT | 300 | data3 (2 rows) MERGE 2 ``` ##### MERGE statement for updatable views {#pg17-p55-merge-statement-for-updatable-views} The updatable views can now be specified as targets in the MERGE statement. [[5f2e179](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5f2e179bd31e5f5803005101eb12a8d7bf8db8f3)]

PDF page 56

**Example 51.** MERGE statement for updatable views ```text postgres=> CREATE TABLE src1(c1 INT PRIMARY KEY, c2 VARCHAR(10)) ; CREATE TABLE postgres=> INSERT INTO src1 VALUES (generate_series(1, 10), 'data1') ; INSERT 0 10 postgres=> CREATE TABLE tgt1(c1 INT PRIMARY KEY, c2 VARCHAR(10)) ; CREATE TABLE postgres=> CREATE VIEW view1 AS SELECT * FROM tgt1 ; CREATE VIEW postgres=> MERGE INTO view1 AS v1 USING src1 AS s1 ON v1.c1 = s1.c1 WHEN NOT MATCHED THEN INSERT (c1, c2) VALUES (s1.c1, s1.c2) ; MERGE 10 ``` #### 3.2.9 PL/pgSQL {#pg17-3-2-9-pl-pgsql} The following enhancements have been implemented in PL/pgSQL. ##### Addition of data type attributes {#pg17-p56-addition-of-data-type-attributes} The array declaration can now be specified for the %TYPE and %ROWTYPE attributes of variables. [[5e8674d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5e8674dc83926f52516f847f1a77e8d38e94e143)] **Example 52.** \%TYPE attribute and \%ROWTYPE attribute ```text postgres=> CREATE OR REPLACE FUNCTION func1() RETURNS TEXT AS $$ DECLARE val1 pg_catalog.pg_class%ROWTYPE[]; val2 pg_node_tree; val3 val2%TYPE[]; BEGIN … END; $$ LANGUAGE plpgsql; CREATE FUNCTION ```

PDF page 57

##### INTERNAL Sub-Transaction {#pg17-p57-internal-sub-transaction} The BeginInternalSubTransaction API can now be run in parallel mode. In the example below, PostgreSQL 16 executes a PL/pgSQL function with PARALLEL SAFE specified in parallel mode, and an error occurs. [[0075d78](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0075d78947e3800c5a807f48fd901f16db91101b)] **Example 53.** Execution error occurs in PostgreSQL 16 ```text postgres=> CREATE FUNCTION zero_divide() RETURNS INT AS $$ DECLARE v INT := 0; BEGIN RETURN 10 / v; END; $$ LANGUAGE plpgsql PARALLEL SAFE ; CREATE FUNCTION postgres=> CREATE FUNCTION error_trap_test() RETURNS TEXT AS $$ BEGIN PERFORM zero_divide(); RETURN 'no error detected!'; EXCEPTION WHEN division_by_zero THEN RETURN 'division_by_zero detected'; END; $$ LANGUAGE plpgsql PARALLEL SAFE ; CREATE FUNCTION postgres=> SET debug_parallel_query = on ; SET postgres=> SELECT error_trap_test() ; ERROR: cannot start subtransactions during a parallel operation CONTEXT: PL/pgSQL function error_trap_test() line 2 during statement block entry ``` #### 3.2.10 Data Types {#pg17-3-2-10-data-types} The following enhancements have been implemented for PostgreSQL 17 data types. ##### INTERVAL {#pg17-p57-interval} The +/-Infinity is now supported for the INTERVAL type. [[519fc1b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=519fc1bd9e9d7b408903e44f55f83f6db30742b7)]

PDF page 58

**Example 54.** INTERVAL type and Infinity ```text postgres=> SELECT timestamp 'infinity' - timestamp '-infinity' ; ?column? ---------- infinity (1 row) postgres=> SELECT age(timestamp '-infinity', timestamp 'infinity') ; age ----------- -infinity (1 row) ``` ##### AT LOCAL clause {#pg17-p58-at-local-clause} The AT LOCAL clause can now be specified to convert timestamps with time zones to local time zones. [[97957fd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=97957fdbaa429c7c582d4753b108cb1e23e1b28a)] **Example 55.** Specify the AT LOCAL clause ```text postgres=> SHOW timezone ; TimeZone ------------ Asia/Tokyo (1 row) postgres=> SELECT CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL ; timezone --------------------- 1978-07-08 08:38:00 (1 row) ``` ##### RANGE {#pg17-p58-range} The expressions containing constant range values can now be directly compared for the range operators '<@' and '@>'. [[075df6b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=075df6b2080b13e0a5adc88737b7c24417a873c1)]

PDF page 59

**Example 56.** Behavior of PostgreSQL 17 ```text postgres=> EXPLAIN (VERBOSE, COSTS OFF) SELECT CURRENT_DATE <@ DATERANGE 'empty' ; QUERY PLAN ----------------- Result Output: false (2 rows) ``` **Example 57.** Behavior of PostgreSQL 16 ```text postgres=> EXPLAIN (VERBOSE, COSTS OFF) SELECT CURRENT_DATE <@ DATERANGE 'empty' ; QUERY PLAN ------------------------------------------------ Result Output: (CURRENT_DATE <@ 'empty'::daterange) Query Identifier: 4806765806694262066 (3 rows) ``` #### 3.2.11 JSON {#pg17-3-2-11-json} The JSON-related syntax has been enhanced. ##### JSON Constructor {#pg17-p59-json-constructor} The following JSON constructor functions have been added. [[03734a7](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=03734a7fed7d924679770adb78a7db8a37d14188)] #### Syntax 7 - JSON constructor {#pg17-syntax-7} ```sql JSON( expression [ FORMAT JSON [ ENCODING UTF8 ] ] [ { WITH | WITHOUT } UNIQUE [ KEYS ]]) text JSON_SCALAR( expression ) text | bytea JSON_SERIALIZE( expression [ FORMAT JSON [ ENCODING UTF8 ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]) ```

PDF page 60

**Example 58.** Added JSON constructor ```text postgres=> SELECT JSON('{ "val": 1}'::bytea FORMAT JSON ENCODING UTF8) ; json ------------- { "val": 1} (1 row) postgres=> SELECT JSON_SCALAR(123.45::numeric) ; json_scalar ------------- 123.45 (1 row) postgres=> SELECT JSON_SERIALIZE(JSON('{ "val" : 1 }')) ; json_serialize ---------------- { "val" : 1 } (1 row) ``` ##### JSONPATH method {#pg17-p60-jsonpath-method} The following JSONPATH methods have been added. [[66ea94e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=66ea94e8e606529bb334515f388c62314956739e), [4697454](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=469745468668917434dbef48eddad4f961880b3d)] **Table 28.** Added JSONPATH methods | Method | Description | Note | | --- | --- | --- | | .bigint() | Returns a large integer value | | | .boolean() | Return True or False | | | .date() | Returns a date | | | .decimal([p [,s]]) | Returns a number with a specified precision | | | .integer() | Returns an integer | | | .number() | Returns a numeric value | | | .string() | Returns a character string | | | .time() | Returns a time | | | .time_tz() | Returns the time with timezone | | | .timestamp() | Returns a timestamp | | | .timestamp_tz() | Returns a timestamp with timezone | |

PDF page 61

**Example 59.** JSONPATH methods ```text postgres=> SELECT jsonb_path_query_array('[1, "yes", false]', '$[*].boolean()'), jsonb_path_query('"2024-09-27"', '$.datetime().string()'), jsonb_path_query('{"len": "9876543219"}', '$.len.bigint()'), jsonb_path_query('1234.5678', '$.decimal(6, 2)'), jsonb_path_query('{"len": "12345"}', '$.len.integer()'), jsonb_path_query('{"len": "123.45"}', '$.len.number()'), jsonb_path_query('"2024-09-27"', '$.date()'), jsonb_path_query('"12:34:56"', '$.time()'), jsonb_path_query('"12:34:56 +05:30"', '$.time_tz()'), jsonb_path_query('"2024-09-27 12:34:56"', '$.timestamp()') ; -[ RECORD 1 ]----------+---------------------- jsonb_path_query_array | [true, true, false] jsonb_path_query | "2024-09-27" jsonb_path_query | 9876543219 jsonb_path_query | 1234.57 jsonb_path_query | 12345 jsonb_path_query | 123.45 jsonb_path_query | "2024-09-27" jsonb_path_query | "12:34:56" jsonb_path_query | "12:34:56+05:30" jsonb_path_query | "2024-09-27T12:34:56" ``` ```text The timestamp_tz method cannot be executed on the jsonb_path_query function because the time zone is not IMMUTABLE. ```

PDF page 62

**Example 60.** Timestamp with Time zone ```text postgres=> SELECT jsonb_path_query('"2024-09-27 12:34:56"', '$.timestamp_tz()') ; ERROR: cannot convert value from timestamp to timestamptz without time zone usage HINT: Use *_tz() function for time zone support. postgres=> SELECT jsonb_path_query_tz('"2024-09-27 12:34:56"', '$.timestamp_tz()') ; jsonb_path_query_tz ----------------------------- "2024-09-27T03:34:56+00:00" ``` ##### JSON Query {#pg17-p62-json-query} The functions have been added to query within JSON data. The JSON_EXISTS function returns true if the item was generated using the PASSING clause. The JSON_QUERY and JSON_VALUE functions return the result of applying the PASSING clause from the specified JSON data. [[6185c97](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6185c9737cf48c9540782d88f12bd2912d6ca1cc)] #### Syntax 8 - JSON Query {#pg17-syntax-8} ```sql boolean JSON_EXISTS (context_item, path_expression [ PASSING { value AS varname } [, ...]] [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ]) JSON_QUERY (context_item, path_expression [ PASSING { value AS varname } [, ...]] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ] [ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER ] [ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ] [ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT expression } ON EMPTY ] [ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT expression } ON ERROR ]) JSON_VALUE (context_item, path_expression [ PASSING { value AS varname } [, ...]] [ RETURNING data_type ] [ { ERROR | NULL | DEFAULT expression } ON EMPTY ] [ { ERROR | NULL | DEFAULT expression } ON ERROR ]) ```

PDF page 63

**Example 61.** JSON query ```text postgres=> SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING 1 AS x) ; json_exists ------------- t (1 row) postgres=> SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text KEEP QUOTES) ; json_query ------------ "aaa" (1 row) postgres=> SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' DEFAULT '0' ON ERROR) ; json_value ------------ 0 (1 row) ``` ##### Relational conversion {#pg17-p63-relational-conversion} The JSON_TABLE function has been added to convert JSON data to the relational view. [[de36004](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=de3600452b61d1bc3967e9e37e86db8956c8f577), [bb766cd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bb766cde63b4f624d029b34c9cdd3d0a94fd5b46)] #### Syntax 9 - JSON_TABLE function {#pg17-syntax-9} ```sql JSON_TABLE (context_item, path_expression [ AS json_path_name ] [ PASSING { value AS varname } [, ...] ] COLUMNS ( json_table_column [, ...] ) [ { ERROR | EMPTY } ON ERROR ] ) ```

PDF page 64

**Example 62.** Execute JSON\_TABLE function ```text postgres=> SELECT * FROM JSON_TABLE(jsonb '123', '$' COLUMNS (item int PATH '$', foo int)) bar ; item | foo ------+----- 123 | (1 row) postgres=> SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt ; a --- 2 (1 row) ``` ##### Jsonb_populate_record_valid function {#pg17-p64-jsonb-populate-record-valid-function} The jsonb_populate_record_valid function has been added. This function returns true if the jsonb_populate_record function does not generate an error for the specified JSON object, or false if it does. [[1edb3b4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1edb3b491bee373bdfcbfc1c2707b5428c2023b9)] #### Syntax 10 - Jsonb_populate_record_valid function {#pg17-syntax-10} ```sql boolean jsonb_populate_record_valid(anyelement, jsonb) ``` **Example 63.** Execute jsonb\_populate\_record\_valid function ```text postgres=> CREATE TYPE jsb_char2 AS (a char(2)) ; CREATE TYPE postgres=> SELECT jsonb_populate_record_valid(NULL::jsb_char2, '{"a": "aaa"}') ; jsonb_populate_record_valid ----------------------------- f (1 row) ```

PDF page 65

#### 3.2.12 Functions {#pg17-3-2-12-functions} The following functions have been added/enhanced. ##### Pg_promote {#pg17-p65-pg-promote} The error message is now output if sending a signal to the postmaster process fails. [[f593c55](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f593c5517d14a949ae659eec470eb6bc0d2fdd5d)] #### Syntax 11 - Error messages that may be output {#pg17-syntax-11} ```sql ERROR: failed to send signal to postmaster: {PID} ERROR: terminating connection due to unexpected postmaster exit ``` ##### Pg_basetype {#pg17-p65-pg-basetype} The function pg_basetype has been added. This function returns the base type for a given domain. [[b154d8a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b154d8a6d0e52e5f6b09739639fdf55fa88bc6b8)] #### Syntax 12 - Pg_basetype function {#pg17-syntax-12} ```sql regtype pg_basetype(regtype) ``` **Example 64.** Execute pg\_basetype function ```text postgres=> CREATE DOMAIN mytext AS text ; CREATE DOMAIN postgres=> CREATE DOMAIN mytext_child AS mytext ; CREATE DOMAIN postgres=> SELECT pg_basetype('mytext'::regtype) ; pg_basetype ------------- text (1 row) postgres=> SELECT pg_basetype('mytext_child'::regtype) ; pg_basetype ------------- text (1 row) ```

PDF page 66

##### Random {#pg17-p66-random} The functions have been added to generate random numbers that have "minimum value ≤ random ≤ maximum value" given a minimum and maximum value. [[e634132](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e6341323a8da64b18e9af3e75a4578230702d61c)] #### Syntax 13 - Random function {#pg17-syntax-13} ```sql integer random(min integer, max integer) bigint random(min bigint, max bigint) numeric random(min numeric, max numeric) ``` **Example 65.** Execute random function ```text postgres=> SELECT random(100, 200), random(10.25, 20.34) ; random | random --------+-------- 153 | 19.21 (1 row) ``` ##### To_bin / to_oct {#pg17-p66-to-bin-to-oct} The function to convert a number to a binary string or octal string has been added. [[260a1f1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=260a1f18dae8729f99cefe4e1f759193fd6bedd0)] #### Syntax 14 - To_bin / to_oct function {#pg17-syntax-14} ```sql text to_bin(integer | bigint) text to_oct(integer | bigint) ``` **Example 66.** Execute to\_bin \/ to\_oct function ```text postgres=> SELECT TO_BIN(100) bin, TO_OCT(100) oct ; bin | oct ---------+----- 1100100 | 144 (1 row) ``` ##### To_regtypemod {#pg17-p66-to-regtypemod} The to_regtypemod function has been added. This function retrieves the typemod information of a datatype from a datatype string. This information can be used with the format_type function, etc. [[1218ca9](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1218ca9956ee60afc6975f14c1a4c953bd6bbaa7)]

PDF page 67

#### Syntax 15 - To_regtypemod function {#pg17-syntax-15} ```sql integer to_regtypemod(text) ``` **Example 67.** Execute to\_regtypemod function ```text postgres=> SELECT to_regtypemod('varchar(10)'), format_type(to_regtype('varchar(10)'), to_regtypemod('varchar(10)')) ; to_regtypemod | format_type ---------------+----------------------- 14 | character varying(10) (1 row) ``` ##### To_timestamp {#pg17-p67-to-timestamp} The to_timestamp function can now specify TZ and OF in the format string. In previous versions, it was only available in the to_char function. [[8ba6fdf](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8ba6fdf905d0f5aef70ced4504c6ad297bfe08ea)] **Example 68.** Execute to\_timestamp function with TZ\/OF ```text postgres=> SELECT TO_TIMESTAMP('2024-09-27 09:12 -05', 'YYYY-MM-DD HH12:MI OF') ; to_timestamp ------------------------ 2024-05-26 23:12:00+09 (1 row) postgres=> SELECT TO_TIMESTAMP('2024-09-27 09:12 EST', 'YYYY-MM-DD HH12:MI TZ') ; to_timestamp ------------------------ 2024-05-26 23:12:00+09 (1 row) ``` ##### UNICODE related {#pg17-p67-unicode-related} The basic functions related to UNICODE versions have been added. The unicode_version function outputs the UNICODE version used by the database cluster, and the icu_unicode_version function outputs the UNICODE version used by the ICU library. The unicode_assigned function verifies whether the specified string is assigned to UNICODE. [[a02b37f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a02b37fc083239a07f1ac02951d208235efb218b)]

PDF page 68

#### Syntax 16 - UNICODE related functions {#pg17-syntax-16} ```sql text unicode_version() text icu_unicode_version() boolean unicode_assigned(text) ``` **Example 69.** Execute UNICODE related functions ```text postgres=> SELECT unicode_version() ; -[ RECORD 1 ]---+----- unicode_version | 15.1 postgres=> SELECT icu_unicode_version() ; -[ RECORD 1 ]-------+----- icu_unicode_version | 10.0 postgres=> SELECT unicode_assigned('ABC') ; -[ RECORD 1 ]----+-- unicode_assigned | t ``` ```text The unicode_assigned function can be executed only when the database encoding is UTF-8. The following error will occur if it is executed in a database with encoding set to EUC_JP. ``` **Example 70.** Execute on EUC\_JP encoded database ```text postgres=> SELECT unicode_assigned('ABC') ; ERROR: Unicode categorization can only be performed if server encoding is UTF8 ``` ##### XMLText {#pg17-p68-xmltext} The XMLText function of the XML standard (SQL/XML X038) has been added. This function converts text information into XML text nodes. The --with-libxml option is required at PostgreSQL build time to execute this function. [[526fe0d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=526fe0d79914b2dfcfd79effd1ab26ff62469248)] #### Syntax 17 - XMLText function {#pg17-syntax-17} ```sql xml xmltext(text) ```

PDF page 69

**Example 71.** Execute xmltext function ```text postgres=> SELECT xmltext('foo & <"bar">') ; xmltext ----------------------------------- foo & <"bar"> (1 row) ``` ##### Uuid_extract_version / uuid_extract_timestamp {#pg17-p69-uuid-extract-version-uuid-extract-timestamp} The functions to extract version numbers and timestamps from UUIDs have been added. [[794f10f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=794f10f6b920670cb9750b043a2b2587059d5051)] #### Syntax 18 - UUID related functions {#pg17-syntax-18} ```sql smallint uuid_extract_version(uuid) timestamp with time zone uuid_extract_timestamp(uuid) ``` **Example 72.** Execute UUID related functions ```text postgres=> SELECT uuid_extract_version(gen_random_uuid()) ; uuid_extract_version ---------------------- 4 (1 row) postgres=> SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') ; uuid_extract_timestamp ------------------------ 2022-02-23 04:22:22+09 (1 row) ``` ##### Pg_column_toast_chunk_id {#pg17-p69-pg-column-toast-chunk-id} The newly added pg_column_toast_chunk_id function returns the chunk ID of TOAST data. [[d1162cf](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d1162cfda885c5a8cb9cebfc8eed9f1d76855e83)] #### Syntax 19 - Pg_column_toast_chunk_id functions {#pg17-syntax-19} ```sql oid pg_column_toast_chunk_id(any) ```

PDF page 70

**Example 73.** Execute pg\_column\_toast\_chunk\_id function ```text postgres=> CREATE TABLE data1 (col1 TEXT, col2 TEXT STORAGE EXTERNAL) ; CREATE TABLE postgres=> INSERT INTO data1 VALUES ('A', repeat('B', 8192)) ; INSERT 0 1 postgres=> SELECT pg_column_toast_chunk_id(col2) FROM data1 ; -[ RECORD 1 ]------------+------ pg_column_toast_chunk_id | 16412 ``` ##### Pg_stat_reset_shared {#pg17-p70-pg-stat-reset-shared} The behavior of the pg_stat_reset_shared function has changed. If the parameter to the pg_stat_reset_shared function is omitted or specified as NULL, all statistics counters are reset. Also, the pg_stat_reset_shared function parameter can now contain the string 'slru' to reset the pg_stat_slru view. [[23c8c0c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=23c8c0c8f4721db693ef908c22f7cf754e6852a9), [2e8a0ed](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2e8a0edc2a3321582d5da65690b0371654c7a44f)] #### Syntax 20 - Pg_stat_reset_shared functions {#pg17-syntax-20} ```sql void pg_stat_reset_shared(target text DEFAULT NULL::text) ``` ##### Pg_stat_reset_slru {#pg17-p70-pg-stat-reset-slru} If the parameter of the pg_stat_reset_slru function is omitted, all SLRU statistics counters are reset. [[e5cca62](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e5cca6288a4098cf731599b5977b0f6714ba0ac6)] #### Syntax 21 - Pg_stat_reset_slru functions {#pg17-syntax-21} ```sql void pg_stat_reset_slru(target text DEFAULT NULL) ``` The element name specified as a parameter to this function has been changed. Entering a string other than the changed name will reset the "other" item. [[bcdfa5f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bcdfa5f2e2f274caeed20b2f986012a9cb6a259c)]

PDF page 71

**Table 29.** Changed Element Name | changed Name | changed Name | Note | | --- | --- | --- | | CommitTs | commit_timestamp | | | MultiXactMemberMultiXactOffsetNotifySerialSubtransXact | multixact_membermultixact_offsetnotifyserializablesubtransactiontransaction | | #### 3.2.13 Optimizer {#pg17-3-2-13-optimizer} Faster execution plans are now chosen in the following cases. ##### GROUP BY optimization {#pg17-p71-group-by-optimization} If the GROUP BY clause with multiple columns is not related to the sort order, the sorting process can now be reduced by swapping the columns. This behavior is enabled by setting the parameter enable_group_by_reordering to 'on' (default 'on'). [[0452b46](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0452b461bc405e6d35d8a14c02813c15e28ae516)] **Example 74.** Execution Plan on PostgreSQL 17 ```text postgres=> EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM data1 GROUP BY col2, col1 ; QUERY PLAN ------------------------------------------------- GroupAggregate Group Key: col1, col2 -> Index Only Scan using idx1_data1 on data1 (3 rows) ```

PDF page 72

**Example 75.** Execution Plan on PostgreSQL 16 ```text postgres=> EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM data1 GROUP BY col2, col1 ; QUERY PLAN ------------------------------- GroupAggregate Group Key: col2, col1 -> Sort Sort Key: col2, col1 -> Seq Scan on data1 (5 rows) ``` ##### UNION optimization {#pg17-p72-union-optimization} The Merge Append plan can now be used for lookups that contain a UNION clause in the subquery. Previously, Sort plan was required. [[66c0185](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=66c0185a3d14bbbf51d0fc9d267093ffec735231)] **Example 76.** Execution Plan on PostgreSQL 17 ```text postgres=> EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM (SELECT c1 FROM merge1 UNION SELECT c1 FROM merge2) ; QUERY PLAN --------------------------------------------------------------- Aggregate -> Unique -> Merge Append Sort Key: merge1.c1 -> Index Only Scan using merge1_pkey on merge1 -> Index Only Scan using merge2_pkey on merge2 (6 rows) ```

PDF page 73

**Example 77.** Execution Plan on PostgreSQL 16 ```text postgres=> EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM (SELECT c1 FROM merge1 UNION SELECT c1 FROM merge2) ; QUERY PLAN -------------------------------------------- Aggregate -> Unique -> Sort Sort Key: merge1.c1 -> Append -> Seq Scan on merge1 -> Seq Scan on merge2 (7 rows) ``` ##### Better IS [NOT] NULL Handling {#pg17-p73-better-is-not-null-handling} The optimizations have been implemented to reduce unnecessary evaluation of IS NULL and IS NOT NULL clauses. [[b262ad4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b262ad440edecda0b1aba81d967ab560a83acb8a)] **Example 78.** Execution Plan on PostgreSQL 17 ```text 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) postgres=> EXPLAIN SELECT COUNT(*) FROM data1 WHERE c1 IS NOT NULL ; QUERY PLAN --------------------------------------------------------------------- Aggregate (cost=17906.00..17906.01 rows=1 width=8) -> Seq Scan on data1 (cost=0.00..15406.00 rows=1000000 width=0) (2 rows) ```

PDF page 74

**Example 79.** Execution Plan on PostgreSQL 16 ```text 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) postgres=> EXPLAIN SELECT COUNT(*) FROM data1 WHERE c1 IS NOT NULL ; QUERY PLAN --------------------------------------------------------------------- Aggregate (cost=17906.00..17906.01 rows=1 width=8) -> Seq Scan on data1 (cost=0.00..15406.00 rows=1000000 width=0) Filter: (c1 IS NOT NULL) (3 rows) ``` ##### Better Parallel DISTINCT Processing {#pg17-p74-better-parallel-distinct-processing} The Gather Merge plan is now available for parallel execution of SELECT statements with DISTINCT clauses. [[7e0ade0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7e0ade0ffe0a76b1926a4af39ecdf799c96ef1ba)] **Example 80.** Execution Plan on PostgreSQL 17 ```text postgres=> EXPLAIN (COSTS OFF) SELECT DISTINCT c2 FROM data1 ; QUERY PLAN ---------------------------------------------------- Unique -> Gather Merge Workers Planned: 2 -> Sort Sort Key: c2 -> HashAggregate Group Key: c2 -> Parallel Seq Scan on data1 (8 rows) ```

PDF page 75

**Example 81.** Execution Plan on PostgreSQL 16 ```text postgres=> EXPLAIN (COSTS OFF) SELECT DISTINCT c2 FROM data1 ; QUERY PLAN ---------------------------------------------------- Unique -> Sort Sort Key: c2 -> Gather Workers Planned: 2 -> HashAggregate Group Key: c2 -> Parallel Seq Scan on data1 (8 rows) ``` ##### GiST Index optimization {#pg17-p75-gist-index-optimization} The Incremental Sort plan is now available for GiST and SP-GiST indexes. [[625d5b3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=625d5b3ca0968c1d8c080d5210f7209184c0d134)] **Example 82.** Execution Plan on PostgreSQL 17 ```text postgres=> CREATE INDEX idx1_gist1 ON gist1 USING GIST(c1) ; CREATE INDEX postgres=> EXPLAIN (COSTS OFF) SELECT c1, c2, c1<->POINT(5,5) dist FROM gist1 ORDER BY dist, c2 LIMIT 1 ; QUERY PLAN -------------------------------------------------- Limit -> Incremental Sort Sort Key: ((c1 <-> '(5,5)'::point)), c2 Presorted Key: ((c1 <-> '(5,5)'::point)) -> Index Scan using idx1_data1 on data1 Order By: (c1 <-> '(5,5)'::point) (6 rows) ```

PDF page 76

**Example 83.** Execution Plan on PostgreSQL 16 ```text postgres=> EXPLAIN (COSTS OFF) SELECT c1, c2, c1<->POINT(5,5) dist FROM data1 ORDER BY dist, c2 LIMIT 1 ; QUERY PLAN ------------------------------------------------- Limit -> Sort Sort Key: ((c1 <-> '(5,5)'::point)), c2 -> Seq Scan on data1 (4 rows) ```