# Enhancement for SQL Statement

> Source chapter 3.4 for PostgreSQL 9.6.

---

LLMS index: [llms.txt](/en/llms.txt)

---

<div class="feature-source-note"><span class="feature-edition-badge">Beta 1</span><span><strong>Source pages:</strong> PDF 28-37</span><span><a href="https://github.com/nori-shinoda/documents/blob/1773200c5362476c4a549222b7d17fa91f546cae/postgresql96_beta1_new_features_en_20160606-1.pdf">Original PDF on GitHub</a></span></div>

<p class="feature-page-marker" id="pgfeat-9-6-sql-pdf-page-28">PDF page 28</p>

The new features on the SQL statement, are explained here.

#### 3.4.1 Enhancement of the COPY statement {#pg9-6-3-4-1-enhancement-of-the-copy-statement}

In the prior version it was able to use the COPY statement to output the results of the SELECT statement to a file or standard output. In PostgreSQL 9.6, by specifying the UPDATE / DELETE / INSERT statements to the COPY statement, it will be able to output the affected records.

**Example 19.** Specify the DELETE statement to COPY statement

```text
postgres=# COPY (DELETE FROM data1 WHERE col1 < 100 RETURNING *) TO
 '/home/postgres/data1.csv' ;
COPY 5
```

```sql
In the above example 5 records that have been deleted by the DELETE statement are written to the
file. When specifying the DELETE, UPDATE, and INSERT statement RETURNING clause is
required .
```

**Example 20.** No RETURNING clause error

```text
postgres=# COPY (DELETE FROM data1 WHERE col1 < 100) TO
'/home/postgres/data1.csv' ;
ERROR:  COPY query must have a RETURNING clause
```

#### 3.4.2 Enhancement of the ALTER TABLE ADD COLUMN statement {#pg9-6-3-4-2-enhancement-of-the-alter-table-add-column-statement}

The ALTER TABLE ADD COLUMN statement to add a column, IF NOT EXISTS clause to check for the existence of the column can now be specified. Only if the specified column does not exist, add column operation will be performed. Syntax

```sql
ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name type
```

<p class="feature-page-marker" id="pgfeat-9-6-sql-pdf-page-29">PDF page 29</p>

**Example 21.** ALTER TABLE ADD COLUMN IF NOT EXISTS statement

```text
postgres=> ALTER TABLE data1 ADD COLUMN c3 CHAR(1) ;
ALTER TABLE
postgres=> ALTER TABLE data1 ADD COLUMN IF NOT EXISTS c3 CHAR(1) ;
NOTICE:  column "c3" of relation "data1" already exists, skipping
ALTER TABLE
postgres=> ALTER TABLE data1 ADD COLUMN IF NOT EXISTS c4 CHAR(1) ;
ALTER TABLE
```

#### 3.4.3 Enhancement of the ALTER TABLESPACE SET statement {#pg9-6-3-4-3-enhancement-of-the-alter-tablespace-set-statement}

In PostgreSQL 9.6 it is now possible to set the parameter effective_io_concurrency for each tablespace. Parameters that can be specified for each tablespace in the old version was only random_page_cost and seq_page_cost.

**Example 22.** Enhancement of the ALTER TABLESPACE statement

```text
postgres=# ALTER TABLESPACE ts1 SET (effective_io_concurrency = 2) ;
ALTER TABLESPACE
postgres=# \db+ ts1
                                              List of tablespaces
 Name | Owner |       Location     | Access privileges |
Options    |  Size  | Description
------+-------+--------------------+-------------------+--------------
----------+--------+-------------
 ts1  | demo  | /home/postgres/ts1 |                   |
{effective_io_concurrency=2} | 472 kB |
(1 row)
```

#### 3.4.4 Enhancement of the CREATE EXTENSION statement {#pg9-6-3-4-4-enhancement-of-the-create-extension-statement}

To CREATE EXTENSION statement, CASCADE clause to automatically load the relevant module can now be specified. Syntax

```sql
CREATE EXTENSION module_name [CASCADE]
```

<p class="feature-page-marker" id="pgfeat-9-6-sql-pdf-page-30">PDF page 30</p>

**Example 23.** CREATE EXTENSION CASCADE statement

```text
postgres=# CREATE EXTENSION earthdistance ;
ERROR:  required extension "cube" is not installed
HINT:  Use CREATE EXTENSION CASCADE to install required extensions too.
postgres=#
postgres=# CREATE EXTENSION earthdistance CASCADE ;
NOTICE:  installing required extension "cube"
CREATE EXTENSION
postgres=#
```

#### 3.4.5 Enhancement of the ALTER OPERATOR statement {#pg9-6-3-4-5-enhancement-of-the-alter-operator-statement}

