This section explains new features related to SQL statements.
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 51Execution of LOCK TABLE statement for the view
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 52Executing LOCK TABLE statement to MATERIALIZED VIEW
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
It is now possible to specify a STATISTICS value for a function index column.
PDF page 46
Example 53STATISTICS value for function index column
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
Multiple tables
The VACUUM and ANALYZE statements can now specify multiple tables at the same time.
Example 54Execution of VACUUM, ANALYZE statement for multiple tables
The following enhancements have been added to the CREATE INDEX statement.
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 58INCLUDE clause with CREATE INDEX statement
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"
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 60The option of CREATE INDEX statement
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
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 61Storage option for CREATE TABLE statement
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
{ 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)
EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS
3.5.8 EXPLAIN statement
Information on sorting is now displayed for each worker when executing a parallel query.
Example 62Parallel query executed in EXPLAIN statement
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
search_target ^@ search_text
Example 66^@ operator
postgres=> SELECT usename FROM pg_user WHERE usename ^@ 'po' ;
usename
----------
postgres
(1 row)
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
Cast from JSONB type
Casting from JSONB type to bool type and numerical type became possible.