4 SQL Statements

postgres=# SHOW allow_system_table_mods ;
 allow_system_table_mods
-------------------------
 on
(1 row)
postgres=# ALTER TABLE pg_attribute SET
(autovacuum_vacuum_scale_factor=0) ;
ALTER TABLE

postgres=> BEGIN ;
BEGIN
postgres=> ALTER TYPE t1 ADD VALUE 'v3' ;
ALTER TYPE

postgres=> BEGIN ;
BEGIN
postgres=> INSERT INTO data1 VALUES (100, 'data1') ;
INSERT 0 1
postgres=> COMMIT AND CHAIN ;
COMMIT
postgres=> INSERT INTO data1 VALUES (200, 'data2') ;
INSERT 0 1
postgres=> ROLLBACK AND CHAIN ;
ROLLBACK
postgres=> INSERT INTO data1 VALUES (300, 'data3') ;
INSERT 0 1
postgres=> COMMIT ;
COMMIT

In transactions started with the CHAIN clause, attributes such as transaction isolation level are
maintained from the previous transaction.

postgres=> SHOW transaction_isolation ;
 transaction_isolation
-----------------------
 read committed
(1 row)
postgres=> BEGIN ISOLATION LEVEL SERIALIZABLE ;
BEGIN
postgres=> COMMIT AND CHAIN ;
COMMIT
postgres=> SHOW transaction_isolation ;
 transaction_isolation
-----------------------
 serializable
(1 row)
postgres=> COMMIT ;
COMMIT
postgres=> SHOW transaction_isolation ;
 transaction_isolation
-----------------------
 read committed
(1 row)

postgres=# COPY data1 FROM '/home/postgres/data1.csv' CSV
                 DELIMITER ',' WHERE mod(c1, 2) = 0 ;
COPY 50000

postgres=> \copy data1 FROM '/home/postgres/data1.csv' CSV
                  DELIMITER ',' WHERE mod(c1, 2) = 0 ;
COPY 50000

postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION
BY RANGE(c1) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES FROM
(0) TO (100) ;
CREATE TABLE
postgres=> CREATE TABLE part1v2 PARTITION OF part1 FOR VALUES FROM
(100) TO (200) ;
CREATE TABLE
postgres=# COPY part1 FROM '/home/postgres/part1.csv' CSV FREEZE ;
ERROR:  cannot perform FREEZE on a partitioned table

postgres=> CREATE TABLE data1(c1 INT, c2 box, c3 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE INDEX idx1_data1 ON data1 USING gist (c2)
INCLUDE (c1) ;
CREATE INDEX
postgres=> \d data1
                      Table "public.data1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           |          |
 c2     | box                   |           |          |
 c3     | character varying(10) |           |          |
Indexes:
    "idx1_data1" gist (c2) INCLUDE (c1)

postgres=> CREATE TABLE stat1 (c1 NUMERIC, c2 NUMERIC, c3
                 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE STATISTICS mcv_stat1(mcv) ON c1, c2 FROM
                 stat1 ;

postgres=>  CREATE TABLE gen1(c1 VARCHAR(10), c2 VARCHAR(10), c3
VARCHAR(20) GENERATED ALWAYS AS (c1 || c2) STORED) ;
CREATE TABLE
postgres=> \d gen1
                                      Table "public.gen1"
 Column |         Type          | Collation | Nullable |Default
--------+-----------------------+-----------+----------+----------
---------
 c1     | character varying(10) |           |          |
 c2     | character varying(10) |           |          |
 c3     | character varying(20) |           |          | generated
always as (((c1::text || c2::text))) stored

 Values cannot be specified directly for generated columns in INSERT and UPDATE statements. Only
the DEFAULT clause is valid.

postgres=> INSERT INTO gen1 VALUES ('AB', 'CD', 'EF') ;
psql: ERROR:  cannot insert into column "c3"
DETAIL:  Column "c3" is a generated column.
postgres=> INSERT INTO gen1 VALUES ('AB', 'CD', DEFAULT) ;
INSERT 0 1

 The generated column values are calculated when executing an INSERT or UPDATE statement, and
the calculated values are physically stored.

postgres=# SELECT heap_page_items(get_raw_page('gen1', 0)) ;
                            heap_page_items
---------------------------------------------------------------------
 1,8152,1,35,524,0,0,"(0,1)",3,2050,24,,,"\\x0741420743440b41424344")
(1 row)

postgres=> SELECT * FROM information_schema.column_column_usage ;
 table_catalog
|
table_schema
|
table_name
|
column_name
|
dependent_column
---------------+--------------+------------+-------------+-----------
------
 postgres      | public       | gen1       | c1          | c3
 postgres      | public       | gen1       | c2          | c3
(2 rows)
postgres=> SELECT attname, attgenerated FROM pg_attribute WHERE attname
IN ('c1', 'c2', 'c3') ;
 attname | attgenerated
---------+--------------
 c1      |
 c2      |
 c3      | s
(3 rows)

 A generated column cannot be specified as a partitioning key. In addition, it is not possible to
define a generated column that depends on the other generated columns.

postgres=>  CREATE TABLE pgen1(c1 INT, c2 INT, c3 INT GENERATED
ALWAYS AS (c1 + c2) STORED) PARTITION BY RANGE(c3) ;
psql: ERROR:  cannot use generated column in partition key
LINE 1: ...NERATED ALWAYS AS (c1 + c2) STORED) PARTITION BY
RANGE(c3) ;
                                                                         ^
