数据类型 | Helium

官网中文文档：http://www.postgres.cn/docs/14/datatype.html

数字类型

数字类型由2、4或8字节的整数以及4或8字节的浮点数和可选精度小数组成。

名字	存储尺寸	描述	范围
smallint	2字节	小范围整数	-32768 to +32767
integer	4字节	整数的典型选择	-2147483648 to +2147483647
bigint	8字节	大范围整数	-9223372036854775808 to +9223372036854775807
decimal	可变	用户指定精度，精确	最高小数点前131072位，以及小数点后16383位
numeric	可变	用户指定精度，精确	最高小数点前131072位，以及小数点后16383位
real	4字节	可变精度，不精确	6位十进制精度
double precision	8字节	可变精度，不精确	15位十进制精度
smallserial	2字节	自动增加的小整数	1到32767
serial	4字节	自动增加的整数	1到2147483647
bigserial	8字节	自动增长的大整数	1到9223372036854775807

整数类型

smallint、integer、bigint 整数类型。(postgreSQL不支持无符号整型)
SQL只声明了整数类型integer（或int）、smallint和bigint。类型int2、int4和int8都是扩展，也在许多其它SQL数据库系统中使用。

-- INT 是 INTEGER 的同义词，您可以使用 INT 代替 INTEGER。
CREATE TABLE my_schema.t01 (
    s_id smallint NOT NULL,
    i_id integer NOT NULL,
    b_id bigint
);

INSERT INTO t01 VALUES (1,12,211);

smallint、integer、bigint

SMALLINT需要 2 个字节的存储大小，可以存储 (-32,767, 32,767) 范围内的任何整数。

您可以使用SMALLINT类型来存储诸如人的年龄、书籍的页数等内容。
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR (255) NOT NULL,
    pages SMALLINT NOT NULL CHECK (pages > 0)
);
INTEGER是整数类型中最常见的选择，因为它提供了存储大小、范围和性能之间的最佳平衡。

INTEGER类型需要 4 字节存储大小，可以存储 (-2,147,483,648, 2,147,483,647) 范围内的数字。

您可以将INTEGER类型用于存储相当大的整数（例如城市甚至国家的人口）的列，如下例所示：
CREATE TABLE cities (
    city_id serial PRIMARY KEY,
    city_name VARCHAR (255) NOT NULL,
    population INT NOT NULL CHECK (population >= 0) -- 注意，INT是INTEGER的同义词
);
如果你想存储超出INTEGER类型范围的整数，你可以使用BIGINT类型。

BIGINT类型需要 8 字节存储大小，可以存储 (-9,223,372,036,854,775,808, +9,223,372,036,854,775,807) 范围内的任何数字。

使用BIGINT类型不仅会消耗大量存储空间，还会降低数据库的性能，因此，您应该要有充分的理由才使用它。

任意精度数字

类型numeric可以存储非常多位的数字。我们特别建议将它用于货币金额和其它要求计算准确的数量。
numeric值的计算在可能的情况下会得到准确的结果，例如加法、减法、乘法。不过，numeric类型上的算术运算比整数类型或者下一节描述的浮点数类型要慢很多。
在随后的内容里，我们使用了下述术语：一个numeric的precision（精度）是整个数中有效位的总数，也就是小数点两边的位数。
numeric的scale（小数位数）是小数部分的数字位数，也就是小数点右边的部分。因此数字 23.5141 的精度为6而小数位数为4。可以认为整数的小数位数为零。
numeric列的最大精度和最大小数位数都是可以配置的。要声明一个类型为numeric的列，你可以用下面的语法：
- precision：可选的。它是允许存储的数字的总位数，含小数部分的位数。
- scale：可选的。小数部分的位数。
- 例如，数字 1234.567 具有 precision 7 和 scale 3 。
- 注意，DECIMAL 和 NUMERIC 是等价的。

# 精度必须为正数，小数位数可以为零或者正数。
NUMERIC(precision, scale)

# 选择小数位数为0。
NUMERIC(precision)

# 在没有指定任何精度或小数位数的情况下，创建一个“无约束的数值”列，其中可以存储任意长度的数值，直到被实现所限制。
NUMERIC

NUMERIC 适用于存储含有大量数字的数值。它的小数点前最多 131072 位数字，小数点后最多 16383 位数字。
除了普通的数值之外，浮点类型还有几个特殊值：
- 'Infinity': 无穷大。
- '-Infinity': 负无穷大。
- 'NaN': 非数字。
在SQL命令中将这些值作为常量写入时，必须在其周围加引号，例如UPDATE table SET x = ‘-Infinity’。输入时，以不区分大小写的方式识别这些字符串。无穷大值也可以拼写为inf和-inf。

CREATE TABLE test_numeric (
  numeric_5_2 NUMERIC(5,2),
  numeric_i NUMERIC
);

-- 插入一条数据
INSERT INTO 
    test_numeric (numeric_5_2)
VALUES 
    (1234.456)
RETURNING numeric_5_2;
-- 这里，因为数字 1234.234 超出了 NUMERIC(5,2) 的范围，因此 PostgreSQL 给出了一个错误。
/* Output
ERROR:  numeric field overflow
DETAIL:  A field with precision 5, scale 2 must round to an absolute value less than 10^3.
*/

INSERT INTO 
    test_numeric (numeric_5_2)
VALUES 
    (234.456)
RETURNING numeric_5_2;
-- 这里，由于 NUMERIC(5,2) 允许的小数位为 2，因此 234.456 被四舍五入为 234.46。
/* Output
 numeric_5_2
-------------
      234.46
(1 row)
*/

PostgreSQL 14 开始支持 Infinity 与 -Infinity。您不能向一个有限范围的 NUMERIC 写入 Infinity 与 -Infinity，否则 PostgreSQL 将给出一个错误。比如：

INSERT INTO 
    test_numeric (numeric_5_2)
VALUES 
    ('Infinity')
RETURNING numeric_5_2;

/* Output
ERROR:  numeric field overflow
DETAIL:  A field with precision 5, scale 2 cannot hold an infinite value.
*/

INSERT INTO 
    test_numeric (numeric_i)
VALUES 
    ('Infinity')
RETURNING numeric_i;

/*
 numeric_i
-----------
  Infinity
(1 row)
*/

INSERT INTO 
    test_numeric (numeric_i)
VALUES 
    ('-Infinity')
RETURNING numeric_i;

/*
 numeric_i
-----------
 -Infinity
(1 row)
*/

INSERT INTO 
    test_numeric (numeric_5_2)
VALUES 
    ('NaN')
RETURNING numeric_5_2;

/*
 numeric_5_2
-------------
         NaN
(1 row)
*/

浮点类型

数据类型real和double precision是不精确的、变精度的数字类型。
使用浮点类型需要注意：
- 如果你要求准确的存储和计算（例如计算货币金额），应使用numeric类型。
- 如果你想用这些类型做任何重要的复杂计算，尤其是那些你对范围情况（无穷、下溢）严重依赖的事情，那你应该仔细评诂你的实现。
- 用两个浮点数值进行等值比较不可能总是按照期望地进行。
real类型的范围是 1E-37 to 1E+37，精度至少是 6 位小数。double precision类型的范围是 1E-307 to 1E+308，精度至少是 15 位数字。
太大或者太小的值都会导致错误。如果输入数字的精度太高，那么可能发生四舍五入。太接近零的数字，如果不能体现出与零的区别就会导致下溢错误。
除了普通的数字值之外，浮点类型还有几个特殊值：
- Infinity
- -Infinity
- NaN
如果在 SQL 命令里把这些数值当作常量写，你必须在它们周围放上单引号，例如UPDATE table SET x = ‘-Infinity’。在输入时，这些字符串是以大小写不敏感的方式识别的。无穷大值也可以拼写为inf和-inf。

