4 SQL Statements

postgres=# CREATE OR REPLACE FUNCTION alter_op_test_fn_bool_real(boolean, real)
RETURNS boolean AS $$ SELECT NULL::BOOLEAN; $$ LANGUAGE SQL IMMUTABLE ;
CREATE FUNCTION
postgres=# CREATE OPERATOR === (
     LEFTARG = boolean,
     RIGHTARG = real,
     PROCEDURE = alter_op_test_fn_bool_real) ;
CREATE OPERATOR
postgres=# ALTER OPERATOR === (boolean, real) SET (MERGES = false) ;
ALTER OPERATOR
postgres=# ALTER OPERATOR === (boolean, real) SET (HASHES = false) ;
ALTER OPERATOR

postgres=# ALTER SYSTEM SET foo.bar = 100 ;
ALTER SYSTEM
postgres=# SELECT pg_reload_conf() ;
 pg_reload_conf
----------------
 t
(1 row)
postgres=# SHOW foo.bar ;
 foo.bar
---------
 100
(1 row)

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

postgres=> CREATE TABLE data1(c1 INT, c2 INT GENERATED ALWAYS AS
(c1 * 2) STORED) ;
CREATE TABLE
postgres=> INSERT INTO data1(c1) VALUES (100) ;
INSERT 0 1
postgres=> SELECT * FROM data1 ;
 c1  | c2
-----+-----
 100 | 200
(1 row)
postgres=> ALTER TABLE data1 ALTER COLUMN c2 SET EXPRESSION AS (c1 * 200) ;
ALTER TABLE
postgres=> SELECT * FROM data1 ;
 c1  |  c2
-----+-------
 100 | 20000
(1 row)

postgres=> ALTER TABLE data1 ALTER COLUMN c1 SET STATISTICS 110 ;
ALTER TABLE
postgres=> SELECT attname, attstattarget FROM pg_attribute WHERE attrelid=
(SELECT oid FROM pg_class WHERE relname='data1') AND attname='c1' ;
 attname | attstattarget
---------+---------------
 c1      |           110
(1 row)
postgres=> ALTER TABLE data1 ALTER COLUMN c1 SET STATISTICS DEFAULT ;
ALTER TABLE
postgres=> SELECT attname,attstattarget FROM pg_attribute WHERE attrelid=
  (SELECT oid FROM pg_class WHERE relname='data1') AND attname='c1' ;
 attname | attstattarget
---------+---------------
 c1      |          null
(1 row)

postgres=> CLUSTER (VERBOSE) ;
INFO:  clustering "public.data1" using index scan on "data1_pkey1"
INFO:  "public.data1": found 0 removable, 1000000 nonremovable row versions in
5406 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.22 s, system: 0.00 s, elapsed: 0.25 s.
CLUSTER

postgres=> COPY data1 FROM stdin WITH (FORMAT csv, FORCE_NULL *) ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 100,data1
>> 200,""
>> 300,data3
>> \.
COPY 3
postgres=> SELECT * FROM data1 ;
 c1  |  c2
-----+-------
 100 | data1
 200 | null
 300 | data3
(3 rows)

