postgres=> SELECT regexp_match(E'A\nC', '\D\D\D', 'n') ;
 regexp_match
--------------
 null
(1 row)

postgres=> SELECT regexp_match(E'A\nC', '\D\D\D', 'n') ;
 regexp_match
--------------
 {"A         +
 C"}
(1 row)

postgres=> SELECT factorial(-4) ;
factorial
-----------
         1
(1 row)

postgres=> SELECT factorial(-4) ;
ERROR:  factorial of a negative number is undefined

postgres=> SELECT 4! ;
RROR:  syntax error at or near ";"
LINE 1: SELECT 4! ;
                  ^

postgres=# CREATE STATISTICS stat1_collation ON collname,
collowner FROM pg_collation ;
ERROR:  permission denied: "pg_collation" is a system catalog

postgres=# CREATE LANGUAGE 'plperl' ;
ERROR:  syntax error at or near "'plperl'"
LINE 1: CREATE LANGUAGE 'plperl';

postgres=# CREATE EXTENSION tablefunc ;
CREATE EXTENSION
postgres=# SELECT normal_rand(-1, 1.2, 1.3) ;
ERROR:  number of rows cannot be negative

postgres=# SELECT name, parent, level, total_bytes FROM
 pg_backend_memory_contexts ;
           name           |       parent       | level | total_bytes
--------------------------+--------------------+-------+------------
TopMemoryContext         |                    |     0 |       68704
 TopTransactionContext    | TopMemoryContext   |     1 |        8192
 Record information cache | TopMemoryContext   |     1 |        8192
 TableSpace cache         | TopMemoryContext   |     1 |        8192
 Type information cache   | TopMemoryContext   |     1 |       24376
 Operator lookup cache    | TopMemoryContext   |     1 |       24576
 RowDescriptionContext    | TopMemoryContext   |     1 |        8192
 MessageContext           | TopMemoryContext   |     1 |       32768
 Operator class cache     | TopMemoryContext   |     1 |        8192
…

postgres=> SELECT * FROM pg_stat_progress_copy ;
-[ RECORD 1 ]---+----------
pid              | 83409
datid            | 13887
datname          | postgres
relid            | 16385
command          | COPY FROM
type             | FILE
bytes_processed  | 22806528
bytes_total      | 138888897
tuples_processed | 1703999
tuples_excluded  | 0

postgres=> SELECT * FROM pg_stat_replication_slots ;
-[ RECORD 1 ]+----------
slot_name    | sub1
spill_txns   | 3
spill_count  | 9
spill_bytes  | 416939406
stream_txns  | 0
stream_count | 0
stream_bytes | 0
total_txns   | 0
total_bytes  | 0
stats_reset  |

postgres=> SELECT * FROM pg_stat_wal ;
-[ RECORD 1 ]----+------------------------------
wal_records      | 52417581
wal_fpi          | 98
wal_bytes        | 4139801371
wal_buffers_full | 341903
wal_write        | 342723
wal_sync         | 876
wal_write_time   | 0
wal_sync_time    | 0
stats_reset      | 2021-10-01 10:21:41.173493+09

postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=remhost1
dbname=postgres user=postgres password=<<PASSWORD>>'
PUBLICATION pub1 WITH (streaming = on) ;
CREATE SUBSCRIPTION
postgres=# SELECT subname, substream  FROM pg_subscription
WHERE subname = 'sub1' ;
subname | substream
---------+-----------
 sub1    | t
(1 row)

postgres=# CREATE SUBSCRIPTION sub2 CONNECTION 'host=remhost1
dbname=postgres user=postgres password=<<PASSWORD>>'
PUBLICATION pub2 WITH (binary = on) ;
CREATE SUBSCRIPTION
postgres=# SELECT subname, subbinary  FROM pg_subscription
WHERE subname = 'sub2' ;
subname | subbinary
---------+-----------
 sub2    | t
(1 row)

LOG:  duration: 6649.485 ms  plan:
        Query Text: REFRESH MATERIALIZED VIEW mview1;
        Gather  (cost=0.00..95721.67 rows=10000000 width=12)
          Workers Planned: 2
          ->  Parallel Seq Scan on data1  (cost=0.00..95721.67
rows=4166667 width=12)

postgres=> CREATE TABLE compress1(c1 INT, c2 TEXT COMPRESSION lz4) ;
CREATE TABLE
postgres=> \d+ compress1
                                        Table "public.compress1"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | …
--------+---------+-----------+----------+---------+----------+-------------+ …
 c1     | integer |           |          |         | plain    |             | …
 c2     | text    |           |          |         | extended | lz4         | …
Access method: heap
postgres=> ALTER TABLE compress1 ALTER COLUMN c2 SET COMPRESSION pglz ;
ALTER TABLE
postgres=> \d+ compress1
                                        Table "public.compress1"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | …
--------+---------+-----------+----------+---------+----------+-------------+ …
 c1     | integer |           |          |         | plain    |             | …
 c2     | text    |           |          |         | extended | pglz        | …
Access method: heap

In the CREATE TABLE (LIKE) statement, specify the "LIKE table name INCLUDING
COMPRESSION" clause to copy the table definition up to the compressed definition. Compression
attribute of each column can be found in the attcompression column of pg_attribute catalog. 'p' is pglz
compression and 'l' is LZ4 compression. To check the compression method of the column, execute the
pg_column_compression function with the column name.

postgres=> SELECT c1, pg_column_compression(c2) FROM compress1 ;
 c1  | pg_column_compression
-----+-----------------------
 100 | pglz
 200 | lz4
(2 rows)

postgres=# CREATE SERVER remsvr1 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remhost1', port '5432', dbname 'postgres',
async_capable 'on') ;
CREATE SERVER

