PDF page 66
This section explains enhancement of SQL statements. #### 3.9.1 UPDATE statement and ROW keyword {#pg10-3-9-1-update-statement-and-row-keyword} The ROW keyword can be used for UPDATE statement. **Example 67.** UPDATE statement with ROW keyword ```text postgres=> UPDATE pgbench_tellers SET (bid, tbalance) = ROW (2, 1) WHERE tid = 10; UPDATE 1 ``` #### 3.9.2 CREATE STATISTICS statement {#pg10-3-9-2-create-statistics-statement} With the CREATE STATISTICS statement, it is now possible to gather statistical information on multiple column correlations. The timing at which the statistical values are actually collected is when the ANALYZE statement is executed. #### Syntax 12 - CREATE STATISTICS statement {#pg10-syntax-12} ```sql CREATE STATISTICS [ IF NOT EXISTS ] stat_name [ ( stat_type [ , … ] ) ] ON col1, col2 [, ... ] FROM table_name ``` For stat_name, specify the name of the extended statistics. It can also be qualified with a schema name. At least two columns must be specified. For stat_type, "dependencies", "ndistinct" can be specified. If omitted, both are assumed to be specified. To alter the extended statistics, execute the ALTER STATISTICS statement. #### Syntax 13 - ALTER STATISTICS statement {#pg10-syntax-13} ```sql ALTER STATISTICS stat_name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER STATISTICS stat_name RENAME TO new_name ALTER STATISTICS stat_name SET SCHEMA new_schema ``` To drop extended extensions, execute the DROP STATISTICS statement. #### Syntax 14 - DROP STATISTICS statement {#pg10-syntax-14} ```sql DROP STATISTICS [ IF EXISTS ] name [, ...] ```PDF page 67
**Example 68.** Creating extended statistics with the CREATE STATISTICS statement ```text postgres=> CREATE TABLE stat1(c1 NUMERIC, c2 NUMERIC, c3 VARCHAR(10)) ; CREATE TABLE postgres=> INSERT INTO stat1 VALUES(generate_series(1, 100000) / 5, generate_series(1, 100000) / 10, 'init') ; INSERT 0 100000 postgres=> CREATE STATISTICS stat1_stat1 ON c1, c2 FROM stat1 ; CREATE STATISTICS ``` **Example 69.** Confirm the information of the table that created the extended statistics ```text postgres=> \d stat1 Table "public.stat1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | | c2 | numeric | | | c3 | character varying(10) | | | Statistics objects: "public"."stat1_stat1" (ndistinct, dependencies) ON c1, c2 FROM stat1 ``` ```text Information of the extended statistics can be checked in the pg_statistic_ext catalog. ``` **Example 70.** Confirm extended statistics ```text postgres=> SELECT * FROM pg_statistic_ext ; -[ RECORD 1 ]---+----------------------------------------- stxrelid | 16575 stxname | stat1_stat1 stxnamespace | 2200 stxowner | 16454 stxkeys | 1 2 stxkind | {d,f} stxndistinct | {"1, 2": 19982} stxdependencies | {"1 => 2": 1.000000, "2 => 1": 0.170467} ```PDF page 68
#### 3.9.3 GENERATED AS IDENTITY column {#pg10-3-9-3-generated-as-identity-column} The GENERATED AS IDENTITY constraint has been added to the CREATE TABLE statement to automatically assign a unique value to a column. It is almost the same function as the "serial" type which can be used in the conventional version, but some specifications are different. The GENERATED AS IDENTITY constraint can be added to more than one column. Both the serial type and the GENERATED AS IDENTITY constraint internally use the SEQUENCE object. #### Syntax 15 - CREATE TABLE statement (column definition) {#pg10-syntax-15} ```sql column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_option ) ] ``` SMALLINT, INT, BIGINT can be used for the data type (type). When creating a table using the LIKE clause, the GENERATED constraint is not inherited. Only the NOT NULL constraint is inherited. To add a GENERATED AS IDENTITY constraint to an existing column, execute the ALTER TABLE statement. A NOT NULL constraint is required for the specified column. #### Syntax 16 - ALTER TABLE statement (add constraint) {#pg10-syntax-16} ```sql ALTER TABLE table_name ALTER COLUMN column_name ADD GENERATED { ALWAYS | BY DEFAUT } AS IDENTITY { ( sequence_option ) } ``` #### Syntax 17 - ALTER TABLE statement (drop constraint) {#pg10-syntax-17} ```sql ALTER TABLE table_name ALTER COLUMN column_name DROP IDENTITY [ IF EXISTS ] ``` #### Syntax 18 - ALTER TABLE statement (update constraint) {#pg10-syntax-18} ```sql ALTER TABLE table_name ALTER COLUMN column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } ``` Information on the columns created with the above syntax is stored in the "columns" table of the information_schema schema. In the past, the is_identity column was "NO", and the other information was NULL. ##### GENERATED ALWAYS {#pg10-p68-generated-always} Columns specified with GENERATED ALWAYS are prohibited from setting column values from the application by the INSERT statement or updating to values other than the DEFAULT value by thePDF page 69
UPDATE statement. **Example 71.** GENERATED ALWAYS ```text postgres=> CREATE TABLE ident1 (c1 bigint GENERATED ALWAYS AS IDENTITY, c2 VARCHAR(10)) ; CREATE TABLE demodb=> \d ident1 Table "public.ident1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+------------------------------ c1 | bigint | | not null | generated always as identity c2 | character varying(10) | | | postgres=> INSERT INTO ident1(c1, c2) VALUES (1, 'data1') ; ERROR: cannot insert into column "c1" DETAIL: Column "c1" is an identity column defined as GENERATED ALWAYS. HINT: Use OVERRIDING SYSTEM VALUE to override. postgres=> INSERT INTO ident1(c2) VALUES ('data1') ; INSERT 0 1 postgres=> UPDATE ident1 SET c1=2 WHERE c1=1 ; ERROR: column "c1" can only be updated to DEFAULT DETAIL: Column "c1" is an identity column defined as GENERATED ALWAYS. postgres=> UPDATE ident1 SET c1=DEFAULT WHERE c1=1 ; UPDATE 1 ``` ```sql Any value can be stored on GENERATED column by specifying the OVERRIDING SYSTEM VALUE clause in the INSERT statement. ``` **Example 72.** OVERRIDING SYSTEM VALUE clause ```text postgres=> INSERT INTO ident1 OVERRIDING SYSTEM VALUE VALUES (100, 'data1') ; INSERT 0 1 ``` ##### GENERATED BY DEFAULT {#pg10-p69-generated-by-default} When GENERATED BY DEFAULT clause is specified, the automatic numbering column is updatable. It has the same behavior as the "serial" type column.PDF page 70
**Example 73.** GENERATED BY DEFAULT ```text postgres=> CREATE TABLE ident2 (c1 bigint GENERATED BY DEFAULT AS IDENTITY, c2 VARCHAR(10)) ; CREATE TABLE postgres=> INSERT INTO ident2 VALUES (1, 'data1') ; INSERT 0 1 postgres=> INSERT INTO ident2(c2) VALUES ('data2') ; INSERT 0 1 postgres=> UPDATE ident2 SET c1=2 WHERE c2='data2' ; UPDATE 1 ``` #### 3.9.4 ALTER TYPE statement {#pg10-3-9-4-alter-type-statement} It is possible to change the name of the ENUM type by using the ALTER TYPE statement. #### Syntax 19 - ALTER TYPE RENAME VALUE statement {#pg10-syntax-19} ```sql ALTER TYPE type_name RENAME VALUE existing_val TO replace_val ``` **Example 74.** Change of ENUM type by ALTER TYPE statement ```text postgres=> CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy') ; CREATE TYPE postgres=> ALTER TYPE mood RENAME VALUE 'ok' TO 'good' ; ALTER TYPE ``` #### 3.9.5 CREATE SEQUENCE statement {#pg10-3-9-5-create-sequence-statement} The data type can be specified in the CREATE SEQUENCE statement. The data types that can be specified are SMALLINT, INTEGER, and BIGINT (default). The range of sequence values is limited to the range of data types. #### Syntax 20 - CREATE SEQUENCE statement {#pg10-syntax-20} ```sql CREATE SEQUENCE sequence_name [ AS type ] [ INCREMENT … ] ```PDF page 71
**Example 75.** Specify SMALLINT type in CREATE SEQUENCE statement ```text postgres=> CREATE SEQUENCE seq1 AS SMALLINT ; CREATE SEQUENCE ``` ```sql It is also possible to change the data type with the ALTER SEQUENCE AS statement. If the data type is changed, the maximum value of SEQUENCE will also be updated. However, changes to reduce the current sequence value are not allowed. ``` #### 3.9.6 COPY statement {#pg10-3-9-6-copy-statement} A COPY statement can now be executed on a simple view with the INSTEAD OF INSERT trigger. **Example 76.** COPY statement for VIEW ```text postgres=> CREATE TABLE instead1(c1 NUMERIC, c2 VARCHAR(10)) ; CREATE TABLE postgres=> CREATE VIEW insteadv1 AS SELECT c1, c2 FROM instead1 ; CREATE VIEW postgres=> CREATE OR REPLACE FUNCTION view_insert_row1() RETURNS trigger AS $$ BEGIN INSERT INTO instead1 VALUES (new.c1, new.c2); RETURN new; END; $$ LANGUAGE plpgsql ; CREATE FUNCTION postgres=> CREATE TRIGGER insteadv1_insert INSTEAD OF INSERT ON insteadv1 FOR EACH ROW EXECUTE PROCEDURE view_insert_row1() ; CREATE TRIGGER postgres=# COPY insteadv1 FROM '/home/postgres/instead.csv' ; COPY 2 ``` #### 3.9.7 CREATE INDEX statement {#pg10-3-9-7-create-index-statement} "autosummarize" can now be specified in the WITH clause of the CREATE INDEX statement that creates a BRIN index. When this is specified, it specifies that summarization is performed on thePDF page 72
previous page when data is inserted in the page. **Example 77.** Enhancement of BRIN index ```text postgres=> CREATE INDEX idx1_brin1 ON brin1 USING brin (c1) WITH (autosummarize) ; CREATE INDEX postgres=> \d brin1 Table "public.brin1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | | c2 | character varying(10) | | | Indexes: "idx1_brin1" brin (c1) WITH (autosummarize='true') ``` #### 3.9.8 CREATE TRIGGER statement {#pg10-3-9-8-create-trigger-statement} The REFERENCING clause can be used in the CREATE TRIGGER statement. It became possible to specify the table name to store the update difference. This setting can be set only for AFTER trigger. #### Syntax 21 - CREATE TRIGGER statement {#pg10-syntax-21} ```sql CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } … [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ] [ FOR [ EACH ] { ROW | STATEMENT } ] … ``` #### 3.9.9 DROP FUNCTION statement {#pg10-3-9-9-drop-function-statement} More than one FUNCTION can now be specified in the DROP FUNCTION statement. To specify multiple FUNCTIONs, separate them with a comma (,).PDF page 73
#### 3.9.10 ALTER DEFAULT PRIVILEGE statement {#pg10-3-9-10-alter-default-privilege-statement} The ON SCHEMAS clause can now be specified in the GRANT and REVOKE clauses of the ALTER DEFAULT PRIVILEGE statement. In the conventional version, it was ON FUNCTIONS, ON SEQUENCES, ON TABLES, and ON TYPES only. #### 3.9.11 CREATE SERVER statement {#pg10-3-9-11-create-server-statement} The IF NOT EXISTS clause is now available in CREATE SERVER and CREATE USER MAPPING statements. #### 3.9.12 CREATE USER statement {#pg10-3-9-12-create-user-statement} The UNENCRYPTED clause can no longer be used in CREATE USER, CREATE ROLE, and ALTER USER statements. Passwords are no longer stored in the pg_shadow catalog without being converted. **Example 78.** UNENCRYPTED clause ```text postgres=# CREATE USER user1 UNENCRYPTED PASSWORD 'user1' ; ERROR: UNENCRYPTED PASSWORD is no longer supported LINE 1: CREATE USER user1 UNENCRYPTED PASSWORD 'user1' ; ^ HINT: Remove UNENCRYPTED to store the password in encrypted form instead. ``` #### 3.9.13 Functions {#pg10-3-9-13-functions} The following functions have been added or enhanced. ##### Delete element from JSONB array {#pg10-p73-delete-element-from-jsonb-array} Elements can be deleted from the JSONB array. **Example 79.** Delete element from JSONB array ```text postgres=> SELECT '{"a":1 , "b":2, "c":3}'::jsonb - '{a,c}'::text[] ; ?column? ---------- {"b": 2} (1 row) ```PDF page 74
##### pg_current_logfile {#pg10-p74-pg-current-logfile} The pg_current_logfile function returns the path of the output log file. Path including configuration parameter log_directory can be acquired. NULL is returned if the configuration parameter log_destination is set to "syslog" or the configuration parameter logging_collector is set to "off". SUPERUSER privilege is required to execute this function. **Example 80.** pg\_current\_logfile function ```text postgres=# SELECT pg_current_logfile() ; pg_current_logfile ----------------------------------------- log/postgresql-2017-05-20_092939.log (1 row) ``` ##### xmltable {#pg10-p74-xmltable} An xmltable function is provided that obtains tabular output from XML data. In order to use this function, it is necessary to specify --with-libxml as a parameter of the "configure" command at the time of installation. Also, in order to build the binary with the --with-libxml parameter, the following packages need to be installed (For Red Hat Enterprise Linux 7). - libxml2 (version >= 2.6.23) - libxml2-devel - xz-develPDF page 75
**Example 81.** xmltable function ```text postgres=> SELECT xmltable.* postgres-> FROM xmldata, postgres-> XMLTABLE('//ROWS/ROW' postgres(> PASSING data postgres(> COLUMNS id int PATH '@id', postgres(> ordinality FOR ORDINALITY, postgres(> "COUNTRY_NAME" text, postgres(> country_id text PATH 'COUNTRY_ID', postgres(> size_sq_km float PATH 'SIZE[@unit = "sq_km"]', postgres(> size_other text PATH postgres(> 'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)', postgres(> premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') ; id | ordinality | COUNTRY_NAME | country_id | size_sq_km | size_other | premier_name ----+------------+--------------+------------+------------+--------------+--------------- 1 | 1 | Australia | AU | | | not specified 5 | 2 | Thailand | TH | | | Prayuth Chan 6 | 3 | Singapore | SG | 697 | | not specified (3 rows) ``` ##### regexp_match {#pg10-p75-regexp-match} The regexp_match function to perform pattern matching has been added. Unlike conventional regexp_matches, it returns an array of text type. The citext Contrib module also has a regexp_match function corresponding to the citext type. **Example 82.** regexp\_match function ```text postgres=> \dfS regexp_match List of functions Schema | Name | Result data type | Argument data types | Type ------------+--------------+------------------+---------------------+-------- pg_catalog | regexp_match | text[] | text, text | normal pg_catalog | regexp_match | text[] | text, text, text | normal (2 rows) ```PDF page 76
##### pg_ls_logdir / pg_ls_waldir {#pg10-p76-pg-ls-logdir-pg-ls-waldir} These functions return the name, size, update date and time of the log file list and WAL file list. Execution of these functions requires SUPERUSER privilege. **Example 83.** pg\_ls\_logdir \/ pg\_ls\_waldir functions ```text postgres=# SELECT * FROM pg_ls_logdir() ; name | size | modification ----------------------------------+------+------------------------ postgresql-2017-05-20_092939.log | 5220 | 2017-05-20 21:44:21+09 (1 row) postgres=# SELECT * FROM pg_ls_waldir() ; name | size | modification --------------------------+----------+------------------------ 00000001000000000000002E | 16777216 | 2017-05-19 22:55:33+09 (1 row) ``` ##### txid_status {#pg10-p76-txid-status} The txid_status function has been added to check the status of transactions. By specifying the transaction ID, the status of the corresponding transaction is returned.PDF page 77
**Example 84.** txid\_status function ```text postgres=> BEGIN ; BEGIN postgres=> SELECT txid_current() ; txid_current -------------- 578 (1 row) postgres=> SELECT txid_status(578) ; txid_status ------------- in progress (1 row) postgres=> COMMIT ; COMMIT postgres=> SELECT txid_status(578) ; txid_status ------------- committed (1 row) postgres=> SELECT txid_status(1000) ; ERROR: transaction ID 1000 is in the future ``` ##### JSON / JSONB type {#pg10-p77-json-jsonb-type} The following functions correspond to JSON type and JSONB type. - to_tsvector - ts_headline ##### pg_stop_backup {#pg10-p77-pg-stop-backup} The pg_stop_backup function has added a parameter wait_for_archive, which specifies to wait for WAL's archive. By default (true), it waits for WAL archive as before.PDF page 78
##### pg_import_system_collations {#pg10-p78-pg-import-system-collations} The pg_import_system_collations function imports information into the PostgreSQL instance when a new Collation is installed in the OS. SUPERUSER privilege is required to execute this function. #### Syntax 22 - pg_import_system_collations {#pg10-syntax-22} ```sql pg_import_system_collations(if_not_exists boolean, schema regnamespace) ``` ##### to_date / to_timestamp {#pg10-p78-to-date-to-timestamp} The to_date function and to_timestamp functions are now strictly checked for the input values of each field. For PostgreSQL 10, the value automatically calculated in the conventional version is an error. **Example 85.** to\_date \(PostgreSQL 9\.6\) ```text postgres=> SELECT to_date('2017-04-40', 'YYYY-MM-DD') ; to_date ------------ 2017-05-10 (1 row) ``` **Example 86.** to\_date \(PostgreSQL 10\) ```text postgres=> SELECT to_date('2017-04-40', 'YYYY-MM-DD') ; ERROR: date/time field value out of range: "2017-04-40" ``` ##### make_date {#pg10-p78-make-date} Negative values (BC) can now be specified for parameters specifying years. **Example 87.** make\_date \(PostgreSQL 9\.6\) ```text postgres=> SELECT make_date(-2000, 4, 30) ; ERROR: date field value out of range: -2000-04-30 ```PDF page 79
**Example 88.** make\_date \(PostgreSQL 10\) ```text postgres=> SELECT make_date(-2000, 4, 30) ; make_date --------------- 2000-04-30 BC (1 row) ``` #### 3.9.14 Procedural language {#pg10-3-9-14-procedural-language} This section explains the enhancement of procedural language. ##### PL/Python {#pg10-p79-pl-python} Plan.execute and plan.cursor statements have been added. **Example 89.** execute method \/ cursor method ```bash # plan.execute plan = plpy.prepare("SELECT val FROM data1 WHERE key=$1", [ "NUMERIC" ]) result = plan.execute(key) # plan.cursor plan = plpy.prepare("SELECT val FROM data1 WHERE key=$1", [ "NUMERIC" ]) rows = plan.cursor([2]) ``` ##### PL/Tcl {#pg10-p79-pl-tcl} Transactions with "subtransaction" syntax can now be executed.PDF page 80
**Example 90.** subtransaction syntax ```sql CREATE FUNCTION transfer_funds2() RETURNS void AS $$ if [catch { subtransaction { spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'" spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'" } } errormsg] { set result [format "error transferring funds: %s" $errormsg] } else { set result "funds transferred correctly" } set plan [spi_prepare "INSERT INTO operations (result) VALUES ($1)"] spi_execp $plan, [list $result] $$ LANGUAGE pltclu; ``` ```text Pltcl.start_proc and pltclu.start_proc which are GUC which specifies initialization procedure name have been added. ```