postgres=> CREATE TABLE oid1(c1 INT) WITH OIDS ;
psql: ERROR:  syntax error at or near "OIDS"
LINE 1: CREATE TABLE oid1(c1 INT) WITH OIDS ;

postgres=> \d pg_tablespace
            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default
------------+-----------+-----------+----------+---------
 oid        | oid       |           | not null |
 spcname    | name      |           | not null |
 spcowner   | oid       |           | not null |
 spcacl     | aclitem[] |           |          |
 spcoptions | text[]    |           |          |
Indexes:
    "pg_tablespace_oid_index"
UNIQUE,
btree
(oid),
tablespace
"pg_global"
    "pg_tablespace_spcname_index"
UNIQUE,
btree
(spcname),
tablespace "pg_global"
Tablespace: "pg_global"

postgres=> CREATE TABLE ftable2(c1 INT, c2 INT, c3 VARCHAR(10),
FOREIGN KEY (c1, c2) REFERENCES ftable1(c1, c2)) ;
CREATE TABLE
postgres=> \d ftable2
                     Table "public.ftable2"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           |          |
 c2     | integer               |           |          |
 c3     | character varying(10) |           |          |
Foreign-key constraints:
    "ftable2_c1_c2_fkey" FOREIGN KEY (c1, c2) REFERENCES ftable1(c1,
c2)

postgres=> SELECT TO_DATE('2019/10/03', ' YYYY/MM/DD') ;
  to_date
------------
 0019-10-03
(1 row)

postgres=> SELECT TO_DATE('2019/10/03', ' YYYY/MM/DD') ;
  to_date
------------
 2019-10-03
(1 row)

postgres=> SELECT SUBSTRING('foobar' from '%#"oo*#"%' FOR '#') ;
 substring
-----------
 o
(1 row)

postgres=> SELECT SUBSTRING('foobar' from '%#"oo*#"%' FOR '#') ;
 substring
-----------
 oo
(1 row)

postgres=> SELECT * FROM pg_stat_progress_create_index ;
-[ RECORD 1 ]------+---------------------------------------
pid                | 13233
datid              | 16385
datname            | postgres
relid              | 16386
index_relid       | 0
phase              | building index: loading tuples in tree
lockers_total      | 0
lockers_done       | 0
current_locker_pid | 0
blocks_total       | 0
blocks_done        | 0
tuples_total       | 10000000
tuples_done        | 6207353
partitions_total   | 0
partitions_done    | 0

postgres=# SELECT * FROM pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 12497
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2019-10-03 20:13:15.551032-04
backend_xmin     |
state            | streaming
sent_lsn         | 0/3000060
write_lsn        | 0/3000060
flush_lsn        | 0/3000060
replay_lsn       | 0/3000060
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2019-10-03 20:15:45.68363-04

postgres=# SELECT * FROM data1 ;
psql: WARNING:  page verification failed, calculated checksum 27311
but expected 12320
ERROR:  invalid page in block 0 of relation base/13567/16384
postgres=#
postgres=# SELECT datname, checksum_failures , checksum_last_failure
FROM pg_stat_database WHERE datname='postgres' ;
-[ RECORD 1 ]-----+------------------------------
datname           | postgres
checksum_failures | 1
checksum_last_failure | 2019-10-03 20:06:23.747741-04

In PostgreSQL 12, a tuple whose datid column is 0 has been added. This is database-independent
global object information.

$ ls data/recovery.conf
data/recovery.conf
$ pg_ctl -D data start
waiting for server to start....2019-10-03 21:55:53.749 EDT [18397]
LOG:  starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2019-10-03 21:55:53.750 EDT [18397] LOG:  listening on IPv6 address
"::1", port 5432
2019-10-03 21:55:53.750 EDT [18397] LOG:  listening on IPv4 address
"127.0.0.1", port 5432
2019-10-03 21:55:53.755 EDT [18397] LOG:  listening on Unix socket
"/tmp/.s.PGSQL.5432"
2019-10-03 21:55:53.771 EDT [18398] LOG:  database system was shut
down at 2019-10-03 21:55:09 EDT
2019-10-03 21:55:53.771 EDT [18398] FATAL:  using recovery command
file "recovery.conf" is not supported
2019-10-03 21:55:53.772 EDT [18397] LOG:  startup process (PID
18398) exited with exit code 1
2019-10-03 21:55:53.772 EDT [18397] LOG:  aborting startup due to
startup process failure
2019-10-03 21:55:53.773 EDT [18397] LOG:  database system is shut
down
 stopped waiting