DETAIL:  Column "c3" is a generated column.
postgres=> CREATE TABLE gen2 (c1 INT, c2 INT GENERATED ALWAYS AS
(c1*2) STORED, c3 INT GENERATED ALWAYS AS (c2*2) STORED) ;
psql: ERROR:  cannot use generated column "c2" in column generation
expression
LINE 1: ...AYS AS (c1*2) STORED, c3 INT GENERATED ALWAYS AS (c2*2)
STOR...

postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10))
PARTITION BY
LIST(c1) TABLESPACE ts1 ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES IN
(100) ;
CREATE TABLE
postgres=> \d part1
                Partitioned table "public.part1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | numeric               |           |          |
 c2     | character varying(10) |           |          |
Partition key: LIST (c1)
Number of partitions: 1 (Use \d+ to list them.)
Tablespace: "ts1"
postgres=> \d part1v1
                     Table "public.part1v1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | numeric               |           |          |
 c2     | character varying(10) |           |          |
Partition of: part1 FOR VALUES IN ('100')
Tablespace: "ts1"

postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES IN
(power(2, 3)) ;
CREATE TABLE
postgres=> \d part1v1
                     Table "public.part1v1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | numeric               |           |          |
 c2     | character varying(10) |           |          |
Partition of: part1 FOR VALUES IN ('8')
Tablespace: "ts1"

 postgres=> CREATE TABLE fkey1(c1 INT PRIMARY KEY, c2
                 VARCHAR(10)) PARTITION BY RANGE(c1) ;
CREATE TABLE
postgres=> CREATE TABLE fkey1v1 PARTITION OF fkey1 FOR VALUES
                 FROM (0) TO (1000000) ;
CREATE TABLE
postgres=> CREATE TABLE fkey1v2 PARTITION OF fkey1 FOR VALUES
                 FROM (1000000) TO (2000000) ;
CREATE TABLE
postgres=> CREATE TABLE ref1(c1 INT REFERENCES fkey1(c1),
                 c2 VARCHAR(10)) ;

 In PostgreSQL 11, when trying to create the ref1 table, "ERROR: cannot reference partitioned table"
fkey1 "error occurred.

postgres=> CREATE TABLE vacuum1(c1 INT, c2 VARCHAR(10)) WITH
(VACUUM_INDEX_CLEANUP = OFF) ;
CREATE TABLE
postgres=> \d+ vacuum1
                                          Table "public.vacuum1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats
target | Description
--------+-----------------------+-----------+----------+---------+----------+---
-----------+-
 c1     | integer               |           |          |         | plain    |           |
 c2     | character varying(10) |           |          |         | extended |
|
Access method: heap
Options: vacuum_index_cleanup=off

postgres=> CREATE TABLE vacuum1(c1 INT, c2 VARCHAR(10)) WITH
(VACUUM_TRUNCATE = OFF) ;
CREATE TABLE
postgres=> \d+ vacuum1
                                         Table "public.vacuum1"
 Column |         Type          | Collation | Nullable | Default |