CREATE TABLE f01 (
    f_01 REAL,
    d_01 DOUBLE PRECISION
);

INSERT INTO f01(f_01, d_01) VALUES (12.212, 333.21212321);

real

real数据类型允许您在数据库中存储单精度浮点数。
real 类型的值占用4个字节的存储空间。其有效范围从1E-37到1E+37。
通常，您会使用real数据类型，来存储具有相对较大范围的浮点数，而且精度没那么重要，或者当您担心存储空间时也可以考虑使用。
但是，如果需要更高的精度，可以使用 double precision 数据类型。

CREATE TABLE weathers(
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    location VARCHAR(255) NOT NULL,
    wind_speed_mps REAL NOT NULL,
    temperature_celsius REAL NOT NULL,
    recorded_at TIMESTAMP NOT NULL
);

INSERT INTO weathers (location, wind_speed_mps, temperature_celsius, recorded_at) 
VALUES
    ('New York', 5.2, 15.3, '2024-04-19 09:00:00'),
    ('New York', 4.8, 14.9, '2024-04-19 10:00:00'),
    ('New York', 6.0, 16.5, '2024-04-19 11:00:00'),
    ('New York', 5.5, 15.8, '2024-04-19 12:00:00'),
    ('New York', 4.3, 14.2, '2024-04-19 13:00:00'),
    ('New York', 5.9, 16.1, '2024-04-19 14:00:00'),
    ('New York', 6.8, 17.3, '2024-04-19 15:00:00'),
    ('New York', 5.1, 15.6, '2024-04-19 16:00:00'),
    ('New York', 4.7, 14.8, '2024-04-19 17:00:00'),
    ('New York', 5.3, 15.9, '2024-04-19 18:00:00');

SELECT
  AVG(wind_speed_mps)       wind_speed,
  AVG(temperature_celsius) temperature_celsius
FROM
  weathers
WHERE
  location = 'New York'
  AND DATE(recorded_at) = '2024-04-19';

/*
    wind_speed     | temperature_celsius
-------------------+---------------------
 5.360000038146973 |  15.639999961853027
(1 row)
*/

double precision

在 PostgreSQL 中，double precision是一种不精确的、可变精度的数值类型。
不精确意味着 PostgreSQL 无法将某些值精确地转换为内部格式，只能将它们存储为近似值。因此，存储和查询的值可能会出现细微的差异。
如果应用程序需要精确的存储和计算，建议改用 numeric 类型。

CREATE TABLE temperatures (
    id SERIAL PRIMARY KEY,
    location TEXT NOT NULL,
    temperature DOUBLE PRECISION
);

INSERT INTO
  temperatures (location, temperature)
VALUES
  ('Lab Room 1', 23.5),
  ('Server Room 1', 21.8),
  ('Server Room 2', 24.3)
RETURNING *;

/*
 id |   location    | temperature
----+---------------+-------------
  1 | Lab Room 1    |        23.5
  2 | Server Room 1 |        21.8
  3 | Server Room 2 |        24.3
(3 rows)
*/

SELECT AVG(temperature) FROM temperatures;

/*
 avg
------
 23.2
*/

存储不精确的数值。

CREATE TABLE t(c double precision);

INSERT INTO t(c) VALUES(0.1), (0.1), (0.1) RETURNING *;

SELECT SUM(c) FROM t;
-- 输出表示0.1、0.1和0.1的和不是0.3，而是0.30000000000000004。
-- 这表明 PostgreSQL 不能使用double precision类型来存储确切的数字0.1。
/*
         sum
---------------------
 0.30000000000000004
(1 row)
*/

插入过小的数字。

INSERT INTO t(c) VALUES (1E-400);
-- 原因是这个数字太小，非常接近于零。
-- 由于 double precision 类型的限制，PostgreSQL 无法存储它。
/*
ERROR:  "0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001" is out of range for type double precision
*/

序列类型

smallserial、serial和bigserial类型不是真正的类型，它们只是为了创建唯一标识符列而存在的方便符号（类似其它一些数据库中支持的AUTO_INCREMENT属性）。

CREATE TABLE tablename (
    colname SERIAL
);

-- 等价于以下语句：

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

因此，我们就创建了一个整数列并且把它的缺省值安排为从一个序列发生器取值。
应用了一个NOT NULL约束以确保空值不会被插入（在大多数情况下你可能还希望附加一个UNIQUE或者PRIMARY KEY约束避免意外地插入重复的值，但这个不是自动发生的）。
最后，该序列被标记为“属于”该列，这样当列或表被删除时该序列也会被删除。

CREATE TABLE t02 (
    id SERIAL,
    a varchar(11) NOT NULL
);

INSERT INTO t02(a) VALUES ('asdasd');
INSERT INTO t02(a) VALUES ('asdasd01');

货币类型

money类型存储固定小数精度的货币数字。可接受的输入格式很多，包括整数和浮点数文字，以及常用的货币格式，如’$1,000.00’。

名字	存储尺寸	描述	范围
money	8 bytes	货币额	-92233720368547758.08到+92233720368547758.07

由于这种数据类型的输出是区域敏感的，因此将money数据装入到一个具有不同lc_monetary设置的数据库是不起作用的。为了避免这种问题，在恢复一个转储到一个新数据库中之前，应确保新数据库的lc_monetary设置和被转储数据库的相同或者具有等效值。
数据类型numeric、int和bigint的值可以被造型成money。从数据类型real和double precision的转换可以通过先造型成numeric来实现，例如：
但是，我们不推荐这样做。浮点数不应该被用来处理货币，因为浮点数可能会有圆整错误。

postgres=> SELECT '12.34'::float8::numeric::money;
 money  
--------
 $12.34
(1 row)

一个money值可以在不损失精度的情况下被造型成numeric。转换到其他类型可能会丢失精度，并且必须采用两个阶段完成：
- 一个money值被一个整数值除的除法结果会被截去分数部分。

postgres=> SELECT '52093.89'::money::numeric::float8;
  float8  
----------
 52093.89
(1 row)

使用示例。

CREATE TABLE financials (
    id SERIAL PRIMARY KEY,
    amount money
);

INSERT INTO financials (amount) VALUES
('12345.67'),  -- 正常金额
('-12345.67'), -- 负金额
('9999999999999999.99');  -- 接近最大值

-- 查询所有记录
SELECT * FROM financials;

/* Output：
 id |          amount           
----+---------------------------
  1 |                $12,345.67
  2 |               -$12,345.67
  3 | $9,999,999,999,999,999.99
(3 rows)
*/

-- 查询金额总和
SELECT SUM(amount) FROM financials;

/* Output：
            sum            
---------------------------
 $9,999,999,999,999,999.99
(1 row)
*/

注意：默认的货币符号为$，货币符号由lc_monetary设置决定。

postgres=> show lc_monetary;
 lc_monetary 
-------------
 C
(1 row)

修改货币符号。
- 修改 postgresql.conf 配置文件参数 lc_monetary。
- 或者 ALTER 修改，或set lc_monetary='zh_CN.UTF-8'。
可能 PostgreSQL 不支持 zh_CN.UTF-8 区域，通过sql查看支持区域。

SELECT * FROM pg_catalog.pg_collation;

布尔类型