ALTER OPERATOR statement to make the change of the operator have been significantly enhanced. It can be added use the following syntax as well as the CREATE OPERATOR statement. Syntax

```sql
ALTER OPERATOR name ({type}, {type}) SET RESTRICT res_proc
ALTER OPERATOR name ({type}, {type}) SET JOIN join_proc
ALTER OPERATOR name ({type}, {type}) SET RESTRICT NONE
ALTER OPERATOR name ({type}, {type}) SET JOIN NONE
```

#### 3.4.6 Added function for jsonb type {#pg9-6-3-4-6-added-function-for-jsonb-type}

Function for jsonb type has been added.

##### Function jsonb_insert {#pg9-6-p30-function-jsonb-insert}

Jsonb_insert function to perform additional elements have been provided.

<p class="feature-page-marker" id="pgfeat-9-6-sql-pdf-page-31">PDF page 31</p>

**Example 24.** Function jsonb\_insert

```text
postgres=> SELECT jsonb_insert(
'{"a": [0,1,2]}', '{a, 1}', '"new_value"') ;
         jsonb_insert
-------------------------------
 {"a": [0, "new_value", 1, 2]}
(1 row)
postgres=> SELECT jsonb_insert(
'{"a": [0,1,2]}', '{a, 1}', '"new_value"', true) ;
         jsonb_insert
-------------------------------
 {"a": [0, 1, "new_value", 2]}
(1 row)
```

#### 3.4.7 Additional Functions {#pg9-6-3-4-7-additional-functions}

Following function have been enhanced in PostgreSQL 9.6.

##### scale function {#pg9-6-p31-scale-function}

By specifying a number of numeric type as the parameter, this function returns the number of decimal places. It cannot be used for a float type value. If NULL is passed, it returns NULL.

##### num_nulls / num_nonnulls functions {#pg9-6-p31-num-nulls-num-nonnulls-functions}

Returns the number of NULL values from any number of arguments (num_nulls), returns the number of non-NULL value (num_nonnulls).

**Example 25.** num\_nulls \/ num\_nonnulls functions

```text
postgres=> SELECT num_nulls(1, 'A', NULL), num_nonnulls(1, 'A', NULL) ;
num_nulls | num_nonnulls
-----------+--------------
         1 |            2
(1 row)
```

<p class="feature-page-marker" id="pgfeat-9-6-sql-pdf-page-32">PDF page 32</p>

##### current_setting function {#pg9-6-p32-current-setting-function}

The overloaded function that has second parameter has been created. When specified true for the second parameter, no error occurs if specified a non-existent parameter.

**Example 26.** current\_setting function

```text
postgres=> \pset null null
Null display is "null".
postgres=> SELECT current_setting('module1.param1') ;
ERROR:  unrecognized configuration parameter "module1.param1"
postgres=>
postgres=> SELECT current_setting('module1.param1', true) ;
 current_setting
-----------------
 null
(1 row)
```

##### pg_control_* functions {#pg9-6-p32-pg-control-functions}

It is now possible to get the information by SQL function, conventionally that has been acquired by the pg_controldata command by SQL function. The following functions can be used. General users can use these functions.

**Table 21.** Added functions

| Function Name | Description |
| --- | --- |
| pg_control_init | Acquiring of database cluster information |
| pg_control_checkpoint | Acquiring of checkpoint information |
| pg_control_recovery | Acquiring of recovery information |

<p class="feature-page-marker" id="pgfeat-9-6-sql-pdf-page-33">PDF page 33</p>

**Example 27.** Execute pg\_control\_init function

```text
postgres=> \x
Expanded display is on.
postgres=> SELECT * FROM pg_control_init() ;
-[ RECORD 1 ]--------------+---------
max_data_alignment         | 8
database_block_size        | 8192
blocks_per_segment         | 131072
wal_block_size             | 8192
bytes_per_wal_segment      | 16777216
max_identifier_length      | 64
max_index_columns          | 32
max_toast_chunk_size       | 1996
large_object_chunk_size    | 2048
bigint_timestamps          | t
float4_pass_by_value       | t
float8_pass_by_value       | t
data_page_checksum_version | 0
```

<p class="feature-page-marker" id="pgfeat-9-6-sql-pdf-page-34">PDF page 34</p>

**Example 28.** Execute pg\_control\_checkpoint function

