PostgreSQL - Study Notes 1 - ZhangZhihuiAAA

Fraction is precise, but slow. Floating point is fast, but imprecise.

Two floating point: types real (float4) and double precision (float8).

Domain is a combination of data type and constraint.

zzhtest=# DROP DOMAIN us_postal_code;
ERROR:  cannot drop type us_postal_code because other objects depend on it
DETAIL:  column postal of table domain_example depends on type us_postal_code
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
zzhtest=# SELECT * FROM domain_example;
 street | city | postal 
--------+------+--------
(0 rows)

zzhtest=# DROP DOMAIN us_postal_code CASCADE;
NOTICE:  drop cascades to column postal of table domain_example
DROP DOMAIN
zzhtest=# SELECT * FROM domain_example;
 street | city 
--------+------
(0 rows)

zzhtest=# DROP TABLE domain_example;
DROP TABLE

zzhtest=# \l
                                                    List of databases
    Name    |   Owner    | Encoding | Locale Provider |  Collate   |   Ctype    | Locale | ICU Rules | Access privileges 
------------+------------+----------+-----------------+------------+------------+--------+-----------+-------------------
 greenlight | greenlight | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | 
 postgres   | root       | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | 
 root       | root       | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | 
 template0  | root       | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/root          +
            |            |          |                 |            |            |        |           | root=CTc/root
 template1  | root       | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/root          +
            |            |          |                 |            |            |        |           | root=CTc/root
 zzhtest    | zzh        | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | 
(6 rows)

zzhtest=# SHOW client_encoding;
 client_encoding 
-----------------
 UTF8
(1 row)

enum_range()

date_trunc()

to_timestamp()

zzhtest=> show time zone;
 TimeZone 
----------
 Etc/UTC
(1 row)

zzhtest=> select now();
              now              
-------------------------------
 2024-12-03 11:05:25.659564+00
(1 row)

zzhtest=> set time zone 'Asia/Shanghai';
SET
zzhtest=> select now();
             now              
------------------------------
 2024-12-03 19:05:46.79514+08
(1 row)

zzhtest=> alter database set time zone 'Asia/Shanghai';

zzhtest=> show config_file;
ERROR:  permission denied to examine "config_file"
DETAIL:  Only roles with privileges of the "pg_read_all_settings" role may examine this parameter.
zzhtest=> \q
zzh@ZZHPC:~$ docker exec -it postgres17 psql --dbname=zzhtest --username=root
psql (17.1 (Debian 17.1-1.pgdg120+1))
Type "help" for help.

zzhtest=# show config_file;
               config_file                
------------------------------------------
 /var/lib/postgresql/data/postgresql.conf
(1 row)

zzhtest=> \x auto;
Expanded display is used automatically.

zzhtest=> select '2024-01-31 11:30:08'::timestamptz as utc,
       '2024-01-31 11:30:08'::timestamptz at time zone 'Asia/Shanghai' as Shanghai,
       '2024-01-31 11:30:08'::timestamptz at time zone 'CST' as America_CST,
       '2024-01-31 11:30:08'::timestamptz at time zone 'CDT' as America_CDT,
       '2024-01-31 11:30:08'::timestamptz at time zone '-06:00' as hour_offset_minus_6,
       '2024-01-31 11:30:08'::timestamptz at time zone '+06:00' as hour_offset_plus_6,
       '2024-01-31 11:30:08'::timestamptz at time zone interval '-06:00' as interval_offset_cst,
       '2024-01-31 11:30:08'::timestamptz at time zone '+08:00' as hour_offset_plus_8,
       '2024-01-31 11:30:08'::timestamptz at time zone '-08:00' as hour_offset_minus_8,
       '2024-01-31 11:30:08'::timestamptz at time zone interval '+08:00' as interval_offset_shanghai;
-[ RECORD 1 ]------------+-----------------------
utc                      | 2024-01-31 11:30:08+00
shanghai                 | 2024-01-31 19:30:08
america_cst              | 2024-01-31 05:30:08
america_cdt              | 2024-01-31 06:30:08
hour_offset_minus_6      | 2024-01-31 17:30:08
hour_offset_plus_6       | 2024-01-31 05:30:08
interval_offset_cst      | 2024-01-31 05:30:08
hour_offset_plus_8       | 2024-01-31 03:30:08
hour_offset_minus_8      | 2024-01-31 19:30:08
interval_offset_shanghai | 2024-01-31 19:30:08

Don't use hour offset. Always use interval.

zzhtest=> \x off;
Expanded display is off.

zzhtest=> select * from pg_timezone_names limit 10;
       name        | abbrev | utc_offset | is_dst 
-------------------+--------+------------+--------
 Hongkong          | HKT    | 08:00:00   | f
 Factory           | -00    | 00:00:00   | f
 Eire              | GMT    | 00:00:00   | t
 Japan             | JST    | 09:00:00   | f
 Asia/Novokuznetsk | +07    | 07:00:00   | f
 Asia/Kuwait       | +03    | 03:00:00   | f
 Asia/Almaty       | +05    | 05:00:00   | f
 Asia/Vladivostok  | +10    | 10:00:00   | f
 Asia/Omsk         | +06    | 06:00:00   | f
 Asia/Qatar        | +03    | 03:00:00   | f
(10 rows)

zzhtest=> select * from pg_timezone_names where name like '%Shanghai';
        name         | abbrev | utc_offset | is_dst 
---------------------+--------+------------+--------
 Asia/Shanghai       | CST    | 08:00:00   | f
 posix/Asia/Shanghai | CST    | 08:00:00   | f