名字	存储字节	描述
boolean	1字节	状态为真或假

PostgreSQL 对布尔值的处理非常灵活，true, 'true', 't', 'yes', 'y', '1'都被视为真，false, 'false', 'f', 'no', 'n', '0'都被视为假。注意，这里所有的值不区分大小写。除了 true 和 false，其他的都是字符类型。
PostgreSQL 布尔类型允许 NULL 值。BOOLEAN 可以简写为 BOOL。
请注意，在 SQL 标准中，一个布尔值只接受 TRUE，FALSE，和 NULL（表示未知unknown）。前导或尾随空格并不重要，并且除true和false之外的所有常量值都必须用单引号引起来。

-- 设置默认值 is_ok BOOL DEFAULT 't'
CREATE TABLE test_boolean (
  v VARCHAR NOT NULL,
  b BOOLEAN NOT NULL
);

INSERT INTO test_boolean (v, b)
VALUES
    (TRUE, TRUE),
    (FALSE, FALSE),
    ('t', 't'),
    ('f', 'f'),
    ('T', 'T'),
    ('F', 'F'),
    ('1', '1'),
    ('0', '0'),
    ('y', 'y'),
    ('n', 'n'),
    ('Y', 'Y'),
    ('N', 'N'),
    ('yes', 'yes'),
    ('no', 'no'),
    ('YES', 'YES'),
    ('NO', 'NO')
RETURNING *;

/* Output：
   v   | b
--------+---
 true  | t
 false | f
 t     | t
 f     | f
 T     | t
 F     | f
 1     | t
 0     | f
 y     | t
 n     | f
 Y     | t
 N     | f
 yes   | t
 no    | f
 YES   | t
 NO    | f
(16 rows)
*/

SELECT *
FROM test_boolean
WHERE b = 'Y';

/* Output：
  v   | b
------+---
 true | t
 t    | t
 T    | t
 1    | t
 y    | t
 Y    | t
 yes  | t
 YES  | t
(8 rows)
*/

操作符

常用的逻辑操作符有：AND、OR、NOT。

a	b	a AND b	a OR b
TRUE	NULL	NULL	TRUE
FALSE	NULL	FALSE	NULL
NULL	NULL	NULL	NULL

常用比较符 IS。

expression IS  TRUE;
expression IS  FALSE;
expression IS  UNKNOWN;

示例。

SELECT * FROM test_boolean WHERE b IS TRUE;
SELECT * FROM test_boolean WHERE b;

/*
  v   | b 
------+---
 true | t
 t    | t
 T    | t
 1    | t
 y    | t
 Y    | t
 yes  | t
 YES  | t
(8 rows)
*/

SELECT * FROM test_boolean WHERE b IS FALSE;
SELECT * FROM test_boolean WHERE NOT b;

/*
   v   | b 
-------+---
 false | f
 f     | f
 F     | f
 0     | f
 n     | f
 N     | f
 no    | f
 NO    | f
(8 rows)
*/

SELECT * FROM test_boolean WHERE b IS UNKNOWN;
SELECT * FROM test_boolean WHERE b IS NULL;

/*
 v | b 
---+---
(0 rows)
*/

SELECT (1>2) IS UNKNOWN;

/*
 ?column? 
----------
 f
(1 row)
*/

SELECT NULL IS UNKNOWN;

/*
 ?column? 
----------
 t
(1 row)
*/

SELECT 'yes'::boolean;

/*
 bool 
------
 t
(1 row)
*/

UUID类型

这种标识符是一个128位的量，它由一个精心选择的算法产生，该算法能保证在已知空间中任何其他使用相同算法的人能够产生同一个标识符的可能性非常非常小。因此，对于分布式系统，这些标识符相比序列生成器而言提供了一种很好的唯一性保障，序列生成器只能在一个数据库中保证唯一。
一个UUID被写成一个小写十六进制位的序列，该序列被连字符分隔成多个组：首先是一个8位组，接下来是三个4位组，最后是一个12位组。总共的32位（十六进制位）表示了128个二进制位。一个标准形式的UUID类似于：

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

PostgreSQL也接受另一种输入形式：使用大写位、标准格式被花括号包围、忽略某些或者全部连字符、在任意4位组后面增加一个连字符。例如：

A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}

要在 PostgreSQL 中生成 UUID，请使用 gen_random_uuid() 函数，如下：

-- gen_random_uuid() 根据随机数生成 uuid
-- 如果想根据 MAC 地址生成 uuid，安装 uuid-ossp 模块
SELECT gen_random_uuid();

/* Output：
           gen_random_uuid
--------------------------------------
 226199a2-b0c7-4ca6-a4f4-b4b31fba2a64
(1 row)
*/

CREATE TABLE fruits (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name VARCHAR NOT NULL
);

INSERT INTO fruits (name)
VALUES
    ('Apple'),
    ('Peach'),
    ('Banana')
RETURNING *;

/* Output：
                  id                  |  name
--------------------------------------+--------
 c7f32415-d35e-4f57-8496-7978f422ff6a | Apple
 c82ab208-4520-424f-a225-131d7a91eadf | Peach
 2c53ece8-440f-4374-8cb0-b7a5826a62f8 | Banana
(3 rows)
*/

uuid-ossp 模块

PostgreSQL 允许您存储和比较 UUID 值，但它的核心不包含用于生成 UUID 值的函数。
相反，它依赖于提供特定算法来生成 UUID 的第三方模块。例如，uuid-ossp模块提供了一些方便的函数来实现生成 UUID 的标准算法。
要安装uuid-ossp模块，请使用如下 CREATE EXTENSION 语句：

-- IF NOT EXISTS子句允许您避免重新安装模块
-- 操作账户需要相关权限
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

uuid-ossp 模块函数参考
要根据计算机的 MAC 地址、当前时间戳和随机值的组合生成 UUID 值，请使用以下uuid_generate_v1()函数：

postgres=# SELECT uuid_generate_v1();
           uuid_generate_v1           
--------------------------------------
 7f13869e-7bb3-11ef-9b7a-0242ac140002
(1 row)

如果您想仅根据随机数生成 UUID 值，可以使用uuid_generate_v4()函数。例如：

postgres=# SELECT uuid_generate_v4();
           uuid_generate_v4           
--------------------------------------
 dfde1e70-6afe-443e-9a06-40011a86ff17
(1 row)

使用示例。

CREATE TABLE contacts (
    contact_id uuid DEFAULT uuid_generate_v4 (),
    first_name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    phone VARCHAR,
    PRIMARY KEY (contact_id)
);

INSERT INTO contacts (
    first_name,
    last_name,
    email,
    phone
)
VALUES
    (
        'AD',
        'S2ith',
        '3j3ohi5th@example.com',
        '418-267-5432'
    ),
    (
        'KD',
        'SC1h',
        '3ji.5th@example.com',
        '408-212-2673'
    ),
    (
        'EC',
        'dsw32',
        'a2x.sm@example.com',
        '408-243-2653'
    );

postgres=> SELECT * FROM my_schema.contacts;
              contact_id              | first_name | last_name |         email         |    phone     
--------------------------------------+------------+-----------+-----------------------+--------------
 98fafd47-7319-4ba6-9ff4-dd8c8fe2aeb8 | AD         | S2ith     | 3j3ohi5th@example.com | 418-267-5432
 5a83c3a8-42de-4f4a-84d0-7d00232fa250 | KD         | SC1h      | 3ji.5th@example.com   | 408-212-2673
 56546027-f971-4ee4-b251-1eb1af74fbff | EC         | dsw32     | a2x.sm@example.com    | 408-243-2653