Storage
| Stats target | Description
--------+-----------------------+-----------+----------+---------
+--------------+-----------------------+-----------+----------+--
-------+----+-------------
 c1     | integer               |           |          |         | plain
|           |
 c2     | character varying(10) |           |          |         |
extended
|           |
Access method: heap
Options: vacuum_truncate=off

postgres=> SET random_page_cost = 1.0 ;
SET
postgres=> EXPLAIN (SETTINGS ON) SELECT * FROM data1 WHERE c1=100 ;
                               QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using idx1_data1 on data1  (cost=0.29..2.31 rows=1 width=12)
   Index Cond: (c1 = '100'::numeric)
 Settings: random_page_cost = '1'
(3 rows)

postgres=> REINDEX (VERBOSE) TABLE CONCURRENTLY data1 ;
psql: INFO:  index "public.idx1_data1" was reindexed
psql: INFO:  index "pg_toast.pg_toast_16385_index" was reindexed
psql: INFO:  table "public.data1" was reindexed
DETAIL:  CPU: user: 3.25 s, system: 0.69 s, elapsed: 13.27 s.
REINDEX

 In accordance with the change of REINDEX statement, --concurrently option was also added to the
reindexdb command.

$ reindexdb --dbname postgres --echo --concurrently
SELECT pg_catalog.set_config('search_path', '', false);
REINDEX DATABASE CONCURRENTLY postgres;
WARNING:  cannot reindex system catalogs concurrently, skipping
all
$

postgres=> SET plpgsql.extra_warnings = 'strict_multi_assignment' ;
SET
postgres=> CREATE OR REPLACE FUNCTION strict1()
  RETURNS void
  LANGUAGE plpgsql
AS $$
  DECLARE
     x INTEGER ;
     y INTEGER ;
  BEGIN
     SELECT 1 INTO x, y ;
     SELECT 1, 2, 3 INTO x, y ;
  END ;
$$ ;
CREATE FUNCTION
postgres=> SELECT strict1() ;
psql: WARNING:  number of source and target fields in assignment do
not match
DETAIL:  strict_multi_assignment check of extra_warnings is active.
HINT:  Make sure the query returns the exact list of columns.
psql: WARNING:  number of source and target fields in assignment do
not match
DETAIL:  strict_multi_assignment check of extra_warnings is active.
HINT:  Make sure the query returns the exact list of columns.
 strict1
---------
(1 row)

postgres=> SET plpgsql.extra_errors to 'too_many_rows' ;
SET
postgres=> DO $$
DECLARE x INTEGER ;
BEGIN
    SELECT generate_series(1,2) INTO x ;
    RAISE NOTICE 'test output' ;
END ;
        $$ ;
psql: ERROR:  query returned more than one row
HINT:  Make sure the query returns a single row, or use LIMIT 1
CONTEXT:  PL/pgSQL function inline_code_block line 4 at SQL
statement

postgres=> BEGIN ;
BEGIN
postgres=> LOCK TABLE lock1 IN EXCLUSIVE MODE ;
LOCK TABLE

postgres=> VACUUM (SKIP_LOCKED) lock1 ;
psql: WARNING:  skipping vacuum of "lock1" --- lock not available
VACUUM
postgres=> \echo :SQLSTATE
00000

postgres=> VACUUM (VERBOSE OFF, FULL ON, ANALYZE ON) data1 ;
VACUUM
postgres=> VACUUM (VERBOSE TRUE, FULL TRUE, ANALYZE FALSE) data1 ;
psql: INFO:  vacuuming "public.data1"
psql: INFO:  "data1": found 0 removable, 1000000 nonremovable row
versions in 5406 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.23 s, system: 0.28 s, elapsed: 0.72 s.
VACUUM

postgres=> VACUUM (VERBOSE ON, INDEX_CLEANUP OFF) data1 ;
INFO:  vacuuming "public.data1"
INFO:  "data1": found 10 removable, 1730 nonremovable row versions
in 10 out of 5406 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin:
593
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 5396 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
…
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

