4 SQL Statements

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