postgres=> CREATE TABLE data1(c1 INT, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> COPY data1 FROM STDIN WITH (FORMAT csv, ON_ERROR ignore) ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 100,data1
>> 200,data2
>> ABC,data3
>> 400,data4
>> \.
NOTICE:  1 row were skipped due to data type incompatibility
COPY 3
postgres=> SELECT * FROM data1 ;
 c1  |    c2
-----+--------
 100 |  data1
 200 |  data2
 400 |  data4
(3 rows)

postgres=> COPY data1 FROM STDIN WITH (FORMAT csv, ON_ERROR ignore,
LOG_VERBOSITY default) ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 100,data1
>> 200,data2
>> ABC,data3
>> 400,data4
>> \.
NOTICE:  1 row was skipped due to data type incompatibility
COPY 3
postgres=> TRUNCATE TABLE data1 ;
TRUNCATE TABLE
postgres=> COPY data1 FROM STDIN WITH (FORMAT csv, ON_ERROR ignore,
LOG_VERBOSITY verbose) ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 100,data1
>> 200,data2
>> ABC,data3
>> 400,data4
>> \.
NOTICE:  skipping row due to data type incompatibility at line 3 for column c1:
"ABC"
NOTICE:  1 row was skipped due to data type incompatibility
COPY 3

postgres=> CREATE TABLE part1 (c1 INT PRIMARY KEY, c2 VARCHAR(10))
PARTITION BY RANGE(c1) USING heap ;
CREATE TABLE
postgres=> ALTER TABLE part1 SET ACCESS METHOD DEFAULT ;
ALTER TABLE

postgres=> EXPLAIN (ANALYZE, BUFFERS, FORMAT json) SELECT * FROM data1 ;
              QUERY PLAN
---------------------------------------
 [                                    +
   {                                  +
     "Plan": {                        +
       "Node Type": "Seq Scan",       +
       "Parallel Aware": false,       +
…
       "Shared I/O Write Time": 0.000,+
       "Local I/O Read Time": 0.000,  +
       "Local I/O Write Time": 0.000, +
       "Temp I/O Read Time": 0.000,   +
…

postgres=> EXPLAIN (MEMORY) SELECT COUNT(*) FROM data1 ;
                             QUERY PLAN
---------------------------------------------------------------------
 Aggregate  (cost=17906.00..17906.01 rows=1 width=8)
   ->  Seq Scan on data1  (cost=0.00..15406.00 rows=1000000 width=0)
 Planning:
   Memory: used=9kB allocated=16kB
(4 rows)

postgres=> EXPLAIN (ANALYZE, SERIALIZE BINARY) SELECT * FROM data1 ;
                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on data1  (cost=0.00..15406.00 rows=1000000 width=10) (actual
 time=0.005..36.797 rows=1000000 loops=1)
 Planning Time: 0.056 ms
 Serialization: time=84.192 ms  output=18555kB  format=binary
 Execution Time: 154.041 ms
(4 rows)

postgres=> SELECT * FROM src1 ;
 c1  |  c2
-----+---------
 100 | source1
 200 | source2
(2 rows)
postgres=> SELECT * FROM tgt1 ;
 c1  |  c2
-----+---------
 100 | target1
 300 | target3
(2 rows)
postgres=> MERGE INTO tgt1 AS t USING src1 AS s ON s.c1 = t.c1
WHEN NOT MATCHED THEN INSERT VALUES (s.c1, s.c2)
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET c2='not matched' ;
MERGE 2
postgres=> SELECT * FROM tgt1 ;
 c1  |    c2
-----+-------------
 100 | target1
200 | source2
 300 | not matched
(3 rows)

The traditional WHEN NOT MATCHED clause is treated as a statement with the BY TARGET
clause omitted.

postgres=> CREATE TABLE src1(c1 INT PRIMARY KEY, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> INSERT INTO src1 VALUES (100, 'data1'),(300, 'data3') ;
INSERT 0 2
postgres=> CREATE TABLE tgt1(c1 INT PRIMARY KEY, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> INSERT INTO tgt1 VALUES (100, 'data1'),(200, 'data2') ;
INSERT 0 2
postgres=> MERGE INTO tgt1 t
  USING src1 s ON s.c1 = t.c1
  WHEN MATCHED THEN
     UPDATE SET c2 = 'updated'
  WHEN NOT MATCHED THEN
     INSERT (c1, c2) VALUES (s.c1, s.c2)
  RETURNING merge_action(), t.* ;
 merge_action | c1  |   c2
--------------+-----+---------
 UPDATE       | 100 | updated
 INSERT       | 300 | data3
(2 rows)
MERGE 2

postgres=> CREATE TABLE src1(c1 INT PRIMARY KEY, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> INSERT INTO src1 VALUES (generate_series(1, 10), 'data1') ;
INSERT 0 10
postgres=> CREATE TABLE tgt1(c1 INT PRIMARY KEY, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE VIEW view1 AS SELECT * FROM tgt1 ;
CREATE VIEW
postgres=> MERGE INTO view1 AS v1 USING src1 AS s1
  ON v1.c1 = s1.c1
  WHEN NOT MATCHED THEN
    INSERT (c1, c2) VALUES (s1.c1, s1.c2) ;
MERGE 10

postgres=> CREATE OR REPLACE FUNCTION func1() RETURNS TEXT AS $$
DECLARE
  val1 pg_catalog.pg_class%ROWTYPE[];
  val2 pg_node_tree;
  val3 val2%TYPE[];
BEGIN
  …
END; $$ LANGUAGE plpgsql;
CREATE FUNCTION

postgres=> CREATE FUNCTION zero_divide() RETURNS INT AS $$
   DECLARE v INT := 0;
BEGIN
   RETURN 10 / v;
 END;
$$ LANGUAGE plpgsql PARALLEL SAFE ;
CREATE FUNCTION
postgres=> CREATE FUNCTION error_trap_test() RETURNS TEXT AS $$
BEGIN
   PERFORM zero_divide();
   RETURN 'no error detected!';
EXCEPTION WHEN division_by_zero THEN
  RETURN 'division_by_zero detected';
END;
$$ LANGUAGE plpgsql PARALLEL SAFE ;
CREATE FUNCTION
postgres=> SET debug_parallel_query = on ;
SET
postgres=> SELECT error_trap_test() ;
ERROR:  cannot start subtransactions during a parallel operation
CONTEXT:  PL/pgSQL function error_trap_test() line 2 during statement block
entry

postgres=> SELECT timestamp 'infinity' - timestamp '-infinity' ;
 ?column?
----------
 infinity
(1 row)
postgres=> SELECT age(timestamp '-infinity', timestamp 'infinity') ;
    age
-----------
 -infinity
(1 row)

postgres=> SHOW timezone ;
  TimeZone
------------
 Asia/Tokyo
(1 row)
postgres=> SELECT CAST('1978-07-07 19:38 America/New_York'
AS TIMESTAMP WITH TIME ZONE) AT LOCAL ;
      timezone
---------------------
 1978-07-08 08:38:00
(1 row)

postgres=> EXPLAIN (VERBOSE, COSTS OFF)
SELECT CURRENT_DATE <@ DATERANGE 'empty' ;
   QUERY PLAN
-----------------
 Result
   Output: false
(2 rows)

postgres=> EXPLAIN (VERBOSE, COSTS OFF)
SELECT CURRENT_DATE <@ DATERANGE 'empty' ;
                   QUERY PLAN
------------------------------------------------
 Result
   Output: (CURRENT_DATE <@ 'empty'::daterange)
 Query Identifier: 4806765806694262066
(3 rows)

postgres=> SELECT JSON('{ "val": 1}'::bytea FORMAT JSON ENCODING UTF8) ;
    json
-------------
 { "val": 1}
(1 row)
postgres=> SELECT JSON_SCALAR(123.45::numeric) ;
 json_scalar
-------------
 123.45
(1 row)
postgres=> SELECT JSON_SERIALIZE(JSON('{ "val" : 1 }')) ;
 json_serialize
----------------
 { "val" : 1 }
(1 row)

postgres=> SELECT
jsonb_path_query_array('[1, "yes", false]', '$[*].boolean()'),
jsonb_path_query('"2024-09-27"', '$.datetime().string()'),
jsonb_path_query('{"len": "9876543219"}', '$.len.bigint()'),
jsonb_path_query('1234.5678', '$.decimal(6, 2)'),
jsonb_path_query('{"len": "12345"}', '$.len.integer()'),
jsonb_path_query('{"len": "123.45"}', '$.len.number()'),
jsonb_path_query('"2024-09-27"', '$.date()'),
jsonb_path_query('"12:34:56"', '$.time()'),
jsonb_path_query('"12:34:56 +05:30"', '$.time_tz()'),
jsonb_path_query('"2024-09-27 12:34:56"', '$.timestamp()') ;
-[ RECORD 1 ]----------+----------------------
jsonb_path_query_array | [true, true, false]
jsonb_path_query       | "2024-09-27"
jsonb_path_query       | 9876543219
jsonb_path_query       | 1234.57
jsonb_path_query       | 12345
jsonb_path_query       | 123.45
jsonb_path_query       | "2024-09-27"
jsonb_path_query       | "12:34:56"
jsonb_path_query       | "12:34:56+05:30"
jsonb_path_query       | "2024-09-27T12:34:56"

The timestamp_tz method cannot be executed on the jsonb_path_query function because the time
zone is not IMMUTABLE.

postgres=> SELECT
jsonb_path_query('"2024-09-27 12:34:56"', '$.timestamp_tz()') ;
ERROR:  cannot convert value from timestamp to timestamptz without time zone
usage
HINT:  Use *_tz() function for time zone support.
postgres=> SELECT
jsonb_path_query_tz('"2024-09-27 12:34:56"', '$.timestamp_tz()') ;
     jsonb_path_query_tz
-----------------------------
 "2024-09-27T03:34:56+00:00"

postgres=> SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x)'
PASSING 1 AS x) ;
 json_exists
-------------
 t
(1 row)
postgres=> SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text KEEP QUOTES) ;
 json_query
------------
 "aaa"
(1 row)
postgres=> SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' DEFAULT '0' ON ERROR) ;
 json_value
------------
 0
(1 row)

postgres=> SELECT * FROM JSON_TABLE(jsonb '123', '$'
COLUMNS (item int PATH '$', foo int)) bar ;
 item | foo
------+-----
  123 |
(1 row)
postgres=>  SELECT * FROM JSON_TABLE(jsonb '"a"', '$'
COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt ;
 a
---
 2
(1 row)

postgres=> CREATE TYPE jsb_char2 AS (a char(2)) ;
CREATE TYPE
postgres=> SELECT jsonb_populate_record_valid(NULL::jsb_char2,
'{"a": "aaa"}') ;
jsonb_populate_record_valid
-----------------------------
 f
(1 row)

postgres=> CREATE DOMAIN mytext AS text ;
CREATE DOMAIN
postgres=> CREATE DOMAIN mytext_child AS mytext ;
CREATE DOMAIN
postgres=> SELECT pg_basetype('mytext'::regtype) ;
 pg_basetype
-------------
 text
(1 row)
postgres=> SELECT pg_basetype('mytext_child'::regtype) ;
 pg_basetype
-------------
 text
(1 row)

postgres=> SELECT random(100, 200), random(10.25, 20.34) ;
 random | random
--------+--------
    153 |  19.21
(1 row)

postgres=> SELECT TO_BIN(100) bin, TO_OCT(100) oct ;
   bin   | oct
---------+-----
 1100100 | 144
(1 row)

postgres=> SELECT to_regtypemod('varchar(10)'),
format_type(to_regtype('varchar(10)'), to_regtypemod('varchar(10)')) ;
 to_regtypemod |      format_type
---------------+-----------------------
            14 | character varying(10)
(1 row)

postgres=> SELECT
TO_TIMESTAMP('2024-09-27 09:12 -05', 'YYYY-MM-DD HH12:MI OF') ;
      to_timestamp
------------------------
 2024-05-26 23:12:00+09
(1 row)
postgres=> SELECT
TO_TIMESTAMP('2024-09-27 09:12 EST', 'YYYY-MM-DD HH12:MI TZ') ;
      to_timestamp
------------------------
 2024-05-26 23:12:00+09
(1 row)

postgres=> SELECT unicode_version() ;
-[ RECORD 1 ]---+-----
unicode_version | 15.1
postgres=> SELECT icu_unicode_version() ;
-[ RECORD 1 ]-------+-----
icu_unicode_version | 10.0
postgres=> SELECT unicode_assigned('ABC') ;
-[ RECORD 1 ]----+--
unicode_assigned | t

The unicode_assigned function can be executed only when the database encoding is UTF-8. The
following error will occur if it is executed in a database with encoding set to EUC_JP.

postgres=> SELECT unicode_assigned('ABC') ;
ERROR:  Unicode categorization can only be performed if server encoding is UTF8

postgres=> SELECT xmltext('foo & <"bar">') ;
              xmltext
-----------------------------------
 foo &amp; &lt;&quot;bar&quot;&gt;
(1 row)

postgres=> SELECT uuid_extract_version(gen_random_uuid()) ;
 uuid_extract_version
----------------------
                    4
(1 row)
postgres=> SELECT
uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') ;
 uuid_extract_timestamp
------------------------
 2022-02-23 04:22:22+09
(1 row)

postgres=> CREATE TABLE data1 (col1 TEXT, col2 TEXT STORAGE EXTERNAL) ;
CREATE TABLE
postgres=> INSERT INTO data1 VALUES ('A', repeat('B', 8192)) ;
INSERT 0 1
postgres=> SELECT pg_column_toast_chunk_id(col2) FROM data1 ;
-[ RECORD 1 ]------------+------
pg_column_toast_chunk_id | 16412

postgres=> EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM data1 GROUP BY col2, col1 ;
                   QUERY PLAN
-------------------------------------------------
 GroupAggregate
   Group Key: col1, col2
   ->  Index Only Scan using idx1_data1 on data1
(3 rows)

postgres=> EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM data1 GROUP BY col2, col1 ;
          QUERY PLAN
-------------------------------
 GroupAggregate
   Group Key: col2, col1
   ->  Sort
         Sort Key: col2, col1
         ->  Seq Scan on data1
(5 rows)

postgres=> EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM
(SELECT c1 FROM merge1 UNION SELECT c1 FROM merge2) ;
                          QUERY PLAN
---------------------------------------------------------------
 Aggregate
   ->  Unique
         ->  Merge Append
               Sort Key: merge1.c1
               ->  Index Only Scan using merge1_pkey on merge1
               ->  Index Only Scan using merge2_pkey on merge2
(6 rows)

postgres=> EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM
(SELECT c1 FROM merge1 UNION SELECT c1 FROM merge2) ;
                 QUERY PLAN
--------------------------------------------
 Aggregate
   ->  Unique
         ->  Sort
               Sort Key: merge1.c1
               ->  Append
                     ->  Seq Scan on merge1
                     ->  Seq Scan on merge2
(7 rows)

postgres=> \d data1
                      Table "public.data1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           | not null |
 c2     | character varying(10) |           |          |
Indexes:
    "data1_pkey" PRIMARY KEY, btree (c1)
postgres=> EXPLAIN SELECT COUNT(*) FROM data1 WHERE c1 IS NOT NULL ;
                             QUERY PLAN
---------------------------------------------------------------------
 Aggregate  (cost=17906.00..17906.01 rows=1 width=8)
   ->  Seq Scan on data1  (cost=0.00..15406.00 rows=1000000 width=0)
(2 rows)

postgres=> \d data1
                      Table "public.data1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           | not null |
 c2     | character varying(10) |           |          |
Indexes:
    "data1_pkey" PRIMARY KEY, btree (c1)
postgres=> EXPLAIN SELECT COUNT(*) FROM data1 WHERE c1 IS NOT NULL ;
                             QUERY PLAN
---------------------------------------------------------------------
 Aggregate  (cost=17906.00..17906.01 rows=1 width=8)
   ->  Seq Scan on data1  (cost=0.00..15406.00 rows=1000000 width=0)
         Filter: (c1 IS NOT NULL)
(3 rows)

postgres=> EXPLAIN (COSTS OFF) SELECT DISTINCT c2 FROM data1 ;
                     QUERY PLAN
----------------------------------------------------
 Unique
   ->  Gather Merge
         Workers Planned: 2
         ->  Sort
               Sort Key: c2
               ->  HashAggregate
                     Group Key: c2
                     ->  Parallel Seq Scan on data1
(8 rows)

postgres=> EXPLAIN (COSTS OFF) SELECT DISTINCT c2 FROM data1 ;
                     QUERY PLAN
----------------------------------------------------
 Unique
   ->  Sort
         Sort Key: c2
         ->  Gather
               Workers Planned: 2
               ->  HashAggregate
                     Group Key: c2
                     ->  Parallel Seq Scan on data1
(8 rows)

postgres=> CREATE INDEX idx1_gist1 ON gist1 USING GIST(c1) ;
CREATE INDEX
postgres=> EXPLAIN (COSTS OFF) SELECT c1, c2, c1<->POINT(5,5) dist FROM gist1
ORDER BY dist, c2 LIMIT 1 ;
                    QUERY PLAN
--------------------------------------------------
 Limit
   ->  Incremental Sort
         Sort Key: ((c1 <-> '(5,5)'::point)), c2
         Presorted Key: ((c1 <-> '(5,5)'::point))
         ->  Index Scan using idx1_data1 on data1
               Order By: (c1 <-> '(5,5)'::point)
(6 rows)

postgres=> EXPLAIN (COSTS OFF) SELECT c1, c2, c1<->POINT(5,5) dist FROM data1
ORDER BY dist, c2 LIMIT 1 ;
                   QUERY PLAN
-------------------------------------------------
 Limit
   ->  Sort
         Sort Key: ((c1 <-> '(5,5)'::point)), c2
         ->  Seq Scan on data1
(4 rows)