pg_ctl: could not start server
Examine the log output.
$

$ pg_ctl -D data start
waiting for server to start....2019-10-03 21:36:22.445 EDT [17730]
LOG:  starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2019-10-03 21:36:22.447 EDT [17730] LOG:  listening on IPv6 address
"::1", port 5432
2019-10-03 21:36:22.447 EDT [17730] LOG:  listening on IPv4 address
"127.0.0.1", …

$ pg_controldata -D data | grep max_wal_senders
max_wal_senders setting:              10

postgres=# LOAD 'auto_explain' ;
LOAD
postgres=# SET auto_explain.log_min_duration = 0 ;
SET
postgres=# BEGIN ISOLATION LEVEL SERIALIZABLE ;
BEGIN
postgres=# SELECT COUNT(*) FROM data1 ;
  count
---------
 1000000
(1 row)
postgres=# COMMIT ;
COMMIT

2019-10-03 21:52:48.785 JST [1789] LOG:  duration: 45.472 ms  plan:
        Query Text: SELECT COUNT(*) FROM data1 ;
        Aggregate  (cost=17906.00..17906.01 rows=1 width=8)
          ->  Seq Scan on data1  (cost=0.00..15406.00 rows=1000000
width=0)

2019-10-03 21:39:09.319 EDT [17764] LOG:  duration: 94.678 ms  plan:
        Query Text: SELECT COUNT(*) FROM data1;
        Finalize Aggregate  (cost=10633.55..10633.56 rows=1 width=8)
          ->  Gather  (cost=10633.33..10633.54 rows=2 width=8)
                Workers Planned: 2
                ->  Partial Aggregate  (cost=9633.33..9633.34 rows=1
width=8)
                      ->  Parallel Seq Scan on data1  (cost=0.00..8591.67
rows=416667 width=0)

postgres=> CREATE INDEX idxgin ON api USING GIN (jdoc) ;
CREATE INDEX
postgres=> EXPLAIN (COSTS OFF) SELECT jdoc->'guid', jdoc->'name' FROM
api WHERE jdoc @@ '$.tags[*] == "qui"' ;
                            QUERY PLAN
------------------------------------------------------------------
 Bitmap Heap Scan on api
   Recheck Cond: (jdoc @@ '($."tags"[*] == "qui")'::jsonpath)
   ->  Bitmap Index Scan on idxgin
         Index Cond: (jdoc @@ '($."tags"[*] == "qui")'::jsonpath)
(4 rows)
postgres=> EXPLAIN (COSTS OFF) SELECT jdoc->'guid', jdoc->'name' FROM
api WHERE jdoc @@ '$.tags[*] ? (@ == "qui")' ;
                             QUERY PLAN
--------------------------------------------------------------------
 Bitmap Heap Scan on api
   Recheck Cond: (jdoc @@ '$."tags"[*]?(@ == "qui")'::jsonpath)
   ->  Bitmap Index Scan on idxgin
         Index Cond: (jdoc @@ '$."tags"[*]?(@ == "qui")'::jsonpath)
(4 rows)

{
    EXEC SQL BEGIN DECLARE SECTION ;
        bytea data[1024] ;
        short data_ind = 0 ;
    EXEC SQL END DECLARE SECTION ;
    memset(data.arr, 0, sizeof(data.arr)) ;
    data.len = 0 ;
    EXEC SQL SELECT col1 INTO :data FROM data1 ;
    for (i = 0; i < data.len; i++)
    {
        printf("data[%d] = %c\n", i, data.arr[i]) ;
    }
}

{
    EXEC SQL BEGIN DECLARE SECTION ;
        int ivar1 = 100;
    int ivar2 = 200;
    EXEC SQL END DECLARE SECTION ;
  EXEC SQL PREPARE pre_name(INT, INT) AS INSERT INTO prepare1
VALUES ($1, $2);
    EXEC SQL EXECUTE pre_name(:ivar1, :ivar2);
}

postgres=# SELECT amname, amhandler, amtype FROM pg_am ;
 amname |      amhandler       | amtype