(3 rows)

字符类型

字符类型以文本形式存储数据，PostgreSQL 提供了三种字符数据类型：CHAR，VARCHAR，和 TEXT。

———————— 名字 ————————	描述
character varying(n), varchar(n)	有限制的变长
character(n), char(n)	定长，空格填充
text	无限变长

下面说明了 PostgreSQL 中的三种字符类型的用法：
- CHAR(n) 存储固定长度的字符串，长度不足时，使用空白填充。
- VARCHAR(n) 存储具有长度限制的可变长度文本。
- VARCHAR 存储没有长度限制的可变长度文本。它与 TEXT 完全相同。
- TEXT 存储没有长度限制的可变长度文本。
CHAR(n) 和 VARCHAR(n) 最多可存储 n 字符。VARCHAR 与 TEXT 完全相同。
对于限制了长度的字符类型来说，如果写入的内容超过了限制的长度，PostgreSQL 采用如下的策略：
- 如果超出长度的部分只有空格，则多余的空格会被截断。
- 如果超出长度的部分不只是空格，则给出错误提示。

CREATE TABLE test_character_type (
  char_1 CHAR(1),
  varchar_10 VARCHAR(10),
  txt TEXT
);

CHAR

CHAR 类型使用示例。

-- 因为该 char_1 列的数据类型是 CHAR(1)，我们试图在该列中插入一个包含 2 个字符的字符串: 'OK'。
INSERT INTO 
    test_character_type (char_1)
VALUES
    ('OK')
RETURNING *;

-- ERROR:  value too long for type character(1)

-- 使用下面的语句向表中插入一个新行超出 char_1 列长度的值，超出的部分为空格
INSERT INTO 
    test_character_type (char_1)
VALUES
    ('好    ')
RETURNING *;
-- 这里插入成功了。这是因为超出的部分是空格，PostgreSQL 自动截断了空格。

/* Output：
 char_1 | varchar_10 | txt
--------+------------+-----
 好     | (NULL)     | (NULL)
*/

VARCHAR

VARCHAR 类型使用示例。

-- 使用下面的语句向表中插入一个新行超出 varchar_10 列长度的值
INSERT INTO 
    test_character_type (varchar_10)
VALUES
    ('Hello World')
RETURNING *;
-- 这是因为该 varchar_10 列的数据类型是 VARCHAR(10)，我们试图在该列中插入一个包含 11 个字符的字符串: 'Hello World'。

-- ERROR:  value too long for type character varying(10)

-- 使用下面的语句向表中插入一个新行超出 varchar_10 列长度的值，超出的部分为空格
INSERT INTO 
    test_character_type (varchar_10)
VALUES
    ('HelloWor好d    ')
RETURNING *;
-- 这里插入成功了。这是因为超出的部分是空格，PostgreSQL 自动截断了空格

/* Output：
 char_1 | varchar_10  | txt
--------+-------------+-----
 (NULL) | HelloWor好d | (NULL)
(1 row)
*/

TEXT

使用下面的语句向表中插入一个新行。

INSERT INTO 
    test_character_type (txt)
VALUES
    ('TEXT column can store 你好 a string of any length')
RETURNING txt;

/* Output：
                     txt
----------------------------------------------
 TEXT column can store 你好 a string of any length
(1 row)
*/

日期/时间类型

名字	存储尺寸	描述	最小值	最大值	解析度
timestamp [ (p) ] [ without time zone ]	8字节	包括日期和时间（无时区）	4713 BC	294276 AD	1微秒
timestamp [ (p) ] with time zone	8字节	包括日期和时间，有时区	4713 BC	294276 AD	1微秒
date	4字节	日期（没有一天中的时间）	4713 BC	5874897 AD	1日
time [ (p) ] [ without time zone ]	8字节	一天中的时间（无日期）	00:00:00	24:00:00	1微秒
time [ (p) ] with time zone	12字节	仅仅是一天中的时间（没有日期），带有时区	00:00:00+1459	24:00:00-1459	1微秒
interval [ fields ] [ (p) ]	16字节	时间间隔	-178000000年	178000000年	1微秒

DATE

创建一个新表，名称为 test_date。
- 这是一个整数类型的列，被定义为自增主键。每当有新记录插入到表中时，如果没有为 id 指定值，数据库会自动为它分配下一个可用的整数值。
- 这是一个日期类型的列，默认值是当前日期(CURRENT_DATE)，并且不允许为空。

CREATE TABLE test_date (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  date_value DATE NOT NULL DEFAULT CURRENT_DATE
);

要将数据插入 DATE 列，您必须确保数据采用 yyyy-mm-dd 格式。

INSERT INTO test_date (date_value)
VALUES
  ('2022-08-29'),
  ('2022-08-30'),
  ('2022-08-31')
RETURNING *;

/* Output：
 id | date_value
----+------------
  1 | 2022-08-29
  2 | 2022-08-30
  3 | 2022-08-31
(3 rows)
*/

要更新 DATE 数据，使用该 UPDATE 语句并传入一个 yyyy-mm-dd 格式的值。

UPDATE 
    test_date
SET 
    date_value = '2022-09-01'
WHERE 
    id = 3
RETURNING *;

/* Output：
 id | date_value
----+------------
  3 | 2022-09-01
(1 row)
*/

查找日期为 2022-08-30，请使用以下语句：

SELECT 
    *
FROM 
    test_date
WHERE 
    date_value = '2022-08-30';

/* Output：
 id | date_value
----+------------
  2 | 2022-08-30
(1 row)
*/

查找日期小于 2022-09-01 的所有的行，如下：

SELECT 
    *
FROM 
    test_date
WHERE 
    date_value < '2022-09-01';

/* Output：
 id | date_value
----+------------
  1 | 2022-08-29
  2 | 2022-08-30
(2 rows)
*/

PostgreSQL 提供了 TO_CHAR() 函数以按照指定格式输出日期值。 TO_CHAR() 函数接受两个参数。第一个参数是要格式化的日期值，第二个参数是格式。要以 yyyy/mm/dd 格式显示日期，请使用以下语句：

SELECT
  id,
  to_char(date_value, 'yyyy/mm/dd')
FROM
  test_date;

/* Output：
 id |  to_char
----+------------
  1 | 2022/08/29
  2 | 2022/08/30
  3 | 2022/09/01
(3 rows)
*/

日期相关函数。

-- 获取当前日期，使用 current_date，结果的格式为：yyyy-mm-dd
SELECT NOW()::date;
SELECT current_date;

/* Output：
 current_date
--------------
 2023-09-24
*/

-- 要以特定格式输出日期值，可以使用TO_CHAR()函数。
-- 该TO_CHAR()函数接受两个参数。第一个参数是要格式化的值，第二个参数是定义输出格式的模板。
SELECT TO_CHAR(NOW() :: DATE, 'dd/mm/yyyy');

/*
  to_char
------------
 23/06/2023
(1 row)
*/

SELECT TO_CHAR(NOW() :: DATE, 'Mon dd, yyyy');

/*
   to_char
--------------
 Sep 26, 2023
(1 row)
*/


-- 指定格式的日期字符串转为日期值，使用 to_date() 函数
SELECT to_date('2022-09-30', 'YYYY-MM-DD');

/* Output:
  to_date
------------
 2022-09-30
*/

-- 获取两个日期之间的间隔
SELECT
    first_name,
    last_name,
    now() - hire_date as diff
FROM
    employees;

