4 SQL Statements

postgres=> CREATE TABLE type1( c1 NUMERIC(5, -2) ) ;
CREATE TABLE
postgres=> INSERT INTO type1 VALUES (12345.678) ;
INSERT 0 1
postgres=> INSERT INTO type1 VALUES (1234567.89) ;
INSERT 0 1
postgres=> INSERT INTO type1 VALUES (12345678.901) ;
ERROR:  numeric field overflow
DETAIL:  A field with precision 5, scale -2 must round to an absolute value
less than 10^7.
postgres=> INSERT INTO type1 VALUES (-1234567.89) ;
INSERT 0 1
postgres=> SELECT * FROM type1 ;
    c1
----------
    12300
  1234600

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=085f931f52494e1f304e35571924efa

 -1234600

6fcdc2b44

(3 rows)

$ psql demodb
WARNING:  database "demodb" has a collation version mismatch
DETAIL:  The database was created using collation version 58.0.6.50, but the
operating system provides version 153.80.
HINT:  Rebuild all objects in this database that use the default collation and
run ALTER DATABASE demodb REFRESH COLLATION VERSION, or build PostgreSQL with
the right library version.
psql (15.0)
Type "help" for help.
demodb=# ALTER DATABASE demodb REFRESH COLLATION VERSION ;
NOTICE:  changing version from 58.0.6.50 to 153.80
ALTER DATABASE

postgres=> ALTER TABLE data1 SET ACCESS METHOD heap ;
ALTER TABLE

A table_rewrite trigger is fired when you execute an ALTER TABLE SET ACCESS METHOD
statement on a materialized view.

postgres=# CREATE EXTENSION file_fdw ;
CREATE EXTENSION
postgres=# CREATE SERVER textsvr FOREIGN DATA WRAPPER file_fdw ;
CREATE SERVER
postgres=# CREATE FOREIGN TABLE text1(c1 INT, c2 VARCHAR(10)) SERVER textsvr
OPTIONS (filename '/tmp/data.txt', format 'text', header 'true') ;
CREATE FOREIGN TABLE
postgres=# COPY data1 TO '/home/postgres/data1.txt' (FORMAT text, HEADER true) ;
COPY 100
postgres=# \! cat data1.txt
c1      c2
1       data1
…

postgres=# \! cat /tmp/csvlog1.csv
c1,c3
1,data1
2,data2
3,data3
postgres=# COPY csvlog1 FROM '/tmp/csvlog1.csv' WITH
(format 'csv', header 'match') ;
ERROR:  column name mismatch in header line field 2: got "c3", expected "c2"
CONTEXT:  COPY csvlog1, line 1: "c1,c3"

postgres=> CREATE TABLE part1 (c1 INT PRIMARY KEY, c2 VARCHAR(10))
PARTITION BY LIST(c1) ;
CREATE TABLE
postgres=> CLUSTER part1 USING part1_pkey ;
CLUSTER

postgres=# CREATE DATABASE icudb1 LOCALE_PROVIDER=icu ICU_LOCALE=en
COLLATION_VERSION='58.0.6.50' TEMPLATE=template0 ;
CREATE DATABASE

postgres=# CREATE DATABASE strategydb1 STRATEGY WAL_LOG ;
CREATE DATABASE

The option --strategy, which corresponds to the STRATEGY clause, has also been added to the
createdb command.

postgres=# CREATE DATABASE oiddb1 OID=17000 ;
CREATE DATABASE

postgres=> CREATE TABLE pktable1 (
   tid INT,
   id INT,
   name1 VARCHAR(10), PRIMARY KEY (tid, id)) ;
CREATE TABLE
postgres=> CREATE TABLE fktable1 (
   tid INT,
   id INT,
   fk_id_del_set_null INT,
   fk_id_del_set_default INT DEFAULT 0,
   FOREIGN KEY (tid, fk_id_del_set_null) REFERENCES pktable1
ON DELETE SET NULL (fk_id_del_set_null),
   FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES pktable1
ON DELETE SET DEFAULT (fk_id_del_set_default)
) ;
CREATE TABLE