--------+----------------------+--------
 heap   | heap_tableam_handler | t
 btree  | bthandler            | i
 hash   | hashhandler          | i
 gist   | gisthandler          | i
 gin    | ginhandler           | i
 spgist | spghandler           | i
 brin   | brinhandler          | i
(7 rows)

postgres=> CREATE TABLE data1(c1 NUMERIC, c2 VARCHAR(10)) USING
heap ;
CREATE TABLE
postgres=> CREATE TABLE data2 USING heap AS SELECT * FROM data1 ;
SELECT 100000
postgres=> CREATE MATERIALIZED VIEW mview1 USING heap AS
                 SELECT COUNT(*) cnt FROM data1 ;
SELECT 1

postgres=> \d+ data1
                                          Table "public.data1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats
target | Description
--------+-----------------------+-----------+----------+---------+----------+---
-----------+-
 c1     | numeric               |           |          |         | main     |           |
 c2     | character varying(10) |           |          |         | extended |
|
Access method: heap

 Specifying on for the psql variable HIDE_TABLEAM (default value: 'off') can suppress the output
of the Access Method item.

postgres=> \set HIDE_TABLEAM on
postgres=> \d+ data1
                                          Table "public.data1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats
target | Description
--------+-----------------------+-----------+----------+---------+----------+--------
------+----------
 c1     | numeric               |           |          |         | main     |           |
 c2     | character varying(10) |           |          |         | extended |           |

postgres=> \dA
List of access methods
  Name  | Type
--------+-------
 brin   | Index
 btree  | Index
 gin    | Index
 gist   | Index
 hash   | Index
 heap   | Table
 spgist | Index
(7 rows)

postgres=# \dF
               List of text search configurations
   Schema   |    Name    |              Description
------------+------------+---------------------------------------
 pg_catalog | arabic     | configuration for arabic language
<< new
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | indonesian | configuration for indonesian language << new
 pg_catalog | irish      | configuration for irish language      << new
 pg_catalog | italian    | configuration for italian language
 pg_catalog | lithuanian | configuration for lithuanian language << new
 pg_catalog | nepali     | configuration for nepali language
 pg_catalog | norwegian  | configuration for norwegian language  << new
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | tamil      | configuration for tamil language     << new
 pg_catalog | turkish    | configuration for turkish language
(22 rows)

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

postgres=# SHOW ssl_library ;
 ssl_library
-------------
 OpenSSL
(1 row)

postgres=> PREPARE sel1(VARCHAR) AS SELECT * FROM plan1 WHERE c2=$1 ;
PREPARE
postgres=> EXPLAIN EXECUTE sel1('plan0') ;
                               QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using idx1_plan1 on plan1  (cost=0.42..4.44 rows=1 width=12)
   Index Cond: ((c2)::text = 'plan0'::text)
(2 rows)
postgres=> EXPLAIN EXECUTE sel1('plan1') ;
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on plan1  (cost=0.00..17906.01 rows=1000001 width=12)
   Filter: ((c2)::text = 'plan1'::text)
(2 rows)

 If the same SQL statement is executed 5 times or more, the execution plan may be cached, and the
cached execution plan (general execution plan) may be used even if the parameter is changed from
the next time. In the example below, the display in the execution plan changes from a literal value to
$1 at the execution of the sixth EXPLAIN statement.

postgres=> EXPLAIN ANALYZE EXECUTE sel1('plan1') ; -- Repeat 5 times
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on plan1  (cost=0.00..23311.01 rows=1000001 width=12)
   Filter: ((c2)::text = 'plan1'::text)
(2 rows)
postgres=> EXPLAIN ANALYZE EXECUTE sel1('plan1') ; -- Sixth (execution
plan changed)
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on plan1  (cost=0.00..23311.01 rows=1000001 width=12)
   Filter: ((c2)::text = ($1)::text)
(2 rows)

postgres=> SET plan_cache_mode = force_generic_plan ;
SET
postgres=> PREPARE sel1(VARCHAR) AS SELECT * FROM plan1
                 WHERE c2=$1 ;
PREPARE
postgres=> EXPLAIN EXECUTE sel1('plan0') ;
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on plan1  (cost=0.00..23312.50 rows=1000120 width=12)
   Filter: ((c2)::text = ($1)::text)