/*
 first_name | last_name |           diff
------------+-----------+---------------------------
 Shannon    | Freeman   | 4191 days 08:25:30.634458
 Sheila     | Wells     | 4922 days 08:25:30.634458
 Ethel      | Webb      | 5652 days 08:25:30.634458
(3 rows)
*/

-- 获取日期值中的年、月、日部分，使用 date_part() 函数
SELECT
date_part('year', current_date) "year",
date_part('month', current_date) "month",
date_part('day', current_date) "day";

/* Output：
 year | month | day
------+-------+-----
 2023 |     9 | 24
*/

-- 计算年龄（年、月、日）
-- 要计算相对于当前日期的年龄（以年、月和日为单位），请使用AGE()函数。
SELECT
    employee_id,
    first_name,
    last_name,
    AGE(birth_date)
FROM
    employees;

/*
 employee_id | first_name | last_name |           age
-------------+------------+-----------+-------------------------
           1 | Shannon    | Freeman   | 36 years 5 mons 22 days
           2 | Sheila     | Wells     | 38 years 4 mons 18 days
           3 | Ethel      | Webb      | 41 years 5 mons 22 days
(3 rows)
*/

-- 如果将日期值传递给AGE()函数，它将从当前日期中减去该日期值。如果将两个参数传递给AGE()函数，它将从第一个参数中减去第二个参数。
SELECT
    employee_id,
    first_name,
    last_name,
    age('2015-01-01',birth_date)
FROM
    employees;

/*
 employee_id | first_name | last_name |           age
-------------+------------+-----------+--------------------------
           1 | Shannon    | Freeman   | 35 years
           2 | Sheila     | Wells     | 36 years 10 mons 24 days
           3 | Ethel      | Webb      | 40 years
(3 rows)
*/

-- 要从日期值获取年、季度、月、周、日，可以使用EXTRACT()函数。
SELECT
    employee_id,
    first_name,
    last_name,
    EXTRACT (YEAR FROM birth_date) AS YEAR,
    EXTRACT (MONTH FROM birth_date) AS MONTH,
    EXTRACT (DAY FROM birth_date) AS DAY
FROM
    employees;

/*
 employee_id | first_name | last_name | year | month | day
-------------+------------+-----------+------+-------+-----
           1 | Shannon    | Freeman   | 1980 |     1 |   1
           2 | Sheila     | Wells     | 1978 |     2 |   5
           3 | Ethel      | Webb      | 1975 |     1 |   1
(3 rows)
*/

TIMESTAMP

PostgreSQL 时间戳类型用来存储日期和时间组合的数据类型。PostgreSQL 支持两种类型的时间戳数据类型，包括 TIMESTAMP 和 TIMESTAMPTZ：
- TIMESTAMP 全写为：TIMESTAMP WITHOUT TIME ZONE，用来存储没有时区的时间戳。
- TIMESTAMPTZ 全写为：TIMESTAMP WITH TIME ZONE，用来存储带时区的时间戳。
TIMESTAMP 和 TIMESTAMPTZ 都使用 8 个字节来存储时间戳值。
在 PostgreSQL 内部，TIMESTAMPTZ 类型的值被存储为它对应的 UTC 值。当您从数据库中查询 TIMESTAMPTZ 值时，PostgreSQL 会将 UTC 值转换回数据库服务器、用户或当前数据库连接设置的时区的时间值。PostgreSQL 以 UTC 值存储timestamptz。
- 当您向timestamptz列中插入值时，PostgreSQL 会将该timestamptz值转换为 UTC 值并将该 UTC 值存储在表中。
- 当您从数据库查询timestamptz值时，PostgreSQL 会将 UTC 值转换回数据库服务器、用户或当前数据库连接设置的时区的时间值。
创建一个新表，名称为 test_timestamp。
- id 列是标识每行的主键列，它是一个标识列。
- 该 timestamp_v 列是不带时区的时间戳类型，它不能为 null，并且默认为由 now() 函数产生的当前时间。
- 该 timestamptz_v 列是带时区的时间戳类型，它不能为 null，并且默认为由 now() 函数产生的当前时间。