postgres=> CREATE TABLE data1(c1 INT, c2 INT, c3 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE UNIQUE INDEX idx1_data1 ON data1(c1) NULLS DISTINCT ;
CREATE INDEX
postgres=> CREATE UNIQUE INDEX idx2_data1 ON data1(c2) NULLS NOT DISTINCT ;
CREATE INDEX
postgres=> INSERT INTO data1 VALUES (1, 1, 'data1') ;
INSERT 0 1
postgres=> INSERT INTO data1 VALUES (2, NULL, 'data2') ;
INSERT 0 1
postgres=> INSERT INTO data1 VALUES (NULL, 3, 'data3') ;
INSERT 0 1
postgres=> INSERT INTO data1 VALUES (NULL, NULL, 'data4') ;
ERROR:  duplicate key value violates unique constraint "idx2_data1"
DETAIL:  Key (c2)=(null) already exists.
postgres=> INSERT INTO data1 VALUES (NULL, 5, 'data5') ;
INSERT 0 1

Similar specifications can be made for the unique constraints of the CREATE TABLE and ALTER
TABLE statements.

postgres=> CREATE TABLE const1(c1 INT UNIQUE NULLS NOT DISTINCT, c2 INT) ;
CREATE TABLE
postgres=> ALTER TABLE const1 ADD CONSTRAINT uniq1 UNIQUE NULLS DISTINCT (c2) ;
ALTER TABLE

postgres=> CREATE UNLOGGED SEQUENCE seq1 ;
CREATE SEQUENCE
postgres=> \d seq1
                       Unlogged sequence "public.seq1"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
postgres=> SELECT relname, relpersistence FROM pg_class WHERE relname = 'seq1' ;
 relname | relpersistence
---------+----------------
 seq1    | u
(1 row)
postgres=> ALTER SEQUENCE seq1 SET LOGGED ;
ALTER SEQUENCE

If the instance crashes, UNLOGGED SEQUENCE will be reset to its default value.

postgres=> CREATE VIEW view1 WITH(security_invoker) AS SELECT * FROM data1 ;
CREATE VIEW

postgres=# SET track_io_timing = on ;
SET
postgres=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT * FROM data1 ;
                     QUERY PLAN
----------------------------------------------------
 [                                                 +
…
       "I/O Write Time": 0.000,     +
       "Temp I/O Read Time": 0.000, +
       "Temp I/O Write Time": 0.000 +
}
…

postgres=# GRANT ALTER SYSTEM ON PARAMETER log_statement TO demo ;
GRANT
postgres=# SELECT * FROM pg_parameter_acl ;
-[ RECORD 1 ]-----------------------------------
oid     | 16385
parname | log_statement
paracl  | {postgres=sA/postgres,demo=A/postgres}
postgres=# \connect postgres demo
You are now connected to database "postgres" as user "demo".
postgres=> ALTER SYSTEM SET log_statement = 'all' ;
ALTER SYSTEM

postgres=> MERGE INTO dst1 AS d USING src1 AS s ON d.c1 = s.c1
WHEN MATCHED THEN
UPDATE SET c2 = s.c2
WHEN NOT MATCHED THEN
INSERT VALUES (s.c1, s.c2) ;
MERGE 2
postgres=> MERGE INTO dst1 AS d USING src1 AS s ON d.c1 = s.c1
WHEN MATCHED AND s.c1 < 1000 THEN
DELETE
WHEN NOT MATCHED THEN
DO NOTHING ;
MERGE 2

The MERGE statement cannot be used in the WITH clause or at the same time as the COPY statement.

postgres=> WITH data1 AS (
MERGE INTO dst1 USING src1 ON (true)
WHEN MATCHED THEN DELETE
) SELECT * FROM data1 ;
ERROR:  MERGE not supported in WITH query
LINE 1: WITH data1 AS (
^

postgres=> VACUUM VERBOSE data1 ;
INFO:  vacuuming "postgres.public.data1"
INFO:  finished vacuuming "postgres.public.data1": index scans: 1
pages: 0 removed, 5406 remain, 5406 scanned (100.00% of total)
tuples: 500000 removed, 500000 remain, 0 are dead but not yet removable
removable cutoff: 757, which was 0 XIDs old when operation ended
…
avg read rate: 0.000 MB/s, avg write rate: 0.068 MB/s
buffer usage: 18975 hits, 0 misses, 1 dirtied
WAL usage: 18953 records, 0 full page images, 4007392 bytes
system usage: CPU: user: 0.11 s, system: 0.00 s, elapsed: 0.11 s
INFO:  vacuuming "postgres.pg_toast.pg_toast_32787"
…

postgres=# SELECT pg_create_physical_replication_slot('slot1') ;
 pg_create_physical_replication_slot
-------------------------------------
 (slot1,)
(1 row)
postgres=# SELECT * FROM pg_ls_replslotdir('slot1') ;
 name  | size |      modification
-------+------+------------------------
 state |  200 | 2022-10-14 09:52:04+09
(1 row)
postgres=# SELECT * FROM pg_ls_logicalsnapdir() ;
      name      | size |      modification
----------------+------+------------------------
 0-3005558.snap |  128 | 2022-10-14 00:01:29+09
(1 row1)
postgres=# SELECT * FROM pg_ls_logicalmapdir() ;
 name | size | modification
------+------+--------------
(0 rows)

Internally, it uses the READ_REPLICATION_SLOT command to read the replication slot
information.

postgres=> SELECT regexp_count('ABCABC', 'Abc', 1, 'i') ;
-[ RECORD 1 ]+--
regexp_count | 2
postgres=> SELECT regexp_like('PostgreSQL', 'Postgre(s|S)QL') ;
-[ RECORD 1 ]--
regexp_like | t
postgres=> SELECT regexp_instr('ABCDEFGHT', 'D.F') ;
-[ RECORD 1 ]+--
regexp_instr | 4
postgres=> SELECT regexp_replace('PostgreSQL', 'a|e|i|o|u', 'X', 1, 2) ;
-[ RECORD 1 ]--+-----------
regexp_replace | PostgrXSQL
postgres=> SELECT regexp_substr('ABCDEFGHT', 'D.F', 2) ;
-[ RECORD 1 ]-+----
regexp_substr | DEF

postgres=> SELECT unnest(int4multirange(int4range('5', '6'), int4range('1',
'2'))) ;
 unnest
--------
 [1,2)
 [5,6)
(2 rows)

postgres=> SELECT pg_size_pretty(pg_size_bytes('10.5 PB')) ;
 pg_size_pretty
----------------
 11 PB
(1 row)

postgres=> EXPLAIN SELECT * FROM data1 WHERE c2 ^@ '999' ;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using idx2_data1 on data1  (cost=0.42..1337.98 rows=100 width=12)
   Index Cond: ((c2 >= '999'::text) AND (c2 < '99:'::text))
   Filter: (c2 ^@ '999'::text)
(3 rows)
postgres=> EXPLAIN SELECT * FROM data1 WHERE starts_with(c2, '999') ;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using idx2_data1 on data1  (cost=0.42..1337.98 rows=100 width=12)
   Index Cond: ((c2 >= '999'::text) AND (c2 < '99:'::text))
   Filter: starts_with(c2, '999'::text)
(3 rows)

postgres=> SELECT * FROM pg_settings_get_flags('session_authorization') ;
          pg_settings_get_flags
------------------------------------------
 {NO_RESET_ALL,NO_SHOW_ALL,NOT_IN_SAMPLE}
(1 row)
postgres=> SELECT * FROM pg_settings_get_flags('bad_guc_name') ;
 pg_settings_get_flags
-----------------------
null
(1 row)

postgres=> SELECT to_char(current_date, 'of / tzh / tzm') ;
    to_char
----------------
 +09 / +09 / 00
(1 row)

postgres=> SELECT
poly_distance('((10, 10),(20,20))'::polygon, '((30,30),(40,40))'::polygon) ;
   poly_distance
--------------------
 14.142135623730951
(1 row)