```text
postgres=> \x
Expanded display is on.
postgres=> SELECT * FROM pg_control_checkpoint() ;
-[ RECORD 1 ]--------+-------------------------
checkpoint_location  | 0/E52BD28
prior_location       | 0/E42B3C8
redo_location        | 0/E52BD28
redo_wal_file        | 00000001000000000000000E
timeline_id          | 1
prev_timeline_id     | 1
full_page_writes     | t
next_xid             | 0:1767
next_oid             | 24576
next_multixact_id    | 1
next_multi_offset    | 0
oldest_xid           | 1748
oldest_xid_dbid      | 1
oldest_active_xid    | 0
oldest_multi_xid     | 1
oldest_multi_dbid    | 1
oldest_commit_ts_xid | 0
newest_commit_ts_xid | 0
checkpoint_time      | 2016-05-18 15:24:31+09
```

**Example 29.** Execute pg\_control\_recovery function

```text
postgres=> \x
Expanded display is on.
postgres=> SELECT * FROM pg_control_recovery() ;
-[ RECORD 1 ]-----------------+----
min_recovery_end_location     | 0/0
min_recovery_end_timeline     | 0
backup_start_location         | 0/0
backup_end_location           | 0/0
end_of_backup_record_required | f
```

<p class="feature-page-marker" id="pgfeat-9-6-sql-pdf-page-35">PDF page 35</p>

##### pg_current_xlog_flush_location function {#pg9-6-p35-pg-current-xlog-flush-location-function}

Function pg_current_xlog_flush_location that returns the LSN indicating the writing location of the WAL file has been added. General users can execute this function, but cannot execute in slave instance of the replication environment.

**Example 30.** pg\_current\_xlog\_flush\_location function

```text
postgres=> SELECT pg_current_xlog_flush_location() ;
 pg_current_xlog_flush_location
--------------------------------
 0/3000060
(1 row)
```

##### parse_ident function {#pg9-6-p35-parse-ident-function}

This function decompose a string indicating the name of the object that contains the schema name into an array consists of schema and object names. The presence of the specified object is not checked. Also search_path parameter is not considered.

**Example 31.** parse\_ident function

```text
postgres=> SELECT parse_ident('public.data1') ;
  parse_ident
----------------
 {public,data1}
(1 row)
```

##### pg_size_bytes function {#pg9-6-p35-pg-size-bytes-function}

Pg_size_bytes function returns the number of bytes from the string specified in units of kB, MB, GB and TB. This is the opposite behavior of pg_size_pretty function. It can be put a space between the number and the unit.

<p class="feature-page-marker" id="pgfeat-9-6-sql-pdf-page-36">PDF page 36</p>

**Example 32.** pg\_size\_bytes function

```text
postgres=> SELECT pg_size_bytes ('1.2 TB') ;
pg_size_bytes
---------------
 1319413953331
(1 row)
```

##### pg_blocking_pids function {#pg9-6-p36-pg-blocking-pids-function}

Returns a list of processes that are blocking the process specified by process ID.

**Example 33.** pg\_blocking\_pids function

```text
postgres=> SELECT pg_blocking_pids(2953) ;
 pg_blocking_pids
------------------
 {2950}
(1 row)
```

```sql
Extend pg_start_backup / pg_stop_backup functions
□
The pg_start_backup function and the pg_stop_backup function, parameters for the
exclusive control "exclusive" (boolean) has been added. The default value is false, the
behavior is the same as previous versions. It does not create the backup_label files and
tablespace_map file if you specify the "exclusive" parameter to false. Pg_stop_backup
function you must specify the same mode as the pg_start_backup function.
```

<p class="feature-page-marker" id="pgfeat-9-6-sql-pdf-page-37">PDF page 37</p>

**Example 34.** pg\_start\_backup \/ pg\_stop\_backup functions

```text
postgres=# SELECT pg_start_backup('back1', true, false) ;
 pg_start_backup
-----------------
 0/4C000028
(1 row)
postgres=# -- Do online backup
postgres=# SELECT pg_stop_backup(true) ;
ERROR:  non-exclusive backup in progress
HINT:  did you mean to use pg_stop_backup('f')?
postgres=#
postgres=# SELECT pg_stop_backup(false) ;
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
                               pg_stop_backup
-----------------------------------------------------------------------------
 (0/4C0000F8,"START WAL LOCATION: 0/4C000028 (file 00000001000000000000004C)+
 CHECKPOINT LOCATION: 0/4C000060                                            +
 BACKUP METHOD: streamed                                                    +
 BACKUP FROM: master                                                        +
 START TIME: 2016-06-01 09:51:03 JST                                        +
 LABEL: back1                                                               +
 ","")
(1 row)
```

##### Unverified functions {#pg9-6-p37-unverified-functions}

The following functions have been added, but the behavior is not verified.

##### pg_notification_queue_usage {#pg9-6-p37-pg-notification-queue-usage}

##### acosd / asind / atan2d / atand / cosd / cotd / sind / tand {#pg9-6-p37-acosd-asind-atan2d-atand-cosd-cotd-sind-tand}