It is displayed as Async Foreign Scan in the execution plan. In the example below, the partitions
part1v1 and part1v2 contained in the partition table part1 are implemented as FOREIGN TABLE
respectively.

postgres=> EXPLAIN SELECT * FROM part1 ;
                                      QUERY PLAN
---------------------------------------------------------------------------
 Append  (cost=100.00..306.87 rows=2482 width=42)
   ->  Async Foreign Scan on part1v1 part1_1  (cost=100.00..147.23 rows=1241
width=42)
   ->  Async Foreign Scan on part1v2 part1_2  (cost=100.00..147.23 rows=1241
width=42)
(3 rows)

postgres=> EXPLAIN SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1 INNER JOIN
tenk1 t2 ON t1.unique1 = t2.twenty WHERE t2.unique1 < 1000 ;
                                             QUERY PLAN
---------------------------------------------------------------------------
Aggregate  (cost=450.10..450.11 rows=1 width=40)
   ->  Nested Loop  (cost=24.33..445.10 rows=1000 width=4)
         ->  Bitmap Heap Scan on tenk1 t2  (cost=24.04..381.54 …
               Recheck Cond: (unique1 < 1000)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..23.79 …
                     Index Cond: (unique1 < 1000)
         ->  Memoize  (cost=0.30..1.85 rows=1 width=4)
               Cache Key: t2.twenty
               ->  Index Only Scan using tenk1_unique1 on tenk1 t1  (cost=…
                     Index Cond: (unique1 = t2.twenty)
(10 rows)

postgres=> CREATE STATISTICS extstat1_data1 ON MOD(c1, 10),
MOD(c2, 10) FROM data1 ;
CREATE STATISTICS
postgres=> ANALYZE data1 ;
ANALYZE
postgres=> SELECT * FROM pg_stats_ext_exprs ;
-[ RECORD 1 ]----------+-----------------------------------------
schemaname             | public
tablename              | data1
statistics_schemaname  | public
statistics_name        | extstat1_data1
statistics_owner       | demo
expr                   | mod(c1, 10)
null_frac              | 0
avg_width              | 4
…

postgres=> CREATE TABLE numeric1(c1 NUMERIC, c2 float) ;
CREATE TABLE
postgres=> INSERT INTO numeric1 VALUES ('Infinity', '-Infinity') ;
INSERT 0 1
postgres=> SELECT * FROM numeric1 WHERE c1='Infinity' ;
    c1    |    c2
----------+-----------
 Infinity | -Infinity
(1 row)
postgres=> SELECT 0.1 / '-Infinity', exp('-Infinity'),
power(2, '-Infinity') ;
 ?column? | exp | power
----------+-----+-------
        0 |   0 |     0
(1 row)

postgres=> SELECT '{[1, 5), (10, 20]}'::int8multirange ;
int8multirange
-----------------
 {[1,5),[11,21)}
(1 row)
postgres=> SELECT nummultirange( numrange(1, 10, '()'),
numrange(15, 20, '[]')) ;
  nummultirange
------------------
 {(1,10),[15,20]}
(1 row)

postgres=> SELECT ('[1, "2", null]'::jsonb)[1] ;
 jsonb
-------
 "2"
(1 row)
postgres=> SELECT ('{"age": 25}'::jsonb)['age'] ;
 jsonb
-------
 25
(1 row)
postgres=> SELECT ('{"email":"pgsql-hackers@postgresql.org",
"phone":"+3012345678"}'::jsonb) ['phone'] ;
     jsonb
----------------
 "+3012345678"
(1 row)

postgres=> SELECT pg_current_wal_lsn(), pg_current_wal_lsn()
+ 256 ;
 pg_current_wal_lsn | ?column?
--------------------+-----------
 0/15D1F88          | 0/15D2088
(1 row)

postgres=> CREATE INDEX idx1_data1 ON data1 USING brin
(c1 numeric_bloom_ops (false_positive_rate = 0.05,
 n_distinct_per_range = 100)) ;
CREATE INDEX

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    postgres       postgres       192.168.1.219/32        \
                                                                md5

EXEC SQL BEGIN DECLARE SECTION;
    char *selectStr = "SELECT c1 FROM data1";
    long f1;
EXEC SQL END DECLARE SECTION;
int main()
{
    EXEC SQL CONNECT TO postgres USER postgres;
    EXEC SQL DECLARE stmt1 STATEMENT;
    EXEC SQL PREPARE stmt1 FROM :selectStr;
    EXEC SQL DECLARE cur1 CURSOR FOR stmt1;
    EXEC SQL OPEN cur1;
    …
}

LOG:  automatic vacuum of table "postgres.public.data1": index
scans: 1
        pages: 0 removed, 5406 remain, 0 skipped due to pins, 0
skipped frozen
        tuples: 500000 removed, 500000 remain, 0 are dead but not
yet removable, oldest xmin: 566
        buffer usage: 21775 hits, 0 misses, 1 dirtied
        index scan needed: 5406 pages from table (100.00% of total)
had 500000 dead item identifiers removed
        index "idx1_data1": pages: 2745 in total, 0 newly deleted,
0 currently deleted, 0 reusable
        index "idx2_data1": pages: 2745 in total, 0 newly deleted,
0 currently deleted, 0 reusable
        avg read rate: 20.330 MB/s, avg write rate: 20.239 MB/s
…

LOG:  connection received: host=192.168.1.219 port=51524
LOG:
connection
authenticated:
identity="demo"
method=md5
(/usr/local/pgsql/data/pg_hba.conf:91)
LOG:
connection
authorized:
user=demo
database=postgres
application_name=psql

$ ps -ef|grep receiver | grep -v grep
postgres  93255  93249  0 14:34 ?        00:00:01 postgres:
walreceiver streaming 0/5D744F8
$

$ ps -ef|grep sender | grep -v grep
postgres  92995  92944  0 14:28 ?        00:00:00 postgres:
walsender postgres [local] START_REPLICATION
$

postgres=> SELECT 'abc def' ~ '[[:word:]] [[:word:]]' ;
 ?column?
----------
 t
(1 row)

$ cd src/test/modules/plsample
$ ls -l
total 24
drwxrwxr-x. 2 postgres postgres   26 Sep 28 06:00 expected
-rw-r--r--. 1 postgres postgres  440 Sep 28 05:57 Makefile
-rw-r--r--. 1 postgres postgres  469 Sep 28 05:57 plsample--1.0.sql
-rw-r--r--. 1 postgres postgres 5046 Sep 28 05:57 plsample.c
-rw-r--r--. 1 postgres postgres  177 Sep 28 05:57 plsample.control
-rw-r--r--. 1 postgres postgres  228 Sep 28 05:57 README
drwxrwxr-x. 2 postgres postgres   26 Sep 28 06:00 sql

postgres=# ALTER DATABASE demodb OWNER TO CURRENT_ROLE ;
ALTER DATABASE

postgres=# BEGIN ;
BEGIN
postgres=*# TRUNCATE TABLE visible1 ;
TRUNCATE TABLE
postgres=*# COPY visible1 FROM '/home/postgres/visible1.csv'
CSV FREEZE ;
COPY 3
postgres=*# COMMIT ;
COMMIT
postgres=# SELECT * FROM pg_visibility_map('visible1', 0) ;
 all_visible | all_frozen
-------------+------------
 t           | t
(1 row)

postgres=> CREATE INDEX idx1_gist1 ON gist1 USING spgist (c1)
INCLUDE (c2) ;
CREATE INDEX
postgres=> \d+ idx1_gist1
                  Index "public.idx1_gist1"
 Column | Type | Key? | Definition | Storage  | Stats target
--------+------+------+------------+----------+--------------
 c1     | text | yes  | c1         | extended |
 c2     | text | no   | c2         | extended |
spgist, for table "public.gist1"

postgres=> CREATE PROCEDURE sum_n_product(x int, y int, OUT sum
 int, OUT prod int) AS $$
BEGIN
     sum := x + y ;
     prod := x * y ;
 END $$ LANGUAGE plpgsql ;
CREATE PROCEDURE
postgres=> SELECT proargmodes FROM pg_proc WHERE
proname='sum_n_product' ;
 proargmodes
-------------
 {i,i,o,o}
(1 row)
postgres=> CALL sum_n_product(2, 4, NULL, NULL) ;
 sum | prod
-----+------
   6 |    8
(1 row)

postgres=> CREATE PROCEDURE insert_data1(id INTEGER, val TEXT)
   LANGUAGE SQL
   BEGIN ATOMIC
      INSERT INTO data1 VALUES (id, val) ;
      INSERT INTO data1 VALUES (id, val) ;
   END ;
CREATE PROCEDURE

postgres=> CREATE PROCEDURE insert_data1(id NUMERIC, val TEXT)
LANGUAGE SQL
BEGIN ATOMIC
 INSERT INTO data1 VALUES (id, val);
END ;
CREATE PROCEDURE
postgres=> DROP TABLE data1 ;
ERROR:  cannot drop table data1 because other objects depend on it
DETAIL:  function insert_data1(numeric,text) depends on table
data1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
postgres=> DROP TABLE data1 CASCADE ;
NOTICE:  drop cascades to function insert_data1(numeric,text)
DROP TABLE

postgres=>  CREATE TABLE part_coll (c1 TEXT) PARTITION BY RANGE
(c1 COLLATE "POSIX") ;
CREATE TABLE
postgres=> CREATE TABLE part_coll_p1 PARTITION OF part_coll FOR
VALUES FROM ('a' collate "C") to ('g') ;
CREATE TABLE

postgres=> CREATE OR REPLACE TRIGGER data1_trig1 AFTER INSERT
ON data1 FOR EACH ROW EXECUTE FUNCTION data1_ins_func1 () ;
CREATE TRIGGER

postgres=> GRANT SELECT ON data1 TO user1 GRANTED BY CURRENT_USER ;
GRANT
postgres=> GRANT SELECT ON data1 TO user1 GRANTED BY postgres ;
ERROR:  grantor must be current user

postgres=> INSERT INTO data1 AS d1 VALUES (10, 'data1') ON CONFLICT
(c1) WHERE d1.c2 = 'data1' DO NOTHING ;
INSERT 0 1

postgres=> CREATE TABLE gen1(c1 INT PRIMARY KEY, c2 INT
GENERATED ALWAYS AS (c1+1) STORED) ;
CREATE TABLE
postgres=> INSERT INTO gen1 VALUES (100, DEFAULT),(200, DEFAULT) ;
INSERT 0 2
postgres=> SELECT * FROM gen1 ;
 c1  | c2
-----+-----
 100 | 101
 200 | 201
(2 rows)

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 (100000) ;
CREATE TABLE
postgres=> CREATE TABLE part1v2 PARTITION OF part1 FOR VALUES
FROM (100000) TO (200000) ;
CREATE TABLE
postgres=> CREATE INDEX idx1_part1 ON part1(c1) ;
CREATE INDEX
postgres=> REINDEX TABLE part1 ;
REINDEX

postgres=> REINDEX (TABLESPACE ts1, VERBOSE) INDEX idx1_data1 ;
INFO:  index "idx1_data1" was reindexed
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
REINDEX
postgres=> REINDEX (TABLESPACE ts1, VERBOSE) TABLE data1 ;
INFO:  index "data1_pkey" was reindexed
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  index "idx1_data1" was reindexed
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  index "pg_toast_16385_index" was reindexed
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
REINDEX

postgres=> VACUUM (PROCESS_TOAST FALSE, VERBOSE) data1 ;
INFO:  vacuuming "public.data1"
INFO:  "data1": found 0 removable, 37 nonremovable row versions in
1 out of 5406 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin:
518
There were 38 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

LOG:  automatic vacuum of table "postgres.public.data1": index
scans: 0
        pages: 0 removed, 5406 remain, 0 skipped due to pins, 0
skipped frozen
        tuples: 15000 removed, 985000 remain, 0 are dead but not yet
removable, oldest xmin: 880
        index scan bypassed: 82 pages from table (1.52% of total)
have 15000 dead item identifiers
        avg read rate: 0.095 MB/s, avg write rate: 0.191 MB/s
        buffer usage: 10793 hits, 2 misses, 4 dirtied
        WAL usage: 5407 records, 1 full page images, 356929 bytes
…

postgres=> SELECT loc analyze FROM dept ;
 analyze
---------
(0 rows)

postgres=> SELECT a, b, c FROM (VALUES (1, 2, 3),
(4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
GROUP BY ALL ROLLUP(a, b), ROLLUP(a, c) ORDER BY a, b, c ;
a | b | c
---+---+---
 1 | 2 | 3
 1 | 2 |
…
   |   |
(25 rows)
postgres=> SELECT a, b, c FROM (VALUES (1, 2, 3),
(4, NULL, 6), (7, 8, 9)) AS t (a, b, c) GROUP BY
DISTINCT ROLLUP(a, b), ROLLUP(a, c) ORDER BY a, b, c ;
 a | b | c
---+---+---
 1 | 2 | 3
 1 | 2 |
…
   |   |
(13 rows)

postgres=> CREATE TABLE graph0(f INT, t INT, label TEXT) ;
CREATE TABLE
postgres=> INSERT INTO graph0 VALUES (1, 2, 'arc 1 -> 2'),
       (1, 3, 'arc 1 -> 3'), (2, 3, 'arc 2 -> 3'),
       (1, 4, 'arc 1 -> 4'), (4, 5, 'arc 4 -> 5') ;
INSERT 0 5
postgres=> WITH RECURSIVE search_graph(f, t, label) AS (
SELECT * FROM graph0 g
UNION ALL
SELECT g.* FROM graph0 g, search_graph sg WHERE
g.f = sg.t
) SEARCH DEPTH FIRST BY f, t SET seq
SELECT * FROM search_graph ORDER BY seq ;
 f | t |   label    |        seq
---+---+------------+-------------------
 1 | 2 | arc 1 -> 2 | {"(1,2)"}
 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"}
 1 | 3 | arc 1 -> 3 | {"(1,3)"}
 1 | 4 | arc 1 -> 4 | {"(1,4)"}
 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"}
 2 | 3 | arc 2 -> 3 | {"(2,3)"}
 4 | 5 | arc 4 -> 5 | {"(4,5)"}
(7 rows)

postgres=> WITH RECURSIVE search_graph(f, t, label) AS (
SELECT * FROM graph0 g
UNION ALL
SELECT g.* FROM graph0 g, search_graph sg WHERE g.f = sg.t
) SEARCH BREADTH FIRST BY f, t SET seq
SELECT * FROM search_graph ORDER BY seq ;
 f | t |   label    |   seq
---+---+------------+---------
 1 | 2 | arc 1 -> 2 | (0,1,2)
 1 | 3 | arc 1 -> 3 | (0,1,3)
 1 | 4 | arc 1 -> 4 | (0,1,4)
 2 | 3 | arc 2 -> 3 | (0,2,3)
 4 | 5 | arc 4 -> 5 | (0,4,5)
 2 | 3 | arc 2 -> 3 | (1,2,3)
 4 | 5 | arc 4 -> 5 | (1,4,5)
(7 rows)

postgres=> CREATE TABLE graph1(f INT, t INT, label TEXT) ;
CREATE TABLE
postgres=> INSERT INTO graph1 VALUES
 (1, 2, 'arc 1 -> 2'), (1, 3, 'arc 1 -> 3'),
(2, 3, 'arc 2 -> 3'),
 (1, 4, 'arc 1 -> 4'), (4, 5, 'arc 4 -> 5'),
(5, 1, 'arc 5 -> 1') ;
INSERT 0 6
postgres=> WITH RECURSIVE search_graph(f, t, label) AS (
SELECT * FROM graph1 g
UNION ALL
SELECT g.* FROM graph1 g, search_graph sg
WHERE g.f = sg.t
) CYCLE f, t SET is_cycle TO TRUE DEFAULT FALSE USING PATH
SELECT * FROM search_graph ;
 f | t |   label    | is_cycle |                   path
---+---+------------+----------+---------------------------------
 1 | 2 | arc 1 -> 2 | f        | {"(1,2)"}
 1 | 3 | arc 1 -> 3 | f        | {"(1,3)"}
 2 | 3 | arc 2 -> 3 | f        | {"(2,3)"}
 1 | 4 | arc 1 -> 4 | f        | {"(1,4)"}
…
 5 | 1 | arc 5 -> 1 | t        | {"(5,1)","(1,4)","(4,5)","(5,1)"}
 2
|
3
|
arc
2
->
3
|
f
|
{"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"}
(25 rows)

postgres=> SELECT d1.c1, x.c1, x.c2 FROM data1 d1 JOIN data2 d2
USING (c1, c2) AS x ;

postgres=> CREATE FOREIGN TABLE remote1(c1 NUMERIC, c2
VARCHAR(10)) SERVER remsvr1 ;
CREATE FOREIGN TABLE
postgres=> TRUNCATE TABLE remote1 ;
TRUNCATE TABLE

postgres=> SELECT bit_count(B'0101011001'::bit(10)) ;
 bit_count
-----------
         5
(1 row)

postgres=> SELECT SUM(c1), BIT_XOR(c1) FROM data1 ;
    sum     | bit_xor
------------+---------
 5000050000 |  100000
(1 row)

postgres=>  SELECT date_bin('15 minutes', TIMESTAMP'2020-02-11
15:44:17', TIMESTAMP'2001-01-01 00:02:30') ;
      date_bin
---------------------
 2020-02-11 15:32:30
(1 row)

postgres=> SELECT make_timestamp(-2, 12, 25, 10, 20, 30) ;
     make_timestamp
------------------------
 0002-12-25 10:20:30 BC
(1 row)

postgres=> SELECT split_part('www.postgresql.org', '.', -1) ;
split_part
------------
 org
(1 row)

postgres=> SELECT substring('Thomas' SIMILAR '%#"o_a#"_' ESCAPE '#') ;
substring
-----------
 oma
(1 row)

postgres=> SELECT rtrim('abc'::bytea, 'c'::bytea) ;
 rtrim
--------
 \x6162
(1 row)
postgres=> \df rtrim
                         List of functions
   Schema   | Name  | Result data type | Argument data types | Type
------------+-------+------------------+---------------------+---
 pg_catalog | rtrim | bytea            | bytea, bytea        | func
 pg_catalog | rtrim | text             | text                | func
 pg_catalog | rtrim | text             | text, text          | func
(3 rows)

postgres=> SELECT trim_array(ARRAY[1, 2, 3, 4, 5], 2) ;
trim_array
------------
 {1,2,3}
(1 row)

postgres=> SELECT unistr('\0441\+00043B\u043E\043D') ;
 unistr
--------
 слон
(1 row)

postgres=> SELECT string_to_table('ABC-+-DEF-+-GHI', '-+-',
 'abc') ;
 string_to_table
-----------------
 ABC
 DEF
 GHI
(3 rows)

postgres => SELECT jsonb_path_query('"2017-03-10T12:34:56+3:10"',
'$.datetime()') ;
      jsonb_path_query
-----------------------------
 "2017-03-10T12:34:56+03:10"
(1 row)

postgres=# SELECT pg_wal_replay_pause() ;
 pg_wal_replay_pause
---------------------
(1 row)
postgres=# SELECT pg_get_wal_replay_pause_state() ;
 pg_get_wal_replay_pause_state
-------------------------------
 paused
(1 row)

postgres=# SELECT pg_log_backend_memory_contexts(pg_backend_pid()) ;
LOG:  logging memory contexts of PID 67300
STATEMENT:  SELECT pg_log_backend_memory_contexts(pg_backend_pid());
LOG:  level: 0; TopMemoryContext: 68704 total in 5 blocks; 14416 free (10
chunks); 54288 used
…

postgres=# SHOW track_commit_timestamp ;
 track_commit_timestamp
------------------------
 on
(1 row)
postgres=# SELECT * FROM
pg_xact_commit_timestamp_origin(txid_current()::text::xid) ;
 timestamp | roident
-----------+---------
           |
(1 row)

postgres=# SELECT * FROM pg_last_committed_xact() ;
 xid |           timestamp           | roident
-----+-------------------------------+---------
 515 | 2021-10-01 11:08:58.549866+09 |       0
(1 row)

postgres=# \dfS pg_create_logical_replication_slot
List of functions
-[ RECORD 1 ]-------+-------------------------------------------------------
Schema              | pg_catalog
Name                | pg_create_logical_replication_slot
Result data type    | record
Argument data types | slot_name name, plugin name, temporary boolean DEFAULT
false, twophase boolean DEFAULT false, OUT slot_name name, OUT lsn pg_lsn
Type                | func

postgres=# SET compute_query_id = on ;
SET
postgres=# EXPLAIN VERBOSE SELECT * FROM data1 ;
                              QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on public.data1  (cost=0.00..15406.00 rows=1000000
width=12)
   Output: c1, c2
 Query Identifier: 3365166609774651210
(3 rows)

postgres=> \set VERBOSITY verbose
postgres=> SET idle_session_timeout = 1000 ;
SET
postgres=> -- Wait one second.
postgres=> SELECT 1 ;
FATAL:  57P05: terminating connection due to idle-session timeout
LOCATION:  ProcessInterrupts, postgres.c:3356
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=>

-- When conflict occurs
LOG:  recovery still waiting after 1031.776 ms: recovery conflict
on snapshot
DETAIL:  Conflicting process: 53647.
 [49948] CONTEXT:  WAL redo at 1/8ECE0138 for Heap2/PRUNE:
latestRemovedXid 895 nredirected 0 ndead 167; blkref #0: rel
1663/14892/17264, blk 108
-- When conflict is resolved
LOG:  recovery finished waiting after 26073.257 ms: recovery
conflict on snapshot
CONTEXT:  WAL redo at 1/8ECE0138 for Heap2/PRUNE: latestRemovedXid
895 nredirected 0 ndead 167; blkref #0: rel 1663/14892/17264, blk
108

$ ./configure --help | grep ssl
  --with-ssl=LIB          use LIB for SSL/TLS support (openssl)
  --with-openssl          obsolete spelling of --with-ssl=openssl
$

$ ./configure --help | grep -i lz4
  --with-lz4              build with LZ4 support
  LZ4_CFLAGS  C compiler flags for LZ4, overriding pkg-config
  LZ4_LIBS    linker flags for LZ4, overriding pkg-config
$

$ initdb -D data --no-instructions
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.
…
initdb: warning: enabling "trust" authentication for local
connections
You can change this by editing pg_hba.conf or using the option -
A, or
--auth-local and --auth-host, the next time you run initdb.

$ initdb --help
initdb initializes a PostgreSQL database cluster.
Usage:
  initdb [OPTION]... [DATADIR]
Options:
  -A, --auth=METHOD         default authentication method for local connections
      --auth-host=METHOD    default authentication method for local TCP/IP
connections
      --auth-local=METHOD   default authentication method for local-socket
connections
 [-D, --pgdata=]DATADIR     location for this database cluster
  -E, --encoding=ENCODING   set default encoding for new databases
  -g, --allow-group-access  allow group read/execute on data directory
  -k, --data-checksums      use data page checksums
      --locale=LOCALE       set default locale for new databases

$ pg_amcheck --database=postgres --table=data2 --verbose
pg_amcheck: including database: "postgres"
pg_amcheck: in database "postgres": using amcheck version "1.3" in
schema "public"
pg_amcheck: checking heap table "postgres"."public"."data2"
heap table "postgres"."public"."data2", block 0, offset 17:
    xmin 3236212 equals or exceeds next valid transaction ID 0:546
heap table "postgres"."public"."data2", block 0, offset 48:
    line pointer to page offset 6282 is not maximally aligned
pg_amcheck:
checking
btree
index
"postgres"."pg_toast"."pg_toast_16399_index"
pg_amcheck:
checking
heap
table
"postgres"."pg_toast"."pg_toast_16399"

Many options can be specified for the pg_amcheck command other than the above example.

$ pg_dump --extension=cube
--
-- PostgreSQL database dump
…
--
-- Name: cube; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS cube WITH SCHEMA public;
…

postgres=> \di+
                                        List of relations
 Schema |    Name    | Type  | Owner | Table | Persistence | Access Method | …
--------+------------+-------+-------+-------+-------------+-------------+ …
 public | idx1_data1 | index | demo  | data1 | permanent   | btree         | …
 public | idx1_data2 | index | demo  | data2 | permanent   | btree         | …
(2 rows)
postgres=> \dt+
                                           List of relations
 Schema |  Name   |       Type        | Owner | Persistence | Access Method | …
--------+---------+-------------------+-------+-------------+-----------+ …-
 public | data1   | table             | demo  | permanent   | heap          | …
 public | data2   | table             | demo  | permanent   | heap          | …
(2 rows)

postgres=> \dt pg_toast.pg_toast_16384
                 List of relations
  Schema  |      Name      |    Type     |  Owner
----------+----------------+-------------+----------
 pg_toast | pg_toast_16384 | TOAST table | postgres
(1 row)
postgres=> \di pg_toast.pg_toast_1213_index
                         List of relations
  Schema  |        Name         | Type  |  Owner   |     Table
----------+---------------------+-------+----------+-------------
 pg_toast | pg_toast_1213_index | index | postgres | pg_toast_1213
(1 row)

postgres=> CREATE STATISTICS stat1_data1 ON c1, c2 FROM data1 ;
CREATE STATISTICS
postgres=> ANALYZE data1 ;
ANALYZE
postgres=> \dX
                          List of extended statistics
 Schema |    Name     |    Definition     | Ndistinct | Dependencies |   MCV
--------+-------------+-------------------+-----------+--------------+------
 public | stat1_data1 | c1, c2 FROM data1 | defined   | defined      | defined
(1 row)

postgres=> \d+ compress1
                                       Table "public.compress1"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | …
--------+---------+-----------+----------+---------+----------+------------+ …
 c1     | integer |           |          |         | plain    |             | …
 c2     | text    |           |          |         | extended | lz4         | …
Access method: heap
postgres=> \set HIDE_TOAST_COMPRESSION on
postgres=> \d+ compress1
                                 Table "public.compress1"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | …
--------+---------+-----------+----------+---------+----------+--------------+ …
 c1     | integer |           |          |         | plain    |              | …
 c2     | text    |           |          |         | extended |              | …
Access method: heap

postgres=> \do - pg_catalog.int4
                               List of operators
   Schema   | Name | Left arg type | Right arg type | Result type | Description
------------+------+---------------+----------------+-------------+-----------
 pg_catalog | -    |               | integer        | integer     | negate
(1 row)
postgres=> \df sum (numeric)
                         List of functions
   Schema   | Name | Result data type | Argument data types | Type
------------+------+------------------+---------------------+------
 pg_catalog | sum  | numeric          | numeric             | agg
(1 row)

postgres=> \dT int
                           List of data types
   Schema   |  Name   |                   Description
------------+---------+-------------------------------------------------
 pg_catalog | integer | -2 billion to 2 billion integer, 4-byte storage
(1 row)

$ reindexdb --table=data1 --tablespace=ts1 --verbose postgres
INFO:  index "idx1_data1" was reindexed
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  index "pg_toast_16385_index" was reindexed
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

$ vacuumdb --help
vacuumdb cleans and analyzes a PostgreSQL database.
Usage:
  vacuumdb [OPTION]... [DBNAME]
Options:
  -a, --all                       vacuum all databases
  -d, --dbname=DBNAME             database to vacuum
      --disable-page-skipping     disable all page-skipping behavior
  -e, --echo                      show the commands being sent to the server
  -f, --full                      do full vacuuming
  -F, --freeze                    freeze row transaction information
      --force-index-cleanup       always remove index entries that point to
dead tuples
  -j, --jobs=NUM                  use this many concurrent connections to vacuum
      --min-mxid-age=MXID_AGE     minimum multixact ID age of tables to vacuum
      --min-xid-age=XID_AGE       minimum transaction ID age of tables to
vacuum
      --no-index-cleanup          don't remove index entries that point to dead
tuples
      --no-process-toast          skip the TOAST table associated with the
table to vacuum
      --no-truncate               don't truncate empty pages at the end of the
table
  -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum,
if available
…

postgres=# CREATE EXTENSION amcheck ;
CREATE EXTENSION
postgres=# SELECT verify_heapam('data1') ;
 verify_heapam
---------------
(0 rows)

postgres=> CREATE EXTENSION cube ;
CREATE EXTENSION
postgres=> SELECT typname, typreceive, typsend FROM pg_type
WHERE typname='cube' ;
 typname | typreceive |  typsend
---------+------------+-----------
 cube    | cube_recv  | cube_send
(1 row)

postgres=> CREATE EXTENSION hstore ;
CREATE EXTENSION
postgres=> CREATE TABLE hstore1 (id INT, attr hstore) ;
CREATE TABLE
postgres=>  INSERT INTO hstore1 VALUES (1, '"attr1"=>"val1",
"attr2"=>"val2", "attr3"=>"val3"') ;
INSERT 0 1
postgres=> SELECT id, attr['attr1'], attr->'attr2' FROM hstore1 ;
 id | attr | ?column?
----+------+----------
  1 | val1 | val2
(1 row)

postgres=# SHOW old_snapshot_threshold ;
 old_snapshot_threshold
------------------------
 90min
(1 row)
postgres=# CREATE EXTENSION old_snapshot ;
CREATE EXTENSION
postgres=# SELECT * FROM pg_old_snapshot_time_mapping() ;
 array_offset |     end_timestamp      | newest_xmin
--------------+------------------------+-------------
            0 | 2021-10-01 10:13:00+09 |         533
            1 | 2021-10-01 10:14:00+09 |         534
            2 | 2021-10-01 10:15:00+09 |         535
            3 | 2021-10-01 10:16:00+09 |         535
(4 rows)

postgres=# SELECT * FROM
 gist_page_opaque_info(get_raw_page('idx1_gist1', 1)) ;
    lsn    |    nsn    | rightlink | flags
-----------+-----------+-----------+--------
 0/2216510 | 0/2216510 |       473 | {leaf}
(1 row)
postgres=# SELECT * FROM
 gist_page_items(get_raw_page('idx1_gist1', 1), 'idx1_gist1') ;
 itemoffset |    ctid     | itemlen |     keys
------------+-------------+---------+--------------
          1 | (457,65535) |     144 | (c2)=('' '')
          2 | (249,65535) |     144 | (c2)=('' '')
          3 | (487,65535) |     144 | (c2)=('' '')
…

postgres=# SELECT * FROM bt_metap('idx1_data1') ;
-[ RECORD 1 ]-------------+-------
magic                     | 340322
version                   | 4
root                      | 290
level                     | 2
fastroot                  | 290
fastlevel                 | 2
last_cleanup_num_delpages | 0
last_cleanup_num_tuples   | -1
allequalimage             | t

postgres=# CREATE USER demo PASSWORD 'password123' ;
ERROR:  password is easily cracked
postgres=# \! tail -3 data/log/postgresql-2020-10-01_151319.log
ERROR:  password is easily cracked
DETAIL:  cracklib diagnostic: it is based on a dictionary word
STATEMENT:  CREATE USER demo PASSWORD 'password123';

postgres=#  SELECT pgstattuple_approx((SELECT reltoastrelid FROM
pg_class WHERE relname='data1')) ;
  pgstattuple_approx
-----------------------
 (0,0,0,0,0,0,0,0,0,0)
(1 row)

postgres=# REFRESH MATERIALIZED VIEW mview1 ;
REFRESH MATERIALIZED VIEW
postgres=#
postgres=# SELECT query, rows FROM pg_stat_statements WHERE
query LIKE 'REFRESH%';
              query               |  rows
----------------------------------+---------
 REFRESH MATERIALIZED VIEW mview1 | 1000000
(1 row)

postgres=# \d pg_stat_statements_info
       View "public.pg_stat_statements_info"
 Column      |  Type                    | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
 dealloc     | bigint                   |           |          |
 stats_reset | timestamp with time zone |           |          |

postgres=> CREATE INDEX trgm_idx ON test_trgm
USING gist (t gist_trgm_ops) ;
CREATE INDEX
postgres=> EXPLAIN ANALYZE SELECT * FROM test_trgm
WHERE t='100000' ;
                              QUERY PLAN
-----------------------------------------------------------------
 Index Scan using trgm_idx on test_trgm  (cost=0.28..8.30 rows=1
width=5) (actual time=0.424..0.732 rows=1 loops=1)
   Index Cond: (t = '100000'::text)
   Rows Removed by Index Recheck: 2
 Planning Time: 0.038 ms
 Execution Time: 0.744 ms
(5 rows)

postgres=# SELECT xmin, ctid, c1 FROM data1 ;
 xmin | ctid  | c1
------+-------+-----
  517 | (0,1) | 100
(1 row)
postgres=# SELECT heap_force_freeze('data1'::regclass,
ARRAY['(0, 1)']::tid[]) ;
 heap_force_freeze
-------------------
(1 row)
postgres=# SELECT xmin, ctid, c1 FROM data1 ;
 xmin | ctid  | c1
------+-------+-----
    2 | (0,1) | 100
(1 row)
postgres=# SELECT heap_force_kill('data1'::regclass,
ARRAY['(0, 1)']::tid[]) ;
 heap_force_kill
-----------------
(1 row)
postgres=# SELECT xmin, ctid, c1 FROM data1 ;
 xmin | ctid | c1
------+------+----
(0 rows)

postgres=> CREATE FOREIGN TABLE remote1(c1 NUMERIC,
c2 VARCHAR(10)) SERVER remsvr1 ;
CREATE FOREIGN TABLE
postgres=> SELECT COUNT(*) FROM remote1 ;
  count
---------
 1000000
(1 row)
postgres=> SELECT * FROM postgres_fdw_get_connections() ;
 server_name | valid
-------------+-------
 remsvr1      | t
(1 row)
postgres=> SELECT postgres_fdw_disconnect_all() ;
 postgres_fdw_disconnect_all
-----------------------------
 t
(1 row)

postgres=> CREATE FOREIGN TABLE data1(c1 NUMERIC, c2 VARCHAR(10)) SERVER svr1
OPTIONS (batch_size '1000') ;
CREATE FOREIGN TABLE
postgres=> \d data1
                         Foreign table "public.data1"
 Column |         Type          | Collation | Nullable | Default | FDW options
--------+-----------------------+-----------+----------+---------+----------
 c1     | numeric               |           |          |         |
 c2     | character varying(10) |           |          |         |
Server: svr1
FDW options: (batch_size '1000')

The following interfaces have been added to the Foreign Data Wrapper for the bulk insert feature.