6 Enhancement for SQL Statement

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

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 .

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

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

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)

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=#

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)

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

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)

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

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

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

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

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

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

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

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.

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)