PDF page 44
This section explains new features related to SQL statements. #### 3.5.1 LOCK TABLE statement {#pg11-3-5-1-lock-table-statement} In PostgreSQL 11, it is now possible to specify a view in the LOCK TABLE statement. When locking a view, the same mode lock is applied to the table included in the view definition. **Example 51.** Execution of LOCK TABLE statement for the view ```text postgres=> CREATE TABLE data1(c1 INT, c2 VARCHAR(10)) ; CREATE TABLE postgres=> CREATE VIEW view1 AS SELECT * FROM data1 ; CREATE VIEW postgres=> BEGIN ; BEGIN postgres=> LOCK TABLE view1 IN ACCESS EXCLUSIVE MODE ; LOCK TABLE postgres=> SELECT relation::regclass, mode FROM pg_locks ; relation | mode ----------+--------------------- pg_locks | AccessShareLock | ExclusiveLock | ExclusiveLock view1 | AccessExclusiveLock data1 | AccessExclusiveLock | ExclusiveLock (6 rows) ``` ```text If the view is nested, the further lower table will be locked. However, the materialized view contained in the view is not locked. ```PDF page 45
**Example 52.** Executing LOCK TABLE statement to MATERIALIZED VIEW ```text postgres=> CREATE MATERIALIZED VIEW mview1 AS SELECT * FROM data1 ; SELECT 0 postgres=> BEGIN ; BEGIN postgres=> LOCK TABLE mview1 IN ACCESS EXCLUSIVE MODE ; ERROR: "mview1" is not a table or a view postgres=> ROLLBACK ; ROLLBACK postgres=> CREATE VIEW view2 AS SELECT * FROM mview1 ; CREATE VIEW postgres=> BEGIN ; BEGIN postgres=> LOCK TABLE view2 IN ACCESS EXCLUSIVE MODE ; LOCK TABLE postgres=> SELECT relation::regclass, mode FROM pg_locks ; relation | mode ----------+--------------------- pg_locks | AccessShareLock | ExclusiveLock | ExclusiveLock view2 | AccessExclusiveLock (4 rows) ``` #### 3.5.2 STATISTICS of function index {#pg11-3-5-2-statistics-of-function-index} It is now possible to specify a STATISTICS value for a function index column.PDF page 46
**Example 53.** STATISTICS value for function index column ```text postgres=> CREATE INDEX idx1_stat1 ON stat1 ((c1 + c2)) ; CREATE INDEX postgres=> ALTER INDEX idx1_stat1 ALTER COLUMN 1 SET STATISTICS 1000 ; ALTER INDEX postgres=> \d+ idx1_stat1 Index "public.idx1_stat1" Column | Type | Key? | Definition | Storage | Stats target --------+---------+------+------------+---------+-------------- expr | numeric | yes | (c1 + c2) | main | 1000 btree, for table "public.stat1" ``` #### 3.5.3 VACUUM statement and ANALYZE statement {#pg11-3-5-3-vacuum-statement-and-analyze-statement} ##### Multiple tables {#pg11-p46-multiple-tables} The VACUUM and ANALYZE statements can now specify multiple tables at the same time. **Example 54.** Execution of VACUUM\, ANALYZE statement for multiple tables ```text postgres=> VACUUM data1, data2 ; VACUUM postgres=> ANALYZE data1, data2 ; ANALYZE ``` ##### Output aggressively {#pg11-p46-output-aggressively} When executing the VACUUM (VERBOSE, FREEZE) statement, "aggressively" is added to the output. **Example 55.** Output \"\;aggressively\"\; ```text postgres=> VACUUM (VERBOSE, FREEZE) data1 ; INFO: aggressively vacuuming "public.data1" INFO: "data1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 575 There were 0 unused item pointers. … ```PDF page 47
#### 3.5.4 Push down the LIMIT clause {#pg11-3-5-4-push-down-the-limit-clause} The content of the LIMIT clause is now pushed to the subquery when the LIMIT clause is set for the sorted subquery. **Example 56.** Execution plan of PostgreSQL 10 ```text postgres=> EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM sort1 ORDER BY 1) AS a LIMIT 5 ; QUERY PLAN -------------------------------------------------------------------------------------- Limit (cost=56588.00..56588.54 rows=5 width=10) (actual time=1204.947..1204.958 rows=5 loops=1) -> Gather Merge (cost=56588.00..153817.09 rows=833334 width=10) (actual time=1204.945..1204.951 rows=5 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (cost=55587.98..56629.65 rows=416667 width=10) (actual time=1182.284..1182.712 rows=912 loops=3) Sort Key: sort1.c1 Sort Method: external sort Disk: 6624kB -> Parallel Seq Scan on sort1 (cost=0.00..9572.67 rows=416667 w idth=10) (actual time=0.020..481.233 rows=333333 loops=3) Planning time: 0.041 ms Execution time: 1207.299 ms (10 rows) ```PDF page 48
**Example 57.** Execution plan of PostgreSQL 11 ```text postgres=> EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM sort1 ORDER BY 1) AS a LIMIT 5 ; QUERY PLAN --------------------------------------------------------------------------------------- Limit (cost=56588.00..56588.54 rows=5 width=10) (actual time=276.900..276.910rows=5 loops=1) -> Gather Merge (cost=56588.00..153817.09 rows=833334 width=10) (actual time=276.899..276.907 rows=5 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (cost=55587.98..56629.65 rows=416667 width=10) (actual time=257.124..257.125 rows=5 loops=3) Sort Key: sort1.c1 Sort Method: top-N heapsort Memory: 25kB Worker 0: Sort Method: top-N heapsort Memory: 25kB Worker 1: Sort Method: top-N heapsort Memory: 25kB -> Parallel Seq Scan on sort1 (cost=0.00..9572.67 rows=416667 width=10) (actual time=0.020..126.640 rows=333333 loops=3) Planning Time: 0.051 ms Execution Time: 276.983 ms (12 rows) ``` #### 3.5.5 CREATE INDEX statement {#pg11-3-5-5-create-index-statement} The following enhancements have been added to the CREATE INDEX statement. ##### INCLUDE clause {#pg11-p48-include-clause} An INCLUDE clause to add columns to the index can be specified. This is effective when adding columns which are unrelated to unique constraint to a unique index etc. In the example below the UNIQUE CONSTRAINT is created in columns c1 and c2, but the index includes c3 column.PDF page 49
**Example 58.** INCLUDE clause with CREATE INDEX statement ```text postgres=> CREATE UNIQUE INDEX idx1_data1 ON data1 (c1, c2) INCLUDE (c3) ; CREATE INDEX postgres=> \d+ idx1_data1 Index "public.idx1_data1" Column | Type | Key? | Definition | Storage | Stats target --------+---------+------+------------+---------+-------------- c1 | integer | yes | c1 | plain | c2 | integer | yes | c2 | plain | c3 | integer | no | c3 | plain | unique, btree, for table "public.data1" ``` ```sql The INCLUDE clause can also be used for the constraint specification part of the CREATE TABLE statement as the CREATE INDEX statement. ``` **Example 59.** INCLUDE clause with CREATE TABLE statement ```text postgres=> CREATE TABLE data2 (c1 INT, c2 INT, c3 INT, c4 VARCHAR(10), CONSTRAINT data2_pkey PRIMARY KEY (c1, c2) INCLUDE (c3)) ; CREATE TABLE postgres=> \d data2 Table "public.data2" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | integer | | not null | c2 | integer | | not null | c3 | integer | | | c4 | character varying(10) | | | Indexes: "data2_pkey" PRIMARY KEY, btree (c1, c2) INCLUDE (c3) ``` ##### Surjective indexes {#pg11-p49-surjective-indexes} The recheck_on_update option can now be specified in the WITH clause of the CREATE INDEX statement. The default value is "on". This parameter specifies whether to update the function index by HOT.PDF page 50
**Example 60.** The option of CREATE INDEX statement ```text postgres=> CREATE INDEX idx1_data1 ON data1(upper(c2)) WITH (recheck_on_update = on) ; CREATE INDEX postgres=> \d+ idx1_data1 Index "public.idx1_data1" Column | Type | Definition | Storage | Stats target --------+------+-----------------+----------+-------------- upper | text | upper(c2::text) | extended | btree, for table "public.data1" Options: recheck_on_update=on ``` #### 3.5.6 CREATE TABLE statement {#pg11-3-5-6-create-table-statement} It is now possible to specify the storage parameter toast_tuple_target indicating the threshold value for TOAST conversion. The default value is the same as before. **Example 61.** Storage option for CREATE TABLE statement ```text postgres=> CREATE TABLE toast1(c1 INT, c2 VARCHAR(10)) WITH (toast_tuple_target = 1024) ; CREATE TABLE postgres=> \d+ toast1 Table "public.toast1" Column | Type | Collation | Nullable | Default | Storage | --------+-----------------------+-----------+----------+---------+----------+ c1 | integer | | | | plain | c2 | character varying(10) | | | | extended | Options: toast_tuple_target=1024 ``` #### 3.5.7 WINDOW function {#pg11-3-5-7-window-function} The GROUPS clause and EXCLUDE clause in the window frame specification can now be specified in the WINDOWS function. Also, float 4 type, float 8 type, and numeric type can be used for RANGE clause.PDF page 51
Syntax ```text { RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ] ``` The following syntax can be specified in the frame_exclusion clause. Syntax (Part of the frame_exclusion) ```text EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS ``` #### 3.5.8 EXPLAIN statement {#pg11-3-5-8-explain-statement} Information on sorting is now displayed for each worker when executing a parallel query. **Example 62.** Parallel query executed in EXPLAIN statement ```text postgres=> EXPLAIN ANALYZE VERBOSE SELECT * FROM part1 ORDER BY 1 ; QUERY PLAN ------------------------------------------------------------------------------ Gather Merge (cost=120509.21..314967.15 rows=1666666 width=10) (actual …) Output: part1v2.c1, part1v2.c2 Workers Planned: 2 Workers Launched: 2 -> Sort (cost=119509.18..121592.52 rows=833333 width=10) (actual time …) Output: part1v2.c1, part1v2.c2 Sort Key: part1v2.c1 Sort Method: external merge Disk: 13736kB Worker 0: Sort Method: external merge Disk: 12656kB Worker 1: Sort Method: external merge Disk: 12816kB Worker 0: actual time=267.130..357.999 rows=645465 loops=1 Worker 1: actual time=268.723..350.636 rows=653680 loops=1 -> Parallel Append (cost=0.00..23311.99 rows=833333 width=10) (actual …) … ```PDF page 52
#### 3.5.9 Functions {#pg11-3-5-9-functions} The following functions have been added/extended. ##### Added hash functions {#pg11-p52-added-hash-functions} Hash functions using SHA-224 / SHA-256 / SHA-384 / SHA-512 has been provided. All usages are the same. **Table 15.** Added hash functions | Functionname | Description | | --- | --- | | sha224(bytea) | Calculate the SHA-224 hash value | | sha256(bytea) | Calculate the SHA-256 hash value | | sha384(bytea) | Calculate the SHA-384 hash value | | sha512(bytea) | Calculate the SHA-512 hash value | **Example 63.** SHA512 hash function ```text postgres=> SELECT sha512('ABC') ; sha512 ----------------------------------------------------------------------- \x397118fdac8d83ad98813c50759c85b8c47565d8268bf10da483153b747a74743a58a 90e85aa9f705ce6984ffc128db567489817e4092d050d8a1cc596ddc119 (1 row) ``` ##### json(b)_to_tsvector function {#pg11-p52-json-b-to-tsvector-function} The json(b)_to_tsvector function to convert from JSON type (or JSONB type) to tsvector type has been added. **Example 64.** json\_to\_tsvector function ```text postgres=> SELECT jsonb_to_tsvector('english', '{"a": "The Fat Rats", "b": 123}'::json, '["string", "numeric"]') ; json_to_tsvector ------------------------- '123':5 'fat':2 'rat':3 (1 row) ```PDF page 53
##### websearch_to_tsquery function {#pg11-p53-websearch-to-tsquery-function} The websearch_to_tsquery function to convert Web search format string to tsquery type has been added. **Example 65.** websearch\_to\_tsquery function ```text postgres=> SELECT websearch_to_tsquery('english', '"fat rat" or rat') ; websearch_to_tsquery ------------------------- 'fat' <-> 'rat' | 'rat' (1 row) ``` #### 3.5.10 Operator {#pg11-3-5-10-operator} The following operator has been added. ##### String forward match search {#pg11-p53-string-forward-match-search} An operator "^ @" to search forward matches for strings has been added. It can be used for the same purpose as "LIKE 'character_string%'" in the WHERE clause. Syntax ```text search_target ^@ search_text ``` **Example 66.** \^\@ operator ```text postgres=> SELECT usename FROM pg_user WHERE usename ^@ 'po' ; usename ---------- postgres (1 row) ``` ```text Unlike the LIKE clause, this operator does not use the B-Tree index. Although not verified, the SP- GiST index is available. ```PDF page 54
#### 3.5.11 Others {#pg11-3-5-11-others} ##### Cast from JSONB type {#pg11-p54-cast-from-jsonb-type} Casting from JSONB type to bool type and numerical type became possible. **Example 67.** Casting from JSONB type ```text postgres=> SELECT 'true'::jsonb::bool ; bool ------ t (1 row) postgres=> SELECT '1.0'::jsonb::float ; float8 -------- 1 (1 row) postgres=> SELECT '12345'::jsonb::int4 ; int4 ------- 12345 (1 row) postgres=> SELECT '12345'::jsonb::numeric ; numeric --------- 12345 (1 row) ``` ##### Enhancement of SP-GiST index {#pg11-p54-enhancement-of-sp-gist-index} The poly_ops operator class that can be created for polygon type has been provided. Also, userdefined methods to compress can be defined.