postgres=> VACUUM (VERBOSE ON, TRUNCATE OFF) data1 ;
INFO:  vacuuming "public.data1"
INFO:  scanned index "idx1_data1" to remove 10 row versions
DETAIL:  CPU: user: 0.06 s, system: 0.00 s, elapsed: 0.08 s
INFO:  "data1": removed 10 row versions in 10 pages
<<途中省略>>
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

postgres=> EXPLAIN WITH s AS NOT MATERIALIZED (SELECT * FROM
data1) SELECT * FROM s WHERE c1=100 ;
                               QUERY PLAN
-----------------------------------------------------------------
--------
 Index Scan using data1_pkey on data1  (cost=0.42..8.44 rows=1
width=12)
   Index Cond: (c1 = '100'::numeric)
(2 rows)

postgres=> EXPLAIN WITH s AS MATERIALIZED (SELECT * FROM data1)
SELECT * FROM s WHERE c1=100 ;
                               QUERY PLAN
-----------------------------------------------------------------
------
 CTE Scan on s  (cost=15406.00..37906.00 rows=5000 width=70)
   Filter: (c1 = '100'::numeric)
   CTE s
     ->  Seq Scan on data1  (cost=0.00..15406.00 rows=1000000
width=12)
(4 rows)

postgres=>
SELECT
jsonb_path_query_array('{"a":[1,2,3,4,5]}',
'$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}') ;
 jsonb_path_query_array
------------------------
 [2, 3, 4]
(1 row)

 The following functions have been added.

postgres=> SELECT * FROM pg_partition_tree('part1') ;
  relid  | parentrelid | isleaf | level
---------+-------------+--------+-------
 part1   |             | f      |     0
 part1v1 | part1       | t      |     1
 part1v2 | part1       | t      |     1
(3 rows)
postgres=> SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS
                 total FROM pg_partition_tree('part1') ;
 total
-------
 84 MB
(1 row)

postgres=> CREATE TABLE part1(c1 NUMERIC, c2 NUMERIC,
                 c3 VARCHAR(10)) PARTITION BY LIST(c1) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES
                 IN (100) PARTITION BY LIST (c2) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1v2 PARTITION OF part1v1 FOR
                 VALUES IN (200) ;
CREATE TABLE
postgres=> SELECT pg_partition_root('part1v1v2') ;
 pg_partition_root
-------------------
 part1
(1 row)

postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10))
                 PARTITION BY LIST(c1) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES
                IN (100) ;
CREATE TABLE
postgres=> SELECT pg_partition_ancestors('part1v1') ;
 pg_partition_ancestors
------------------------
 part1v1
 part1
(2 rows)

postgres=# SELECT pg_promote(true, 90) ;
 pg_promote
------------
 t
(1 row)

postgres=# SELECT * FROM pg_ls_tmpdir() ;
       name       |   size    |      modification
------------------+-----------+------------------------
 pgsql_tmp36911.6 | 148955136 | 2019-10-04 11:57:36-04
 pgsql_tmp37050.0 | 139722752 | 2019-10-04 11:57:36-04
 pgsql_tmp37051.0 | 138403840 | 2019-10-04 11:57:36-04
(3 rows)

postgres=# SELECT * FROM pg_ls_archive_statusdir() ;
             name              | size |      modification
-------------------------------+------+------------------------
 00000001000000000000000D.done |    0 | 2019-10-04 19:33:00-04
 00000001000000000000000E.done |    0 | 2019-10-04 19:33:01-04
 00000001000000000000000F.done |    0 | 2019-10-04 19:33:02-04
(3 rows)

postgres=> SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE
                 '2019-10-04 20:38:40+00', 'Asia/Tokyo') ;
       date_trunc
------------------------
 2019-10-04 11:00:00-04
(1 row)

postgres=> SELECT log10(20), sinh(1) ;
       log10        |        sinh
--------------------+--------------------
 1.3010299956639813 | 1.1752011936438014
(1 row)

postgres=# SELECT pg_create_physical_replication_slot('slot_1') ;
 pg_create_physical_replication_slot
-------------------------------------
 (slot_1,)
(1 row)
postgres=#
SELECT
pg_copy_physical_replication_slot('slot_1',
'slot_c') ;
ERROR:  cannot copy a replication slot that doesn't reserve WAL