postgres=> ALTER FUNCTION func1 DEPENDS ON EXTENSION cube ;
ALTER FUNCTION
postgres=> ALTER FUNCTION func1 NO DEPENDS ON EXTENSION cube ;
ALTER FUNCTION
3.2.2 ALTER STATISTICS SET STATISTICS
Specify the statistics collection target when executing ANALYZE. The value that can be set is from 0 to 10,000. If you specify 10,000 or more, it is assumed to be 10,000. Specify -1 to reset to the default value. Syntax
The specified value can be checked in the stxstattarget column of the pg_statistic_ext catalog or with the command “\d” in the psql utility.
PDF page 36
Example 28ALTER STATISTICS SET STATISTICS statement
postgres=> CREATE TABLE data1(c1 INT, c2 INT, c3 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE STATISTICS stat1 ON c1, c2 FROM data1 ;
CREATE STATISTICS
postgres=> ALTER STATISTICS stat1 SET STATISTICS 10000 ;
ALTER STATISTICS
postgres=> SELECT stxname, stxstattarget FROM pg_statistic_ext
WHERE stxname='stat1' ;
stxname | stxstattarget
---------+---------------
stat1 | 10000
(1 row)
postgres=> \d data1
Table "public.data1"
Column | Type | Collation | Nullable | Default
--------+--------------------+-----------+----------+---------
c1 | integer | | |
c2 | integer | | |
c3 | character varying(10) | | |
Statistics objects:
"public"."stat1" (ndistinct, dependencies, mcv) ON c1, c2 FROM
data1; STATISTICS 10000
3.2.3 ALTER TABLE
The following enhancements have been added to the ALTER TABLE statement.
ALTER TABLE ALTER COLUMN DROP EXPRESSION
By executing the ALTER TABLE ALTER COLUMN DROP EXPRESSION statement, it is now possible to drop an auto-created definition from a column specified with the GENERATED ALWAYS clause. Simply drop the calculation information and the column values will remain. IF EXISTS clause can be specified.
Alters to a column’s STORAGE attribute are now propagated to the index. This feature has been backported to the latest versions of PostgreSQL 12 and PostgreSQL 11.
Example 30ALTER TABLE SET STORAGE statement
postgres=> CREATE TABLE attr1(c1 CHAR(10)) ;
CREATE TABLE
postgres=> \d+ attr1
Table "public.attr1"
Column | Type | Collation | Nullable | Default | Storage | …
--------+-------------+-----------+----------+---------+----------+ …
c1 | character(10) | | | | extended | …
Access method: heap
postgres=> CREATE INDEX idx1_attr1 ON attr1(c1) ;
CREATE INDEX
postgres=> ALTER TABLE attr1 ALTER COLUMN c1 SET STORAGE EXTERNAL ;
ALTER TABLE
postgres=> \d+ idx1_attr1
Index "public.idx1_attr1"
Column | Type | Key? | Definition | Storage | Stats target
--------+-------------+------+------------+----------+--------------
c1 | character(10) | yes | c1 | external |
btree, for table "public.attr1"
3.2.4 ALTER TYPE
In the ALTER TYPE statement, attributes can be changed using the SET clause. Syntax
ALTERTYPEtype_nameSET(attribute=value)
The attributes that can be changed are as follows. Part of the attributes that can be specified in the CREATE TYPE statement.
PDF page 39
Attribute name
Description
RECEIVE
The function that converts external representation into an internal representation.
SEND
The function to convert from the internal representation to binary.
TYPMOD_IN
Modifier support function.
TYPMOD_OUT
Modifier support function.
ANALYZE
Statistics collection function.
STORAGE
The function that determines how to store variable-length data.
Table 20Attributes that can be changed
Example 31ALTER TYPE SET statement
postgres=# ALTER TYPE box SET (SEND = myboxsend) ;
ALTER TYPE
3.2.5 ALTER VIEW
Column names in views can now be changed. Execute an ALTER VIEW RENAME COLUMN statement. Syntax
ALTERVIEWview_nameRENAMECOLUMNold_nameTOnew_name
PDF page 40
Example 32Rename view column name
postgres=> CREATE VIEW customer_name AS SELECT c_customer_id,
c_first_name || ',' || c_last_name c_full_name FROM customer ;
CREATE VIEW
postgres=> \d customer_name
View "public.customer_name"
Column | Type | Collation | Nullable | Default
---------------+---------------+-----------+----------+---------
c_customer_id | character(16) | | |
c_full_name | text | | |
postgres=> ALTER VIEW customer_name RENAME COLUMN c_full_name TO full_name ;
ALTER VIEW
postgres=> \d customer_name
View "public.customer_name"
Column | Type | Collation | Nullable | Default
---------------+---------------+-----------+----------+---------
c_customer_id | character(16) | | |
full_name | text | | |
3.2.6 CREATE DATABASE
The LOCALE clause can now be specified as an option in the CREATE DATABASE statement. It cannot be specified at the same time as LC_CTYPE clause or LC_COLLATE clause. Syntax
In-page deduplication has been added to the B-Tree index. This feature is enabled by default. To disable this feature, set the deduplicate_items attribute to ‘off’.
Example 34B-Tree index deduplication
postgres=> CREATE TABLE data1(c1 INT, c2 VARCHAR(10), c3 VARCHAR(10)) ;
CREATE TABLE
postgres=> INSERT INTO data1 VALUES (generate_series(1, 1000000),
'data1', 'data1') ;
INSERT 0 1000000
postgres=> CREATE INDEX idx1_dup ON data1(c2)
WITH (deduplicate_items = off) ;
CREATE INDEX
postgres=> CREATE INDEX idx2_dedup ON data1(c3)
WITH (deduplicate_items = on) ;
CREATE INDEX
postgres=> \d+ idx1_dup
Index "public.idx1_dup"
Column | Type | Key? | Definition | Storage | Stats target
--------+-----------------------+------+------------+----------+--
c2 | character varying(10) | yes | c2 | extended |
btree, for table "public.data1"
Options: deduplicate_items=off
Some attributes4 can be specified in the CREATE TABLE/ALTER TABLE statement.
Example 36Specifying additional attributes for tables
postgres=> CREATE TABLE data1(c1 NUMERIC, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> ALTER TABLE data1 SET (toast.vacuum_index_cleanup = off) ;
ALTER TABLE
postgres=> ALTER TABLE data1 SET (autovacuum_vacuum_insert_threshold = 10000) ;
ALTER TABLE
postgres=> ALTER TABLE data1 SET (autovacuum_vacuum_insert_scale_factor =
0.1) ;
ALTER TABLE
3.2.9 CREATE TABLESPACE
Maintenance_io_concurrency5 can now be specified for tablespace attributes.
4 See 3.3.1 Added parameters 5 See 3.3.1 Added parameters
PDF page 43
Example 37Specify maintenance_io_concurrency attribute
postgres=# CREATE TABLESPACE ts1 LOCATION '/home/postgres/ts1'
WITH (maintenance_io_concurrency = 20) ;
CREATE TABLESPACE
postgres=# ALTER TABLESPACE ts1 SET (maintenance_io_concurrency = 30) ;
ALTER TABLESPACE
postgres=# \db+ ts1
List of tablespaces
Name | Owner | Location | Access privileges | Options
| Size | Description
------+----------+--------------------+-------------------+--------------
-------------------+---------+-------------
ts1 | postgres | /home/postgres/ts1 | |
{maintenance_io_concurrency=30} | 0 bytes |
(1 row)
3.2.10 DROP DATABASE FORCE
The database can now be forcibly dropped even if there are connected session exists. The connected session is forcibly closed. The database to which the session issuing the DROP DATABASE statement is connected cannot be dropped. Syntax
DROP DATABASE database_name [[WITH] (FORCE)]
Example 38DROP DATABASE statement
postgres=# DROP DATABASE demodb WITH (FORCE) ;
DROP DATABASE
postgres=# DROP DATABASE postgres WITH (FORCE) ;
ERROR: cannot drop the currently open database
3.2.11 EXPLAIN ANALYZE
The following extensions have been implemented in the EXPLAIN ANALYZE statement.
PDF page 44
The output of cache information
By specifying BUFFERS, the status of the shared buffer when creating an execution plan is now output.
Example 39EXPLAIN (ANALYZE, BUFFERS) statement
postgres=> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM data1 WHERE c1
= 10000 ;
QUERY PLAN
-----------------------------------------------------------------
Index Scan using idx1_data1 on data1 (cost=0.42..8.44 rows=1
width=16) (actual time=0.029..0.030 rows=1 loops=1)
Index Cond: (c1 = 10000)
Buffers: shared hit=1 read=3
Planning:
Buffers: shared hit=17 read=1
Planning Time: 1.380 ms
Execution Time: 0.044 ms
(7 rows)
If ANALYZE clause is not specified, the planner's buffer usage is output.
Example 40EXPLAIN (BUFFERS) statement
postgres=> EXPLAIN (BUFFERS) SELECT * FROM data1 WHERE c1=100 ;
QUERY PLAN
-----------------------------------------------------------------
Index Scan using idx1_data1 on data1 (cost=0.43..19.68 rows=4
width=16)
Index Cond: (c1 = 100)
Planning:
Buffers: shared hit=93 read=3
(4 rows)
Output sort information
The output of the EXPLAIN ANALYZE statement when executing a parallel query has changed. JIT function information is now output for each worker. Also, the output of sort information has changed.
PDF page 45
Example 41Output of the EXPLAIN ANALYZE statement (JIT)
If the WAL option is specified in the EXPLAIN ANALYZE statement, the generated WAL information will be output.
PDF page 47
Example 43EXPLAIN (ANALYZE, WAL) statement
postgres=> EXPLAIN (ANALYZE, WAL) DELETE FROM data1 WHERE c1 = 20000 ;
QUERY PLAN
--------------------------------------------------------------------
Delete on data1 (cost=0.00..17906.00 rows=1 width=6) (actual
time=56.817..56.818 rows=0 loops=1)
WAL: records=2 fpi=1 bytes=8237
-> Seq Scan on data1 (cost=0.00..17906.00 rows=1 width=6) (actual
time=1.269..56.800 rows=1 loops=1)
Filter: (c1 = 20001)
Rows Removed by Filter: 999998
WAL: records=1 fpi=1 bytes=8183
Planning Time: 0.085 ms
Execution Time: 56.843 ms
(8 rows)
3.2.12 INSERT
Specifying a value using the INSERT OVERRIDING USER VALUE statement for an IDENTITY column with the GENERATED ALWAYS clause specified is now allowed. However, the value specified for the IDENTITY column is ignored.
Example 44INSERT OVERRIDING USER VALUE statement
postgres=> CREATE TABLE gen1(c1 INT GENERATED ALWAYS AS IDENTITY,
c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> INSERT INTO gen1 OVERRIDING USER VALUE VALUES
(100, 'data1') ;
INSERT 0 1
postgres=> SELECT * FROM gen1 ;
c1 | c2
----+-------
1 | data1
(1 row)
PDF page 48
3.2.13 JSON
The following JSON related features have been added.
Allow Unicode escape
Unicode escapes are now allowed for character literals and identifiers, even if the server’s character encoding is not UTF-8.
Datetime method of jsonpath
The datetime method that converts a character string to a date/time type has been added.
The WITH TIES clause can be specified in the SELECT FETCH FIRST statement. It outputs the same number of tuples as the number of tuples specified in the ROWS clause.
Example 48SELECT FETCH FIRST WITH TIES statement
postgres=> SELECT * FROM data1 WHERE c1 < 4 ORDER BY c1
FETCH FIRST 2 ROWS ONLY ;
c1 | c2
----+-------
1 | data1
1 | data1
(2 rows)
postgres=> SELECT * FROM data1 WHERE c1 < 4 ORDER BY c1
FETCH FIRST 2 ROWS WITH TIES ;
c1 | c2
----+-------
1 | data1
1 | data1
1 | data1
(3 rows)
3.2.17 VACUUM PARALLEL
VACUUM statements for indexes can now be parallelized. Parallelism works by default, and the degree of parallelism is determined by the number of indexes created on the table. To specify the degree of parallelism, specify a value between 0 and 1024 in the PARALLEL clause. This feature does not work with automatic VACUUM.
PDF page 50
Example 49PARALLEL specification of VACUUM statement
postgres=> VACUUM (PARALLEL 4, VERBOSE) data1 ;
INFO: vacuuming "public.data1"
INFO: launched 2 parallel vacuum workers for index vacuuming (planned: 2)
INFO: scanned index "idx3_data1" to remove 1000 row versions by parallel
vacuum worker
DETAIL: CPU: user: 0.39 s, system: 0.11 s, elapsed: 12.45 s
INFO: scanned index "idx2_data1" to remove 1000 row versions by parallel
vacuum worker
DETAIL: CPU: user: 0.39 s, system: 0.12 s, elapsed: 12.56 s
INFO: scanned index "idx1_data1" to remove 1000 row versions
DETAIL: CPU: user: 0.47 s, system: 0.17 s, elapsed: 53.26 s
INFO: "data1": removed 1000 row versions in 1000 pages
…
3.2.18 Operator <->
Distance operators (<->) can now be used between several data types. GiST index and SP-GiST index can be used in box_ops operator class.
Example 50Operator <->
postgres=> CREATE TABLE point_tbl(f1 point) ;
CREATE TABLE
postgres=> CREATE TABLE box_tbl(f1 box) ;
CREATE TABLE
postgres=> SELECT p.f1, b.f1, p.f1 <-> b.f1 AS dist_pb,
b.f1 <-> p.f1 AS dist_bp FROM point_tbl p, box_tbl b ;
f1 | f1 | dist_pb | dist_bp
----+----+---------+---------
(0 rows)
3.2.19 Functions
The following functions have been added/enhanced.
PDF page 51
Gen_random_uuid
The gen_random_uuid function has been provided to generate a random UUID.
The min_scale function obtains the minimum number of digits from the NUMERIC type, excluding trailing zeros after the decimal point. The trim_scale function gets the value of the NUMERIC type excluding trailing zeros after the decimal point.
The jsonb_set_lax function is the same as the jsonb_set function except that a null_value_treatment parameter (text type) has been added. The following values can be specified for this parameter.
The lcm function, which returns the least common multiple, and the gcd function, which returns the greatest common divisor, have been added. Specify two numbers of the same type in the parameter. If both parameters are 0, these functions return 0.
The offset parameter of the get_bit / set_bit function that specifies the bytea type has been changed from integer type to bigint type.
Unicode normalization
The normalize function that performs UNICODE normalization and the ‘is_normalized’ function that checks whether it is normalized has been implemented. You can also use the ‘is normalized’ syntax to check the specified text. Syntax
text is [not] [form] normalized
In the [form] part, NFC (default), NFD, NFKC, or NFKD can be specified.
This function resets the counter value in the pg_stat_slru catalog. If NULL is specified for the parameter of this function, all counters will be reset. By specifying the counter name (’name’ column of pg_stat_slru catalog) to this function, the specified counter is reset.