(2 rows)

zzhtest=> select * from pg_timezone_names where abbrev = 'CST' limit 10;
      name      | abbrev | utc_offset | is_dst 
----------------+--------+------------+--------
 Asia/Chungking | CST    | 08:00:00   | f
 Asia/Taipei    | CST    | 08:00:00   | f
 Asia/Shanghai  | CST    | 08:00:00   | f
 Asia/Chongqing | CST    | 08:00:00   | f
 Asia/Macau     | CST    | 08:00:00   | f
 Asia/Macao     | CST    | 08:00:00   | f
 Asia/Harbin    | CST    | 08:00:00   | f
 ROC            | CST    | 08:00:00   | f
 CST6CDT        | CST    | -06:00:00  | f
 PRC            | CST    | 08:00:00   | f
(10 rows)

zzhtest=> select 'epoch'::timestamp;
      timestamp      
---------------------
 1970-01-01 00:00:00

zzhtest=> select 'allballs'::time;
   time   
----------
 00:00:00

zzhtest=> select 'tomorrow'::date;
    date    
------------
 2024-12-04

zzhtest=> select 'yesterday'::timestamp;
      timestamp      
---------------------
 2024-12-02 00:00:00

zzhtest=> select CURRENT_DATE + 1 as tomorrow, CURRENT_DATE - 1 as yesterday;
  tomorrow  | yesterday  
------------+------------
 2024-12-04 | 2024-12-02

zzhtest=> select CURRENT_TIME, pg_typeof(CURRENT_TIME);
    current_time    |      pg_typeof      
--------------------+---------------------
 12:43:00.247344+00 | time with time zone

zzhtest=> select LOCALTIME, pg_typeof(LOCALTIME);
    localtime    |       pg_typeof        
-----------------+------------------------
 12:46:05.994065 | time without time zone

zzhtest=> select CURRENT_DATE, pg_typeof(CURRENT_DATE);
 current_date | pg_typeof 
--------------+-----------
 2024-12-03   | date

zzhtest=> select CURRENT_TIMESTAMP, pg_typeof(CURRENT_TIMESTAMP);
       current_timestamp       |        pg_typeof         
-------------------------------+--------------------------
 2024-12-03 12:43:55.400232+00 | timestamp with time zone

zzhtest=> select now(), pg_typeof(now());
              now              |        pg_typeof         
-------------------------------+--------------------------
 2024-12-03 12:46:37.058481+00 | timestamp with time zone

zzhtest=> select '1 year 2 months 3 days 4 hours 5 minutes 6 seconds'::interval;
           interval            
-------------------------------
 1 year 2 mons 3 days 04:05:06

zzhtest=> select '1 year 2 months 3 days 04:05:06'::interval;
           interval            
-------------------------------
 1 year 2 mons 3 days 04:05:06

zzhtest=> show intervalstyle;
 IntervalStyle 
---------------
 postgres

zzhtest=> set intervalstyle = 'iso_8601';
SET
zzhtest=> select '1 year 2 months 3 days 04:05:06'::interval;
    interval    
----------------
 P1Y2M3DT4H5M6S

zzhtest=> select 'P 1Y2M3D T 4H5M6S'::interval;
ERROR:  invalid input syntax for type interval: "P 1Y2M3D T 4H5M6S"
LINE 1: select 'P 1Y2M3D T 4H5M6S'::interval;
               ^
zzhtest=> select 'P0001-02-03T04:05:06'::interval;
    interval    
----------------
 P1Y2M3DT4H5M6S

zzhtest=> select INTERVAL '2' year;
 interval 
----------
 P2Y

zzhtest=> set intervalstyle = 'postgres';
SET
zzhtest=> select INTERVAL '2' year;
 interval 
----------
 2 years

zzhtest=> select INTERVAL '1-6' YEAR TO MONTH
zzhtest-> ;
   interval    
---------------
 1 year 6 mons

zzhtest=> select INTERVAL '6000' SECOND;
 interval 
----------
 01:40:00

zzhtest=> select CURRENT_DATE, CURRENT_DATE + '1 mons'::interval as next_mon_date, CURRENT_DATE - '1 mons'::interval prev_mon_date;
 current_date |    next_mon_date    |    prev_mon_date    
--------------+---------------------+---------------------
 2024-12-03   | 2025-01-03 00:00:00 | 2024-11-03 00:00:00

Serial is not a good data type for primary keys. It still exists, but there's a better choice (identity) since Postgres 10.

nextval()

zzh@ZZHPC:~$ docker exec -it postgres17 psql --dbname=zzhtest --username=zzh
psql (17.1 (Debian 17.1-1.pgdg120+1))
Type "help" for help.

zzhtest=> CREATE SEQUENCE seq AS SMALLINT INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 30000;
CREATE SEQUENCE
zzhtest=> SELECT nextval('seq');
 nextval 
---------
       1

zzhtest=> SELECT nextval('seq');
 nextval 
---------
       2

zzhtest=> SELECT nextval('seq');
 nextval 
---------
       3

zzhtest=> SELECT setval('seq', 1);
 setval 
--------
      1

zzhtest=> SELECT nextval('seq');
 nextval 
---------
       2

zzhtest=> \q
zzh@ZZHPC:~$ docker exec -it postgres17 psql --dbname=zzhtest --username=zzh
psql (17.1 (Debian 17.1-1.pgdg120+1))
Type "help" for help.

zzhtest=> SELECT nextval('seq');
 nextval 
---------
       3