(2 rows)

postgres=> SHOW default_with_oids ;
default_with_oids
-------------------
 off
(1 row)
postgres=> SET default_with_oids = on ;
psql: ERROR:  tables declared WITH OIDS are not supported
postgres=> SELECT COUNT(*) FROM pg_settings WHERE
                 name='default_with_oids' ;
 count
-------
     0
(1 row)

$ grep primary_conninfo data/postgresql.conf
primary_conninfo = 'host=svrhost1 port=5432 user=postgres
password=password options=''-c wal_sender_timeout=5000'''

LOG:  -1 switching to external sort with 16 tapes: CPU: user: 0.00
s, system: 0.00 s, elapsed: 0.00 s
LOG:  -1 using 3951 KB of memory for read buffers among 15 input
tapes
LOG:  performsort of -1 done (except 15-way final merge): CPU:
user: 0.15 s, system: 0.01 s, elapsed: 0.16 s

LOG:  performsort of worker -1 starting: CPU: user: 0.00 s,
system: 0.00 s, elapsed: 0.00 s
LOG:  internal sort of worker -1 ended, 25 KB used: CPU: user:
0.00 s, system: 0.00 s, elapsed: 0.00 s

postgres=# ALTER SYSTEM SET wal_level=minimal ;
ALTER SYSTEM
postgres=# \q
$
$ pg_ctl -D data restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-10-04 00:52:19.890 EDT [22682]
FATAL:  WAL archival cannot be enabled when wal_level is "minimal"
 stopped waiting
pg_ctl: could not start server
Examine the log output.
$

$ pg_basebackup -D back -R
$ ls back
backup_label      log           pg_ident.conf  pg_replslot
pg_stat_tmp  PG_VERSION            postgresql.conf
base              pg_commit_ts  pg_logical     pg_serial
pg_subtrans  pg_wal                standby.signal
current_logfiles  pg_dynshmem   pg_multixact   pg_snapshots
pg_tblspc    pg_xact
global            pg_hba.conf   pg_notify      pg_stat
pg_twophase  postgresql.auto.conf
$ cat back/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo
=
'user=postgres
passfile=''/home/postgres/.pgpass''
port=5432
sslmode=disable
sslcompression=0 gssencmode=disable target_session_attrs=any'
primary_slot_name = 'slot1'
$

$ pg_checksums --help
pg_checksums enables, disables, or verifies data checksums in a
PostgreSQL database cluster.
Usage:
  pg_checksums [OPTION]... [DATADIR]
Options:
 [-D, --pgdata=]DATADIR    data directory
  -c, --check              check data checksums (default)
  -d, --disable            disable data checksums
  -e, --enable             enable data checksums
  -f, --filenode=FILENODE  check only relation with specified
filenode
  -N, --no-sync            do not wait for changes to be written
safely to disk
  -P, --progress           show progress information
  -v, --verbose            output verbose messages
  -V, --version            output version information, then exit
  -?, --help               show this help, then exit
If no data directory (DATADIR) is specified, the environment
variable PGDATA
is used.
Report bugs to <pgsql-bugs@lists.postgresql.org>.
$

 Specify the --check the option to check the integrity of the block. If the consistency error is confirmed,
the command ends with a return value of 1.

$ pg_checksums -D data --check
Checksum operation completed
Files scanned:  973
Blocks scanned: 11187
Bad checksums:  0
Data checksum version: 1
$ echo $?
0
$

 Specify the --enable option (--disable the option to disable) to enable database cluster checksums.
Even if the checksum is enabled, a consistency check is performed.

$ pg_checksums -D data --enable
Checksum operation completed
Files scanned:  973
Blocks scanned: 11187
pg_checksums: syncing data directory
pg_checksums: updating control file
Checksums enabled in cluster
$

 This command must be run with the instance successfully shut down. If an instance terminates
abnormally or cannot be executed for a database cluster during the instance is up.

$ pg_ctl -D data -m immediate stop
waiting for server to shut down.... done server stopped
$ pg_checksums -D data --check
pg_checksums: cluster must be shut down
$

$ pg_checksums -D data --check --progress
87/87 MB (100%) computed
Checksum operation completed
Files scanned:  973
Blocks scanned: 11187
Bad checksums:  0
Data checksum version: 1

$ pg_ctl -D data logrotate
server signaled to rotate log file
$

$ pg_dump -t data1 --inserts --on-conflict-do-nothing
--
-- PostgreSQL database dump
…
-- Data for Name: data1; Type: TABLE DATA; Schema: public; Owner: demo
--
INSERT INTO public.data1 VALUES (1, 'data1') ON CONFLICT DO NOTHING;
INSERT INTO public.data1 VALUES (2, 'data1') ON CONFLICT DO NOTHING;
…

$ pg_dump -t data1 --inserts --rows-per-insert=2
--
-- PostgreSQL database dump
…
-- Data for Name: data1; Type: TABLE DATA; Schema: public; Owner: postgres
--
INSERT INTO public.data1 VALUES
        (1, 'data1'),
        (2, 'data1');
INSERT INTO public.data1 VALUES
        (3, 'data1'),
        (4, 'data1');
…

$ pg_dumpall --exclude-database='demodb[12]' -f alldump.sql

-- User Configurations
-- User Config {User_name}
-- Databases
-- Database {Database_name} dump

$ pg_rewind --help
pg_rewind resynchronizes a PostgreSQL cluster with another copy of the
cluster.
Usage:
  pg_rewind [OPTION]...
Options:
  -D, --target-pgdata=DIRECTORY  existing data directory to modify
      --source-pgdata=DIRECTORY  source data directory to synchronize with
      --source-server=CONNSTR    source server to synchronize with
  -n, --dry-run                  stop before modifying anything
  -N, --no-sync                  do not wait for changes to be written
                                 safely to disk
  -P, --progress                 write progress messages
      --debug                    write a lot of debug messages
…

$ pg_upgrade --help
pg_upgrade upgrades a PostgreSQL cluster to a different major version.
Usage:
  pg_upgrade [OPTION]...
Options:
  -b, --old-bindir=BINDIR       old cluster executable directory
…
  -r, --retain                  retain SQL and log files after success
  -s, --socketdir=DIR           socket directory to use (default CWD)
  -U, --username=NAME           cluster superuser (default "postgres")
  -v, --verbose                 enable verbose internal logging
  -V, --version                 display version information, then exit
  --clone                       clone instead of copying files to new
cluster
  -?, --help                    show this help, then exit
…

postgres=> \pset format csv
Output format is csv.
postgres=> \pset csv_fieldsep
Field separator for CSV is ",".
postgres=> SELECT * FROM data1 ;
c1,c2
<- Title
1,ABC
2,"AB,C"
<- If the data contains column separators.
2,"AB""C"
<- When the data contains double quotations.

postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION
                 BY LIST(c1) ;
CERATE 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: 0

 Tablespace of partitioned indexes is now displayed.

postgres=> CREATE INDEX idx1_part1 ON part1(c2) TABLESPACE ts1 ;
CREATE INDEX
postgres=> \d idx1_part1
       Partitioned index "public.idx1_part1"
 Column |         Type          | Key? | Definition
--------+-----------------------+------+------------
 c2     | character varying(10) | yes  | c2
btree, for table "public.part1"
Tablespace: "ts1"

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 (10) ;
CREATE TABLE
postgres=> \z
                                    Access privileges
 Schema |  Name   |       Type        | Access privileges | Column
privileges | Policies
--------+---------+-------------------+-------------------+-------------
------+----------
 public | part1   | partitioned table |                   |                   |
 public | part1v1 | table             |                   |                   |
(2 rows)

$ psql -p 5432 -h pghost1 -d postgres -U demo
Password for user demo: <<PASSWORD>>
psql (12.0)
Type "help" for help.
postgres=> \conninfo
You are connected to database "postgres" as user "demo" on host
"pghost1" (address "192.168.1.101") at port "5432".
postgres=>

postgres=> \set VERBOSITY sqlstate
postgres=> SELECT * FROM not_exists ;
psql: ERROR:  42P01

ostgres=> CREATE TABLE part1(c1 INT, c2 VARCHAR(2)) PARTITION BY RANGE(c1) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES FROM (0) TO (1000000) ;
CREATE TABLE
postgres=> \dP
             List of partitioned relations
 Schema | Name  | Owner |       Type        | On table
--------+-------+-------+-------------------+----------
 public | part1 | demo  | partitioned table |
(1 row)
postgres=> \dP+
                          List of partitioned relations
 Schema | Name  | Owner |       Type        | On table | Total size | Description
--------+-------+-------+-------------------+----------+------------+-------------
 public | part1 | demo  | partitioned table |          | 0 bytes    |
(1 row)

postgres=> \h CREATE SEQUENCE
Command:     CREATE SEQUENCE
Description: define a new sequence generator
Syntax:
CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]
URL: https://www.postgresql.org/docs/12/sql-createsequence.html

$ vacuumdb -h remhost11 -d postgres -U postgres --skip-locked
vacuumdb: error: cannot use the "skip-locked" option on server
versions older than PostgreSQL 12

postgres=# LOAD 'auto_explain' ;
LOAD
postgres=# SET auto_explain.log_level = NOTICE ;
SET
postgres=# SET auto_explain.log_min_duration = 0 ;
SET
postgres=# SELECT * FROM data1 WHERE c1=1000 ;
NOTICE:  duration: 146.234 ms  plan:
Query Text: SELECT * FROM data1 WHERE c1=1000 ;
Index Scan using data1_pkey on data1  (cost=0.42..8.44 rows=1
width=12)
  Index Cond: (c1 = '1000'::numeric)
  c1  |  c2
------+-------
 1000 | data1
(1 row)

2019-10-05 11:10:02.465 JST [28847] LOG:  duration: 62.630 ms  plan:
        Query Text: SELECT COUNT(*) FROM data1 ;
        Finalize Aggregate  (cost=11604.55..11604.56 rows=1 width=8)
          ->  Gather  (cost=11604.33..11604.54 rows=2 width=8)
                Workers Planned: 2
                ->  Partial Aggregate  (cost=10604.33..10604.34 rows=1 width=8)
                      ->  Parallel Seq Scan on data1  (cost=0.00..9562.67
rows=416667 width=0)
        Settings: random_page_cost = '1'

2019-10-04 22:25:11.027 JST [17749] LOG:  duration: 47.828 ms  plan:
        Query Text: SELECT COUNT(*) FROM data1 ;
        Aggregate  (cost=17906.00..17906.01 rows=1 width=8)
          ->  Seq Scan on data1  (cost=0.00..15406.00 rows=1000000 width=0)
        JIT:
          Functions: 2
          Options: Inlining false, Optimization false, Expressions true,
Deforming true

postgres=> INSERT INTO hash1 VALUES ('abc') ;
INSERT 0 1
postgres=> SELECT citext_hash_extended(c1, 0) FROM hash1 ;
 citext_hash_extended
----------------------
 -6747756470228489321
(1 row)
postgres=> SELECT citext_hash_extended(c1, 1) FROM hash1 ;
 citext_hash_extended
----------------------
  2125675926272891485
(1 row)

postgres=> INSERT INTO hstore1 VALUES (hstore('key1', 'val1')) ;
INSERT 0 1
postgres=> SELECT hstore_hash_extended(c1, 0) FROM hstore1 ;
 hstore_hash_extended
----------------------
  4863461342754690677
(1 row)
postgres=> SELECT hstore_hash_extended(c1, 1) FROM hstore1 ;
 hstore_hash_extended
----------------------
  1673650576531873159
(1 row)

postgres=> \dfS pg_stat_statements_reset
List of functions
-[ RECORD 1 ]-------+--------------------------------------------
-------------
Schema              | public
Name                | pg_stat_statements_reset
Result data type    | void
Argument data types | userid oid DEFAULT 0, dbid oid DEFAULT 0,
queryid bigint DEFAULT 0
Type                | func

 Also, this function cannot be executed by users who hold the pg_read_all_stats role.

postgres=# CREATE SERVER remsvr1 FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remhost1', options '-c work_mem=4MB') ;
CREATE SERVER
postgres=# ALTER SERVER remsvr1 OPTIONS
(SET options '-c work_mem=16MB -c geqo=off') ;
ALTER SERVER
postgres=# SELECT * FROM pg_foreign_server ;
-[ RECORD 1 ]-------------------------------------------------------
srvname    | remsvr1
srvowner   | 10
srvfdw     | 16412
srvtype    |
srvversion |
srvacl     |
srvoptions | {host=remhost1, "options=-c work_mem=16MB -c geqo=off"}