CREATE TABLE test_timestamp (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  timestamp_v TIMESTAMP NOT NULL DEFAULT now(),
  timestamptz_v TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO test_timestamp (timestamp_v, timestamptz_v)
VALUES
    ('2022-08-30 10:10:10', '2022-08-30 10:10:10'),
    ('2022-08-30', '2022-08-30')
RETURNING *;

/* Output：
 id |     timestamp_v     |     timestamptz_v
----+---------------------+------------------------
  1 | 2022-08-30 10:10:10 | 2022-08-30 10:10:10+08
  2 | 2022-08-30 00:00:00 | 2022-08-30 00:00:00+08
(2 rows)
*/

您不能为时间戳类型指定一个时间类型的值，PostgreSQL 将会给出一个错误。

INSERT INTO test_timestamp (timestamp_v, timestamptz_v)
VALUES ('10:10:10', '10:10:10')
RETURNING *;

/* Output：
ERROR:  invalid input syntax for type timestamp: "10:10:10"
LINE 2: VALUES ('10:10:10', '10:10:10')
*/

要更新 TIMESTAMP 数据，使用该 UPDATE 语句并传入一个正确格式的值。

UPDATE
  test_timestamp
SET
  timestamp_v = '2022-08-30 11:11:11',
  timestamptz_v = '2022-08-30 11:11:11'
WHERE id = 1
RETURNING *;

/* Output：
 id |     timestamp_v     |     timestamptz_v
----+---------------------+------------------------
  1 | 2022-08-30 11:11:11 | 2022-08-30 11:11:11+08
(1 row)
*/

查找日期为 2022-08-30 的所有的行，如下：

SELECT *
FROM test_timestamp
WHERE to_char(timestamp_v, 'YYYY-MM-DD') = '2022-08-30';

/* Output：
 id |     timestamp_v     |     timestamptz_v
----+---------------------+------------------------
  2 | 2022-08-30 00:00:00 | 2022-08-30 00:00:00+08
  1 | 2022-08-30 11:11:11 | 2022-08-30 11:11:11+08
(2 rows)
*/

查找日期大于 2022-08-30 的所有的行，如下：

SELECT *
FROM test_timestamp
WHERE timestamp_v > '2022-08-30';

/* Output：
 id |     timestamp_v     |     timestamptz_v
----+---------------------+------------------------
  1 | 2022-08-30 11:11:11 | 2022-08-30 11:11:11+08
(1 row)
*/

以 yyyy/mm/dd 格式显示时间，请使用以下语句：

SELECT
  id,
  to_char(timestamp_v, 'YYYY/MM/DD HH24:MI:SS'),
  to_char(timestamptz_v, 'YYYY/MM/DD HH24:MI:SS TZH')
FROM
  test_timestamp;

/* Output：
 id |       to_char       |         to_char
----+---------------------+-------------------------
  2 | 2022/08/30 00:00:00 | 2022/08/30 00:00:00 +08
  1 | 2022/08/30 11:11:11 | 2022/08/30 11:11:11 +08
(2 rows)
*/

时间戳相关函数。

-- 获取当前时间戳，请使用 now() 或者 current_timestamp
-- 除此之外，您还可以使用 transaction_timestamp(), statement_timestamp(), localimestamp(), 或 clock_timestamp() 获取当前的时间戳。
SELECT now(), current_timestamp;

/* Output：
              now              |       current_timestamp       
-------------------------------+-------------------------------
 2024-09-25 12:30:58.789316+08 | 2024-09-25 12:30:58.789316+08
(1 row)
*/

-- 使用 current_timestamp 指定时间戳的小数秒精度
SELECT current_timestamp, current_timestamp(2);

/* Output：
       current_timestamp       |     current_timestamp     
-------------------------------+---------------------------
 2024-09-25 12:32:04.488063+08 | 2024-09-25 12:32:04.49+08
(1 row)
*/

-- 获取时间值中的年、月、日、时、分、秒的值，请使用 date_part() 函数
SELECT
    date_part('year', now()) "year",
    date_part('month', now()) "month",
    date_part('day', now()) "day",
    date_part('hour', now()) "hour",
    date_part('minute', now()) "minute",
    date_part('second', now()) "second";

/* Output：
 year | month | day | hour | minute |  second   
------+-------+-----+------+--------+-----------
 2024 |     9 |  25 |   12 |     32 | 54.977147
(1 row)
*/

更多参考：https://www.rockdata.net/zh-cn/tutorial/type-timestamp/

TIME

PostgreSQL 支持 TIME 数据类型，以存储时间值。
PostgreSQL 存储 TIME 数据类型值使用 8 个字节。TIME 数据类型允许的范围是从 00:00:00 到 24:00:00。
TIME 数据类型采用以下格式存储：HH:MI:SS.ssssss
- HH 表示小时
- MI 表示分钟
- SS 表示秒
- ssssss 表示小数秒

枚举类型

在 PostgreSQL 中，枚举类型是是一组有序的常量值的集合。比如，您可以使用枚举类型用作订单的状态值。
如果没有枚举类型，您可能使用整数 1, 2, 3 … 或者字符串表示，但是很容易带来错误，比如书写错误等。用了枚举类型之后，数据库可以保证不能输入错误的值。
枚举标签是大小写敏感的，因此’happy’与’HAPPY’是不同的。标签中的空格也是有意义的。
要使用枚举值，您需要使用 CREATE TYPE 语句创建一个自定义的枚举值。
- name 是要创建的枚举类型的名称。
- label 是枚举类型中的常量值。

shoping=> \h CREATE TYPE
Command:     CREATE TYPE
Description: define a new data type
Syntax:
CREATE TYPE name AS ENUM
    ( [ 'label' [, ... ] ] )

URL: https://www.postgresql.org/docs/14/sql-createtype.html

创建一个表示星期的枚举类型。

CREATE TYPE my_week AS ENUM (
    'Monday',
    'Tuesday',
    'Wednesday',
    'Thursday',
    'Friday',
    'Saturday',
    'Sunday'
);

-- 创建一个销售表
CREATE TABLE week_day_sales (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    week_day my_week NOT NULL,
    sales integer NOT NULL
);

-- 插入数据
INSERT INTO week_day_sales (week_day, sales)
VALUES
    ('Monday', 110),
    ('Tuesday', 120),
    ('Wednesday', 130),
    ('Thursday', 140),
    ('Friday', 150),
    ('Saturday', 160),
    ('Sunday', 170),
    ('Monday', 210),
    ('Tuesday', 220),
    ('Wednesday', 230),
    ('Thursday', 240),
    ('Friday', 250),
    ('Saturday', 260),
    ('Sunday', 270);

向枚举列插入一个错误的值，PostgreSQL 将给出一个错误：

INSERT INTO week_day_sales (week_day, sales)
VALUES ('Error', 110);

-- ERROR:  invalid input value for enum my_week: "Error"
-- LINE 2: VALUES ('Error', 110);

检索 week_day_sales 表的所有的行。

SELECT * FROM week_day_sales;

/* Output：
 id | week_day  | sales
----+-----------+-------
  1 | Monday    |   110
  2 | Tuesday   |   120
  3 | Wednesday |   130
  4 | Thursday  |   140
  5 | Friday    |   150
  6 | Saturday  |   160
  7 | Sunday    |   170
  8 | Monday    |   210
  9 | Tuesday   |   220
 10 | Wednesday |   230
 11 | Thursday  |   240
 12 | Friday    |   250
 13 | Saturday  |   260
 14 | Sunday    |   270
(14 rows)
*/

按照枚举类型的值的顺序排序，请使用下面的语句。

SELECT * FROM week_day_sales ORDER BY week_day;

/* Output：
 id | week_day  | sales
----+-----------+-------
  1 | Monday    |   110
  8 | Monday    |   210
  2 | Tuesday   |   120
  9 | Tuesday   |   220
  3 | Wednesday |   130
 10 | Wednesday |   230
  4 | Thursday  |   140
 11 | Thursday  |   240
  5 | Friday    |   150
 12 | Friday    |   250
  6 | Saturday  |   160
 13 | Saturday  |   260
  7 | Sunday    |   170
 14 | Sunday    |   270
(14 rows)
*/

PostgreSQL 提供了几个用于枚举类型的函数：
- enum_first() 函数返回由参数指定的枚举类型的第一个枚举值。
- enum_last() 函数返回由参数指定的枚举类型的最后一个枚举值。
- enum_range() 函数返回由参数指定的枚举类型的所有枚举值，或者指定的范围内的枚举值。

网络地址类型

PostgreSQL提供用于存储 IPv4、IPv6 和 MAC 地址的数据类型。用这些数据类型存储网络地址比用纯文本类型好，因为这些类型提供输入错误检查以及特殊的操作符和函数。
- INET：用于存储 IPv4 和 IPv6 网络地址，使用无类别域间路由（CIDR）表示法。它只存储网络地址和子网掩码，不存储主机部分。
- CIDR：用于存储 IPv4 和 IPv6 主机地址以及网络地址。它既可以存储单个 IP 地址，也可以存储带有子网掩码的 IP 地址和网络。
- MACADDR：用于存储媒体访问控制（MAC）地址，通常是 48 位的以太网地址。
- MACADDR8：用于存储扩展的唯一标识符（EUI-64）格式的 MAC 地址，这通常用于 IPv6 的链路本地地址。（在 PostgreSQL 10 及以后版本中引入）

名字	存储尺寸	描述
cidr	7或19字节	IPv4和IPv6网络
inet	7或19字节	IPv4和IPv6主机以及网络
macaddr	6字节	MAC地址
macaddr8	8 bytes	MAC地址（EUI-64格式）

在对inet或者cidr数据类型进行排序的时候，IPv4 地址将总是排在 IPv6 地址前面，包括那些封装或者是映射在 IPv6 地址里的 IPv4 地址，例如 ::10.2.3.4 或者 ::ffff::10.4.3.2。
使用示例：

-- 创建一个新表以演示网络类型
CREATE TABLE network_examples (
    id SERIAL PRIMARY KEY,
    ipv4_cidr cidr,
    ipv6_cidr cidr,
    ipv4_inet inet,
    ipv6_inet inet,
    mac_address macaddr,
    mac_address_eui64 macaddr8
);

-- 插入示例数据
INSERT INTO network_examples (
        ipv4_cidr, 
        ipv6_cidr, 
        ipv4_inet, 
        ipv6_inet, 
        mac_address, 
        mac_address_eui64
) VALUES (
        '192.168.1.0/24', 
        '2001:db8::/32', 
        '192.168.1.5', 
        '2001:db8::1', 
        '08:00:2b:01:02:03', 
        '00:1A:2B:3C:4D:5E:00:1A'
);

-- 查询并显示插入的数据
SELECT * FROM network_examples;

/* Output：
 id |   ipv4_cidr    |   ipv6_cidr   |  ipv4_inet  |  ipv6_inet  |    mac_address    |    mac_address_eui64    
----+----------------+---------------+-------------+-------------+-------------------+-------------------------
  1 | 192.168.1.0/24 | 2001:db8::/32 | 192.168.1.5 | 2001:db8::1 | 08:00:2b:01:02:03 | 00:1a:2b:3c:4d:5e:00:1a
(1 row)
*/

几何类型

几何数据类型表示二维的空间物体。
PostgreSQL 有一系列丰富的函数和操作符可用来进行各种几何操作，如缩放、平移、旋转和计算相交等。

名字	存储尺寸	表示	描述
point	16字节	平面上的点	(x,y)
line	32字节	无限长的线	{A,B,C}
lseg	32字节	有限线段	((x1,y1),(x2,y2))
box	32字节	矩形框	((x1,y1),(x2,y2))
path	16+16n字节	封闭路径（类似于多边形）	((x1,y1),…)
path	16+16n字节	开放路径	[(x1,y1),…]
polygon	40+16n字节	多边形（类似于封闭路径）	((x1,y1),…)
circle	24字节	圆	<(x,y),r>（中心点和半径）

点

点是几何类型的基本二维构造块。用下面的语法描述point类型的值：(x , y)，其中x和y分别是坐标，都是浮点数。

线

线由线性方程Ax + By + C = 0 表示，其中A和B都不为零。类型line 的值采用以下形式输入和输出：{A, B, C}。
另外，还可以用下列任一形式输入：其中(x1,y1)和(x2,y2)是线上不同的两点。

[ ( x1 , y1 ) , ( x2 , y2 ) ]

线段

线段用一对线段的端点来表示。lseg类型的值用下面的语法声明：其中(x1,y1)和(x2,y2)是线段的端点。

[ ( x1 , y1 ) , ( x2 , y2 ) ]

方框

方框用其对角的点对表示。box类型的值使用下面的语法指定：其中(x1,y1)和(x2,y2)是方框的对角点。

( ( x1 , y1 ) , ( x2 , y2 ) )

在输入时可以提供任意两个对角，但是值将根据需要被按顺序记录为右上角和左下角。

路径

路径由一系列连接的点组成。路径可能是开放的，也就是认为列表中第一个点和最后一个点没有被连接起来；也可能是封闭的，这时认为第一个和最后一个点被连接起来。
path类型的值用下面的语法声明：

[ ( x1 , y1 ) , ... , ( xn , yn ) ]

其中的点是组成路径的线段的端点。方括弧（[]）表示一个开放的路径，圆括弧（()）表示一个封闭的路径。如第三种到第五种语法所示，当最外面的圆括号被忽略时，路径将被假定为封闭。

多边形

多边形由一系列点代表（多边形的顶点）。多边形和封闭路径很像，但是存储方式不一样而且有自己的一套支持例程。
polygon类型的值用下列语法声明：其中的点是组成多边形边界的线段的端点。

( ( x1 , y1 ) , ... , ( xn , yn ) )

圆

圆由一个圆心和一个半径代表。circle类型的值用下面的语法指定：其中(x,y)是圆心，而r是圆的半径。

< ( x , y ) , r >

示例

创建 geometric_types 表：

CREATE TABLE geometric_types (
    id SERIAL PRIMARY KEY,
    point_value point,
    line_value line,
    lseg_value lseg,
    box_value box,
    closed_path_value path,
    open_path_value path,
    polygon_value polygon,
    circle_value circle
);

INSERT INTO geometric_types (
    point_value,
    line_value,
    lseg_value,
    box_value,
    closed_path_value,
    open_path_value,
    polygon_value,
    circle_value
) VALUES (
    '(10, 20)',  -- 点
    '{2,1,1}',    -- 无限长的线
    '[(0,0),(10,10)]',  -- 有限线段
    '((0,0),(10,10))',   -- 矩形框
    '((0,0),(10,0),(10,10),(0,10),(0,0))',  -- 封闭路径
    '[(0,0),(10,10)]',  -- 开放路径
    '((0,0),(10,0),(10,10),(0,10),(0,0))',  -- 多边形
    '<(5,5),10>'       -- 圆
);

查询数据：

SELECT * FROM geometric_types;

/*
id | point_value | line_value |   lseg_value    |   box_value   |          closed_path_value          | open_path_value |            polygon_value            | circle_value 
----+-------------+------------+-----------------+---------------+-------------------------------------+-----------------+-------------------------------------+--------------
  1 | (10,20)     | {2,1,1}    | [(0,0),(10,10)] | (10,10),(0,0) | ((0,0),(10,0),(10,10),(0,10),(0,0)) | [(0,0),(10,10)] | ((0,0),(10,0),(10,10),(0,10),(0,0)) | <(5,5),10>
(1 row)
*/

位串类型

位串就是一串 1 和 0 的串。它们可以用于存储和可视化位掩码。我们有两种类型的 SQL 位类型：bit(n) 和 bit varying(n)，其中n是一个正整数。
bit类型的数据必须准确匹配长度n; 试图存储短些或者长一些的位串都是错误的。bit varying数据是最长n的变长类型，更长的串会被拒绝。写一个没有长度的bit等效于 bit(1)，没有长度的bit varying意味着没有长度限制。
使用示例：

CREATE TABLE test (
    a BIT(3),        -- 固定位数 3 bit
    b BIT VARYING(5) -- 边长位数，最大 5 bit
);

INSERT INTO test VALUES (B'101', B'00');
-- ERROR:  bit string length 2 does not match type bit(3)
-- B'10' 有问题
INSERT INTO test VALUES (B'10', B'101');

INSERT INTO test VALUES (B'10'::bit(3), B'101');

SELECT * FROM test;

/* Output：
  a  |  b  
-----+-----
 101 | 00
 100 | 101
(2 rows)
*/

位串函数和运算符

位串运算符。

-- bit || bit → bit
SELECT B'10001' || B'011'; -- 10001011

-- bit & bit → bit 按位 AND（输入的长度必须相等）
SELECT B'10001' & B'01101'; -- 00001

-- bit | bit → bit 按位或（输入必须具有相同的长度）
SELECT B'10001' | B'01101'; -- 11101

-- bit # bit → bit 按位异或（输入必须具有相同的长度）
SELECT B'10001' # B'01101'; -- 11100

-- ~ bit → bit
SELECT ~ B'10001'; -- 01110

-- bit << integer → bit 按位左移（保留字符串长度）
SELECT B'10001' << 3; -- 01000

-- bit >> integer → bit 按位右移（保留字符串长度）
SELECT B'10001' >> 2; -- 00100

位串函数。

-- bit_count ( bit ) → bigint 返回位串中设置的位数（也称为“popcount”）
SELECT bit_count(B'10111'); -- 4

-- bit_length ( bit ) → integer 返回位串中的位数
SELECT bit_length(B'10111'); -- 5

-- length ( bit ) → integer 返回位串中的位数
SELECT length(B'10111'); -- 5

-- octet_length ( bit ) → integer 返回位串中的字节数
SELECT octet_length(B'1011111011'); -- 2

-- overlay ( bits bit PLACING newsubstring bit FROM start integer [ FOR count integer ] ) → bit
-- 将 bits 中从第 start 位开始并扩展到 count 位的子字符串替换为 newsubstring。
-- 如果省略 count，则默认为 newsubstring 的长度。
SELECT overlay(B'01010101010101010' placing B'11111' from 2 for 3); -- 0111110101010101010
-- 01  010101010101010
-- 0111110101010101010

-- position ( substring bit IN bits bit ) → integer
-- 返回 bits 内指定 substring 的第一个起始索引，如果不存在则返回零
SELECT position(B'010' in B'000001101011'); -- 8
-- 000001101011
--        010

-- substring ( bits bit [ FROM start integer ] [ FOR count integer ] ) → bit
-- 提取 bits 的子字符串，如果指定，则从第 start 位开始，并在 count 位之后停止（如果指定）。
-- 至少提供 start 和 count 之一。
SELECT substring(B'110010111111' from 3 for 2); -- 00
-- 110010111111
--   00

-- get_bit ( bits bit , n integer ) → integer
-- 从位串中提取 n 第位；第一个（最左边）位是位 0(下标从0开始)。
SELECT get_bit(B'101010101010101010', 6); -- 1
-- 101010101010101010
--       1

-- set_bit ( bits bit , n integer , newvalue integer ) → bit
-- 将位串中的 n 第 1 位设置为 newvalue ；第一个（最左边）位是位 0
SELECT set_bit(B'', 7, 0); -- 101010001010101010
-- 101010101010101010
-- 101010001010101010

类型转换。

SELECT 44::bit(10);             -- 0000101100
SELECT 44::bit(3);              -- 100
SELECT cast(-44 as bit(12));    -- 111111010100
SELECT '1110'::bit(4)::integer; -- 14

JSON

JSON 是一种通用的、轻量级的数据传输格式，它可以表示复杂的、结构化的数据，但同时也易于阅读和编写。
PostgreSQL 从 9.2 版本开始支持原生 JSON 数据类型，并且提供了许多用于操作 JSON 数据的函数和运算符。
JSON 对象是一个键值对的组合。键是字符串类型的文本，值可以是上面定义的几种类型的任何一种。
- 对象、数组、字符串、数字、true、false、null
PostgreSQL 支持两种 JSON 类型：JSON 和 JSONB。他们的处理效率不同。JSON 按文本保存输入数据，保留原始数据中的空格，重复键。JSONB 则按照二进制保存输入数据，他会删除没必要的空格以及重复的键。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快。但在操作上，二者是没有区别的。
要将一个字符串的 JSON 文本转为 JSON 类型的值，您可以使用如下两种方法：
- 使用 JSON() 构造函数将 JSON 文本转为 JSON 类型的值：json('[1, 2, 3]')
- 使用类型转换将 JSON 文本转为 JSON 类型的值：'{"a": [1, 2, 3]}'::json
PostgreSQL 提供了几个用于 JSON 数据的操作符，如下：(json和jsonb都适用)
- ->: 获取 JSON 数组的元素或者 JSON 对象中的字段，返回值为 JSON 类型的值。
- -»: 获取 JSON 数组的元素或者 JSON 对象中的字段，返回值为文本。
- #>: 获取指定路径的值，返回值为 JSON 类型的值。
- #»: 获取指定路径的值，返回值为文本。

JSON

使用示例：JSON 类型

CREATE TABLE my_schema.login_logs (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  detail JSON NOT NULL
);

INSERT INTO my_schema.login_logs (detail)
VALUES
  ('{ "name": "Tom", "address": {"ip": "192.168.1.23","country": "US"}}'),
  ('{ "name": "Tim", "address": {"ip": "192.168.1.24","country": "US"}}'),
  ('{ "name": "Jim", "address": {"ip": "192.168.1.25","country": "UK"}}');

查询JSON数据。

postgres=> SELECT * FROM my_schema.login_logs;
 id |                               detail                                
----+---------------------------------------------------------------------
  1 | { "name": "Tom", "address": {"ip": "192.168.1.23","country": "US"}}
  2 | { "name": "Tim", "address": {"ip": "192.168.1.24","country": "US"}}
  3 | { "name": "Jim", "address": {"ip": "192.168.1.25","country": "UK"}}
(3 rows)

如果您想显示 JSON 对象内部的字段，请使用 -> 和 -» 操作符。-> 返回的值是 JSON 类型，-» 返回的值是文本类型。

postgres=> 
SELECT
  detail -> 'name' AS name_json,
  detail ->> 'name' AS name_text
FROM
  login_logs;

 name_json | name_text 
-----------+-----------
 "Tom"     | Tom
 "Tim"     | Tim
 "Jim"     | Jim
(3 rows)

获取所有登陆用的国家和 IP，请使用下面的语句：

postgres=> 
SELECT
  detail -> 'address' ->> 'country' as country,
  detail -> 'address' ->> 'ip' as ip
FROM login_logs;

 country |      ip      
---------+--------------
 US      | 192.168.1.23
 US      | 192.168.1.24
 UK      | 192.168.1.25
(3 rows)

您可以在 WHERE 子句中使用 JSON 运算符来过滤返回的行。

postgres=> 
SELECT
  detail ->> 'name' AS name,
  detail -> 'address' ->> 'country' AS country
FROM
  login_logs
WHERE
  detail -> 'address' ->> 'country' = 'US';

 name | country 
------+---------
 Tom  | US
 Tim  | US
(2 rows)

JSONB

使用示例：JSONB

create table if not exists name_age (
    info jsonb
);

INSERT INTO name_age VALUES ('{"id":1,"name":"张三", "age":12}');

-- 在json查询结果里插入新的key值gender
SELECT info||'{"gender":"男"}'::jsonb from name_age where (info->>'id')::int4 = 1;

/*
                       ?column?                       
------------------------------------------------------
 {"id": 1, "age": 12, "name": "张三", "gender": "男"}
(1 row)
*/

Postgres里的查询需要用到查询符。比如说，我们要查询id为1的数据，语句如下：
- @> 这个查询符，表明info当前这条记录里的顶层json中有没有id为1的key-value对；有的话则满足条件。

SELECT info from name_age WHERE info @> '{"id":1}'::jsonb;

/*
                 info                 
--------------------------------------
 {"id": 1, "age": 12, "name": "张三"}
(1 row)
*/

查询 age>16 的记录，并且只显示 name。

SELECT info -> 'name' FROM name_age WHERE (info -> 'age')::int4 > 10;

/*
 ?column? 
----------
 "张三"
(1 row)
*/

将 age 从 12 改为 22 ，SQL语句：

SELECT info ||'{"age":22}'::jsonb from name_age where (info->>'id')::int4 = 1;

/*
               ?column?               
--------------------------------------
 {"id": 1, "age": 22, "name": "张三"}
(1 row)
*/

删除age这个key，直接用操作符 - 即可。

SELECT info - 'age' FROM name_age WHERE (info -> 'age')::int4 > 10;

/*
         ?column?          
---------------------------
 {"id": 1, "name": "张三"}
(1 row)
*/

数字类型#

整数类型#

任意精度数字#

浮点类型#

序列类型#

货币类型#

布尔类型#

UUID类型#

字符类型#

CHAR#

VARCHAR#

TEXT#

日期/时间类型#

DATE#

TIMESTAMP#

TIME#

枚举类型#

网络地址类型#

几何类型#

点#

线#

线段#

方框#

路径#

多边形#

圆#

示例#

位串类型#

JSON#

JSON#

JSONB#

数字类型

整数类型

任意精度数字

浮点类型

序列类型

货币类型

布尔类型

UUID类型

字符类型

CHAR

VARCHAR

TEXT

日期/时间类型

DATE

TIMESTAMP

TIME

枚举类型

网络地址类型

几何类型

点

线

线段

方框

路径

多边形

圆

示例

位串类型

JSON

JSON

JSONB