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)
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.
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
When using IDENTITY, prefer GENERATED ALWAYS for safety.
