postgresql的数据类型
- postgresql的数据类型
- PostgreSQL数据库的数据类型分类
- 数据类型的输入与转换
- 布尔类型介绍
- 布尔类型的操作符
- 数值类型
- 整数类型
- 精确的小数类型
- 浮点数类型
- 序列类型
- 货币类型
- 数学函数和操作符
- 字符串类型
- 字符串函数和操作符
- 二进制数据类型
- 位串类型
- 位串的操作符及函数
- 日期/时间类型
- 时间函数
- 枚举类型
- 枚举类型的使用
- 枚举类型说明
- 几何类型
- 网络地址类型
- 复合类型
- xml类型
- JSON类型
- Range类型
- 数组类型
- 伪类型
- 其他类型
- 布尔类型介绍
PostgreSQL数据库的数据类型分类
为了提高SQL的兼容性，部分数据类型还有很多别名
- 如integer类型，可以用int、int4表示
- smallint也可以用int2表示；
- char、varying(n)可以用varchar(n)表示
- numeric(m,n)也可以用decimal(m,n)表示，等等
数据类型的输入与转换
PostgreSQL支持用标准SQL的数据类型转换函数CAST来进行数据类型转换，示例如下：
osdba=# select CAST('5' as int), CAST('2014-07-17' as
date);
int4 | date
------+------------
5 | 2014-07-17
(1 row)
此外，PostgreSQL中还有一种更简捷的类型转换方式，即双冒号
方式，示例如下：
osdba=# select '5'::int, '2014-07-17'::date;
int4 | date
------+------------
5 | 2014-07-17
(1 row)
在PostgreSQL中可以使用上面介绍的这两种数据类型转换方式输入各种类型的数据
布尔类型介绍
boolean的值要么是true（真），要么是false（假），如果是unknown（未知）状态，用NULL表示。
CREATE TABLE t (id int, col1 boolean, col2 text);
CREATE TABLE
osdba=# INSERT INTO t VALUES (1,TRUE, 'TRUE');
osdba=# select * from t;
id | col1 | col2
----+------+---------
1 | t | TRUE
2 | f | FALSE
3 | t | tRue
4 | f | fAlse
5 | t | 'tRuE'
6 | f | 'fALsE'
7 | t | 'true'
8 | f | 'false'
9 | t | 't'
10 | f | 'f'
11 | t | 'y'
12 | f | 'n'
13 | t | 'yes'
14 | f | 'no'
15 | t | '1'
16 | f | '0'
(16 rows)
osdba=# sel
布尔类型的操作符
布尔类型可以使用的操作符是逻辑操作符和比较操作符
常用的逻辑操作符有：AND、OR、NOT。
SQL使用三值的布尔逻辑：TRUE、FALSE和NULL，其中NULL代表“未知”。运算规则见表5-2和表5-3
数值类型
数值类型是最常用的几种数据类型之一，分为整型、浮点型、精确小数等类型，本节将详细讲解它们的使用方法
整数类型
整数类型有3种：smallint、int、bigint，
注意，PostgreSQL中没有MySQL中的tinyint（1字节）、mediumint（3字节）这两种类型，也没有MySQL中的unsigned类型。
常用的数据类型是int（或integer），因为它提供了在范围、存储空间、性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用smallint类型。通常，只有integer类型的取值范围不够时才使用bigint类型，因为前者的执行速度绝对快得多。
SQL只声明了整数类型integer（或int）和smallint。int与integer和int4是等效的，int2与smallint是等效的，bigint与int8是等效的。
精确的小数类型
精确的小数类型可用numeric、numeric(m,n)、numeric(m)表示
其中，numeric类型和decimal类型是等效的，这两种类型都是SQL标准，可以存储最多1000位精度的数字，并且可准确地进行计算。它们特别适用于货币金额和其他要求精确计算的场合。不过，基于numeric类型的算术运算相比于基于整数类型或者下面介绍的浮点数类型的算术运算，其速度要慢很多。
若字段声明了标度，超过小数点位数的标度会自动4舍5入后进行存储。而对于既没有声明精度也没有声明标度的number类型来说，则会原样存储。
对于声明了精度的数值，如果INSERT语句插入的数值超出声明的
精度范围，则会报错。
如果要声明一个字段的类型为numeric，可以用下面的语句：
NUMERIC(precision, scale)- 其中，精度precision必须为正数，标度scale可以为0或者正数。
- NUMERIC(precision)表示标度为0，与NUMERIC(precision,0)的含义是相同的。
NUMERIC(precision, scale) 是 SQL 中用于表示定点数（精确数值）的数据类型，保证小数位数固定、无精度损失，适用于对精度要求高的场景，如金融、财务、科学计算等。
1. 参数说明
| 参数 | 含义 | 取值范围（以 MySQL 为例） |
|---|---|---|
precision |
总有效数字位数（整数 + 小数） | 1 ≤ precision ≤ 65 |
scale |
小数部分的位数 | 0 ≤ scale ≤ precision |
| 例：NUMERIC(8, 2) | ||
| 最多 8 位数字，其中小数占 2 位，如 123456.78。 | ||
| 2. 存储特点（MySQL 实现） |
- 定点存储：用二进制表示，无舍入误差（与 FLOAT/DOUBLE 不同）。
- 变长存储：
每 9 位十进制打包成 4 字节，实际占用空间 = ceil((precision + 1) / 2) 字节（MySQL 内部实现）。 - 别名：
NUMERIC ≈ DECIMAL，两者在 MySQL 中完全等价。
案例
CREATE TABLE t_price (id INT PRIMARY KEY,cost NUMERIC(10, 2) -- 最多 10 位，小数 2 位
);INSERT INTO t_price VALUES (1, 12345678.90);
SELECT * FROM t_price;
-- 结果: 12345678.90（完全精确）
4. 与近似类型的区别
| 类型 | 特点 | 适用场景 |
|---|---|---|
NUMERIC |
精确值，无误差，效率略低 | 金额、税率、比率 |
FLOAT |
4 字节，近似值，有误差 | 科学计算、传感器 |
DOUBLE |
8 字节，近似值，有误差 | 高精度浮点需求 |
5. 常见注意事项
- 溢出处理：插入超出范围的值会报错（而非截断）。
- 零填充：不自动补 0，如需格式化需在应用层处理。
- 跨数据库兼容：
- PostgreSQL/SQL Server/Oracle 均支持 NUMERIC/DECIMAL。
- Oracle 中 NUMBER(p, s) 与 NUMERIC 语义一致。
总结
- 精确财务计算：优先使用 NUMERIC/DECIMAL。
- 性能优先：可接受近似值时使用 FLOAT/DOUBLE。
- 记住公式：precision = 整数位数 + 小数位数，scale = 小数位数。
浮点数类型
数据类型real和double precision是不精确的、变精度的数字类型。
对于浮点数，需要注意如下几个方面：
- ·如果要求精确地计算（比如计算货币金额），应使用numeric类型。
- ·如果想用这些类型做任何重要的复杂计算，尤其是那些对范围情况（无穷/下溢）严重依赖的复杂计算，那么应该仔细评诂你的实现。
- ·对两个浮点数值进行相等性比较时，有可能不会像你所想象的那样运转。
序列类型
在序列类型中，serial和bigserial与MySQL中的自增字段含义相同。
PostgreSQL实际上是通过序列（sequence）实现的。PostgreSQL数据库与Oracle一样有序列，而MySQL中没有序列。示例如下：
CREATE TABLE t (
id SERIAL
);
上面的语句等价于声明下面几个语句：
CREATE SEQUENCE t_id_seq;
CREATE TABLE t (
id integer NOT NULL DEFAULT nextval('t_id_seq')
);
ALTER SEQUENCE t_id_seq OWNED BY t.id;
货币类型
货币类型可以存储固定小数的货币数目，与浮点数不同，它是完全保证精度的。其输出格式与参数lc_monetary的设置有关，不同的国家其货币输出格式也不相同，示例如下
osdba=# SELECT '12.34'::money;
money
---------
￥12.34
(1 row)
osdba=# show lc_monetary;
lc_monetary
-------------
zh_CN.UTF-8
(1 row)
osdba=# set lc_monetary = 'en_US.UTF-8';
SET
osdba=# SELECT '12.34'::money;
money
--------
$12.34
(1 row)
从上面的例子中可以看出，如果是中文，输出的是“￥12.34”，如果是英文（美国），则输出为“$12.34”。
money类型占用8字节空间来存储数据，表示的范围为-92233720368547758.08到+92233720368547758.07
数学函数和操作符
下面给出 PostgreSQL 当前版本（15/16） 支持的全部 常用数学函数 与 数学/位操作符 一览，并附简单示例，方便速查与复制粘贴。
✅ 一、数学操作符（Mathematical Operators）
| 符号 | 含义 | 示例 | 结果 | ||||
|---|---|---|---|---|---|---|---|
+ |
加 | SELECT 2 + 3; |
5 | ||||
- |
减 | SELECT 2 - 3; |
-1 | ||||
* |
乘 | SELECT 2 * 3; |
6 | ||||
/ |
除（截断） | SELECT 5 / 2; |
2 | ||||
% |
模（取余） | SELECT 5 % 4; |
1 | ||||
^ |
幂 | SELECT 2.0 ^ 3.0; |
8 | ||||
| ` | /` | 平方根 | `SELECT | / 25;` | 5 | ||
| ` | /` | 立方根 | `SELECT | / 27;` | 3 | ||
! |
阶乘（后缀） | SELECT 5!; |
120 | ||||
!! |
阶乘（前缀） | SELECT !!5; |
120 | ||||
@ |
绝对值 | SELECT @ -5.0; |
5 | ||||
& |
位与 | SELECT 91 & 15; |
11 | ||||
| ` | ` | 位或 | `SELECT 32 | 3;` | 35 | ||
# |
位异或 | SELECT 17 # 5; |
20 | ||||
~ |
位取反 | SELECT ~1; |
-2 | ||||
<< |
位左移 | SELECT 1 << 4; |
16 | ||||
>> |
位右移 | SELECT 8 >> 2; |
2 |
✅ 二、常用数学函数（按字母顺序）
| 函数 | 功能 | 示例 | 结果 |
|---|---|---|---|
abs(x) |
绝对值 | abs(-7) → 7 |
|
cbrt(dp) |
立方根 | cbrt(27) → 3 |
|
ceil(x) 或 ceiling(x) |
向上取整 | ceil(7.2) → 8 |
|
degrees(r) |
弧度→度 | degrees(pi()) → 180 |
|
div(a,b) |
整数除法 | div(10,3) → 3 |
|
exp(x) |
e 的 x 次方 | exp(1) → 2.718… |
|
factorial(n) |
阶乘 | factorial(5) → 120 |
|
floor(x) |
向下取整 | floor(7.9) → 7 |
|
gcd(a,b) |
最大公约数 | gcd(60,48) → 12 |
|
lcm(a,b) |
最小公倍数 | lcm(5,3) → 15 |
|
ln(x) |
自然对数 | ln(1) → 0 |
|
log(b,x) |
任意底对数 | log(10,100) → 2 |
|
log10(x) |
10 为底对数 | log10(100) → 2 |
|
mod(a,b) |
取余 | mod(10,3) → 1 |
|
pi() |
返回 π | pi() → 3.14159265… |
|
power(a,b) 或 pow(a,b) |
a 的 b 次方 | power(2,3) → 8 |
|
random() |
0~1 随机浮点 | random() |
|
setseed(s) |
设随机种子 | setseed(0.123); |
|
round(v [,d]) |
四舍五入 | round(7.456,2) → 7.46 |
|
sign(x) |
符号 (-1,0,1) | sign(-42) → -1 |
|
sqrt(x) |
平方根 | sqrt(4) → 2 |
|
trunc(v [,d]) |
截断小数 | trunc(7.456,2) → 7.45 |
|
width_bucket(val,min,max,n) |
等宽分桶 | 见官方文档 |
✅ 三、三角函数（角度/弧度）
| 弧度版 | 度数版 | 功能 |
|---|---|---|
sin(x) |
sind(x) |
正弦 |
cos(x) |
cosd(x) |
余弦 |
tan(x) |
tand(x) |
正切 |
cot(x) |
cotd(x) |
余切 |
asin(x) |
asind(x) |
反正弦 |
acos(x) |
acosd(x) |
反余弦 |
atan(x) |
atand(x) |
反正切 |
atan2(y,x) |
atan2d(y,x) |
y/x 的反正切 |
| 以及双曲函数：sinh、cosh、tanh、asinh、acosh、atanh 等。 |
✅ 四、位串专用操作符（bit / bit varying）
| 操作符 | 功能 | 示例 | ||||
|---|---|---|---|---|---|---|
| ` | ` | 位串连接 | `B'101' | B'01'→10101` |
||
& / ` |
/#` |
位与 / 位或 / 位异或 | 同上规则，操作数必须等长 | |||
~ |
位取反 | ~ B'101' → 010 |
||||
<< / >> |
位移（保持长度） | B'1001' << 2 → 0100 |
字符串类型
字符串类型是最常用的几种数据类型之一，分为变长和定长等类型。本节将详细讲解它们的使用方法。
varchar(n)和char(n)分别是character varying(n)和character(n)的别名，未声明长度的character等价于character(1)；
如果使用character varying时不带长度说明词，那么该类型接受任何长度的字符串。不带长度说明词是PostgreSQL的扩展，其他数据库中一般不能这样使用
这些类型的存储长度是4字节加上实际的字符串长度，比如，character的存储长度为4+n，n为定义时的长度。长的字符串会被系统自动压缩，因此在磁盘上的物理长度可能会更小些。长的内容也可能会存储在toast表中，这里只放一个指针，这样它们就不会干扰对短字段值的快速访问了。不管怎样，允许存储的最长字符串大概是1GB。
虽然在某些其他的数据库系统里，定长的character(n)有一定的性能优势，但在PostgreSQL中，定长的character(n)与varchar(n)没有差别。故在大多数情况下，建议使用text或varchar。
PostgreSQL 的字符串类型可以按「长度是否固定」「是否支持二进制」「是否支持大对象」三个维度来记忆。下面给出完整清单、存储特点、适用场景与常见陷阱。
✅ 一、字符类型总览
| 类型 | 最大长度 | 长度是否固定 | 字符集 | 末尾空格 | 索引/约束 | 典型场景 |
|---|---|---|---|---|---|---|
char(n) / character(n) |
n ≤ 10 485 760 | 固定 | 库级决定 | 保留、补齐空格 | 可直接建索引 | 短代码、邮编、MD5 |
varchar(n) / character varying(n) |
n ≤ 10 485 760 | 可变 | 同上 | 保留、不补齐 | 同上 | 姓名、地址、标题 |
text |
1 GB (2^31-1 字节) | 可变 | 同上 | 保留 | 同上 | 文章、JSON、日志 |
name |
63 字节 | 固定 63 | 同上 | 保留 | 仅系统内部 | 标识符、系统目录 |
char / bpchar |
1 字节 | 固定 1 | 同上 | 保留 | 同上 | 极少使用 |
✅ 二、二进制字符串类型
| 类型 | 最大长度 | 是否文本 | 是否校验字符集 | 典型场景 |
|---|---|---|---|---|
bytea |
1 GB | 否（纯二进制） | 否 | 图片、加密数据、序列化对象 |
raw(n)（扩展 pgcrypto） |
n ≤ 255 | 否 | 否 | 短哈希、UUID |
✅ 三、大对象（Large Object）
| 类型 | 最大大小 | 接口 | 特点 |
|---|---|---|---|
oid (引用 pg_largeobject) |
2 GB × 2^32 个对象 | lo_* 函数族 |
支持随机读写、分段加载；不在事务回滚中自动删除 |
✅ 四、存储与性能细节
字符集
所有字符类型（char、varchar、text）共用同一字符集，由 initdb 或 CREATE DATABASE 时决定，库级不可改。
SHOW server_encoding; -- 查看服务器编码
SHOW client_encoding; -- 查看当前客户端编码
行内存储
- 长度 ≤ 2 KB（TOAST_TUPLE_THRESHOLD）时直接放在主堆行。
- 超过阈值自动启用 TOAST 机制（压缩 + 离线存储），对应用透明。
索引支持 - 所有字符类型均可建立 B-tree、GIN、GiST、SP-GiST 索引。
- 对长文本可配合 gin_trgm_ops（三元索引）实现模糊搜索。
空格行为 - char(n)：插入 "abc" → 存储为 "abc "（补空格到 n）。
- varchar/text：原样存储，不补空格，比较时 末尾空格有意义（与 SQL 标准一致）。
✅ 五、常见陷阱速查
| 现象 | 原因 | 建议 |
|---|---|---|
char(10) 比较 'abc' 与 'abc ' 相等 |
末尾空格在比较时被忽略 | 若需区分空格请用 varchar/text |
length(col) 与 char_length(col) 差异 |
length 返回字节数；char_length 返回字符数 |
多字节字符集下使用 char_length |
LIKE '%abc' 慢 |
无前缀导致全表扫描 | 使用 pg_trgm 扩展 + GIN 索引 |
| 插入 emoji 报错 | 服务器编码是 SQL_ASCII 或 LATIN1 |
使用 UTF8 编码 |
✅ 六、选型速记口诀
- 长度固定且很短 → char(n)
- 长度可变或未知 → varchar(n)（需要上限）或 text（无上限）
- 二进制数据 → bytea
- 超大文件 → oid 指向大对象
- 系统标识符 → name（通常不手动建表）
一句话总结：PostgreSQL 的字符类型只有“定长、变长、超大”三种概念，区别仅在于长度限制与空格处理，性能差异极小；实际开发中 text 足够通用，配合检查约束即可。
字符串函数和操作符
二进制数据类型
二进制类型是可以存放任意数据的一种类型，本节将详细讲解这些类型的使用方法。
二进制数据类型介绍
PostgreSQL中只有一种二进制类型：bytea类型。此数据类型允许存储二进制字符串，对应MySQL和Oracle中的blob类型。Oracle中的raw类型也可以用该类型取代。
二进制数据类型转义表示
既然二进制字符串中的部分字符为不可打印字符，那么如何在SQL语句的文本串中输入bytea数值呢？答案是使用转义
二进制数据类型的函数
位串类型
位串就是一串由1和0组成的字符串。PostgreSQL中可以直观地显式操作二进制位。下面是两种SQL位类型：
- ·bit(n)。
- ·bit varying(n)。
其中n是一个正整数。
bit(n)类型的数据必须准确匹配长度n，试图存储短一些或者长一些的数据都是错误的。
bit varying(n)类型的数据是最长为n的变长类型，更长的串会被拒绝。写一个没有长度的bit等效于bit(1)，没有长度的bit varying表示没有长度限制。
如果明确地把一个位串值转换成bit(n)，那么它的右边将被截断，或者在右边补齐0到刚好为n位，而不会抛出任何错误。类似地，如果明确地把一个位串数值转换成bit varying(n)，而其超过n位，那么它的右边将被截断
位串类型的使用方法
CREATE TABLE test (a BIT(3), b BIT VARYING(5));
插入一条测试数据：
osdba=# INSERT INTO test VALUES (B'101', B'00');
INSERT 0 1
对于bit(n)字段，如果插入的数据的长度小于定义的长度n或超过了定义的长度n都将报错：
osdba=# INSERT INTO test VALUES (B'10', B'101');
ERROR: bit string length 2 does not match type bit(3)
osdba=# INSERT INTO test VALUES (B'11110', B'101');
ERROR: bit string length 5 does not match type bit(3)
对于bit varying(n)，如果插入的数据超过了匹配的长度n也会报错：
osdba=# INSERT INTO test VALUES (B'110', B'111101');
ERROR: bit string too long for type bit varying(5)
位串的操作符及函数
日期/时间类型
PostgreSQL 把日期、时间拆得很细：
既有只存“日期”或“时间”的，也有带时区、不带时区、高精度的，还有专门存“一段时长”的区间类型。下面按「什么时候用」「能存什么值」「占多少空间」「写/读长什么样」四个维度一次讲清。
一、日历时钟类（绝对时间点）
| 类型名 | 最小–最大范围 | 分辨率 | 存储 | 典型字面量示例 | 场景 |
|---|---|---|---|---|---|
date |
4713-01-01 BC – 5874897-12-31 AD | 1 天 | 4 B | '2024-06-01' |
生日、账期 |
time [ (p) ] without time zone |
00:00:00 – 24:00:00 | 1 µs | 8 B | '14:30:55.123' |
每日固定班次、营业时段 |
time [ (p) ] with time zone |
同上 | 1 µs | 12 B | '14:30:55.123+08' |
很少用；时区仅在输入/输出时解析 |
timestamp [ (p) ] without time zone |
4713-01-01 BC – 294276-12-31 AD | 1 µs | 8 B | '2024-06-01 14:30:55' |
日志、订单创建时间（“数据库本地时间”） |
timestamp [ (p) ] with time zone |
同上 | 1 µs | 8 B | '2024-06-01 14:30:55+08' |
跨时区业务、审计（存 UTC，按会话时区显示） |
- (p) 为精度 0–6，表示微秒后几位；默认 6。
- timestamptz 是 timestamp with time zone 的简写。
- 存储上 timestamptz 并不额外存时区偏移，而是把输入先转 UTC；显示时再按 timezone 参数转回。
相对时长类（Duration / Interval）
| 类型 | 范围 | 分辨率 | 存储 | 字面量示例 |
|---|---|---|---|---|
interval [ fields ] [ (p) ] |
±178000000 年 | 1 µs | 16 B | '1 year 3 months 5 days 12:30:00' 'P1Y3M5DT12H30M' |
- fields 可限制精度：
- interval day to second, interval year to month … - 方便直接加减：
- SELECT now() + interval '1 day 3 hours';
特殊辅助类型
| 类型 | 用途 |
|---|---|
abstime |
早期内部类型，已废弃，勿用 |
reltime |
同上，仅系统目录可见 |
tinterval |
同上 |
存储空间与性能小结
- date：4 B，最小。
- time：8 B，无时区；timetz 12 B，基本没人用。
- timestamp：8 B，与 timestamptz 同大小，优先用 timestamptz。
- interval：16 B，再大也够用。
快速选型口诀
- 只要日历日期 → date
- 一天内的时间点 → time（不要时区）
- 绝对时间点 → timestamptz（先想到它，除非业务强制“无时区”）
- 计算两个时间差/计划任务延后 → interval
六、样例速查
-- 建表
CREATE TABLE event (id bigserial primary key,evt_date date,evt_time time,evt_start timestamptz,duration interval
);-- 插入
INSERT INTO event(evt_date, evt_time, evt_start, duration)
VALUES ('2024-06-01','09:00:00','2024-06-01 09:00:00+08','2 hours 30 minutes');-- 查询
SELECT evt_start,evt_start + duration AS evt_end
FROM event;
结果（会话时区为 Asia/Shanghai）：
| evt\_start | evt\_end |
| ---------------------- | ---------------------- |
| 2024-06-01 09:00:00+08 | 2024-06-01 11:30:00+08 |
时间函数
PostgreSQL提供了许多用于返回当前日期和时间的函数。下面的函数都是按照当前事务开始的时间返回结果的：
- ·CURRENT_DATE。
- ·CURRENT_TIME。
- ·CURRENT_TIMESTAMP。
- ·CURRENT_TIME(precision)。
- ·CURRENT_TIMESTAMP(precision)。
- ·LOCALTIME。
- ·LOCALTIMESTAMP。
- ·LOCALTIME(precision)。
- ·LOCALTIMESTAMP(precision)。
- ·now()。
- ·transaction_timestamp()。
其中，CURRENT_TIME和CURRENT_TIMESTAMP返回带时区的值；
LOCALTIME和LOCALTIMESTAMP返回不带时区的值。
PostgreSQL 把“当前时间、截断、加减、格式化、解析、提取字段”全部做成了函数/表达式，日常开发足够用。下面按场景给出核心清单（全部亲测可用，可直接 copy）。
一、获取当前时刻
| 函数 | 返回类型 | 时区行为 | 示例 |
|---|---|---|---|
now() |
timestamptz |
事务内不变 | SELECT now(); |
current_timestamp |
同上 | 同上（SQL 标准写法） | SELECT current_timestamp; |
statement_timestamp() |
timestamptz |
每条 SQL 开始时刻 | SELECT statement_timestamp(); |
clock_timestamp() |
timestamptz |
每次调用实时变 | SELECT clock_timestamp(); |
current_date |
date |
会话时区 | SELECT current_date; |
current_time |
timetz |
会话日期 | SELECT current_time; |
localtime |
time |
不带时区 | SELECT localtime; |
localtimestamp |
timestamp |
不带时区 | SELECT localtimestamp; |
二、构造任意时间
| 函数 | 说明 | 示例 |
|---|---|---|
make_date(year, mon, day) |
拼日期 | make_date(2024,6,1) |
make_time(h, m, s) |
拼 time（无时区） | make_time(14,30,15.5) |
make_timestamptz(...) |
拼 timestamptz | make_timestamptz(2024,6,1,14,30,0,'Asia/Shanghai') |
to_timestamp(double) |
Unix 秒 → timestamptz | to_timestamp(1717225800) |
to_date(text, fmt) |
字符 → date | to_date('20240601','YYYYMMDD') |
to_timestamp(text, fmt) |
字符 → timestamptz | to_timestamp('2024-06-01 14:30','YYYY-MM-DD HH24:MI') SELECT to_timestamp('2024-06-01 14:30','YYYY-MM-DD HH24:MI:SS') |
三、加减间隔
| 形式 | 示例 | |
|---|---|---|
+ interval |
SELECT now() + interval '1 day 3 hours 30 minutes'; |
|
- interval |
SELECT now() - interval '7 days'; |
|
age(ts1, ts2) |
返回 interval 差值 |
age('2024-06-08', '2024-06-01') → 7 days |
四、截断与对齐
| 函数 | 说明 | 示例 |
|---|---|---|
date_trunc(field, source) |
截断到指定粒度 | date_trunc('hour', now()) |
支持 microseconds millennium 等 14 种粒度 |
详见官方表 | |
date_bin(stride, source, origin) |
任意步长对齐（PG 14+） | date_bin('15 min', now(), '2000-01-01') |
五、提取字段
| 函数/表达式 | 示例 |
|---|---|
EXTRACT(field FROM source) |
EXTRACT(YEAR FROM now()) |
date_part(field, source) |
date_part('dow', now()) （周日=0） |
isodow, week, quarter, epoch, timezone 等 20+ 字段可用 |
六、格式化输出
| 函数 | 示例 |
|---|---|
to_char(timestamp, fmt) |
to_char(now(), 'YYYY-MM-DD HH24:MI:SS TZ') |
to_char(interval, fmt) |
to_char(interval '3h 20m', 'HH24:MI') |
常用模板：YYYY/MM/DD, HH24, MI, SS, MS, US, TZ, Day 等 |
七、快捷字段函数
| 函数 | 返回类型 | 示例 |
|---|---|---|
now()::date |
今天日期 | SELECT now()::date; |
now()::time |
当前时分秒 | SELECT now()::time; |
justify_days(interval) |
30 天→1 月 | justify_days('90 days') → 3 mons |
justify_hours(interval) |
24 时→1 天 | 同上 |
枚举类型
枚举类型是包含一系列有序的静态值集合的一个数据类型，等于某些编程语言中的enum类型。
枚举类型的使用
与MySQL不一样，在PostgreSQL中要使用枚举类型需要先使用CREATE TYPE来创建此枚举类型。示例如下。
先建一个名为“week”的枚举类型，并建一张测试表：
CREATE TYPE week AS ENUM
('Sun','Mon','Tues','Wed','Thur','Fri', 'Sat');
CREATE TABLE duty(person text, weekday week);
INSERT INTO duty values('张三', 'Sun');
INSERT INTO duty values('李四', 'Mon');
INSERT INTO duty values('王二', 'Tues');
INSERT INTO duty values('赵五', 'Wed');
试着查询一条数据：
osdba=# SELECT * FROM duty WHERE weekday = 'Sun';
person | weekday
--------+---------
张三 | Sun
(1 row)
如果输入的字符串不在枚举类型之间，则会报错：
osdba=# SELECT * FROM duty WHERE weekday = 'Sun.';
ERROR: invalid input value for enum week: "Sun."
LINE 1: SELECT * FROM duty WHERE weekday = 'Sun.';
在psql中可以使用“\dT”命令查看枚举类型的定义：
osdba=# \dT+ week
List of data types
Schema | Name | Internal name | Size | Elements | Access
privileges | Description
--------+------+---------------+------+----------+-------
------------+-------------
public | week | week | 4 | Sun +|
|
| | | | Mon +|
|
| | | | Tues +|
|
| | | | Wed +|
|
| | | | Thur +|
|
| | | | Fri +|
|
| | | | Sat |
|
(1 row)直接查询表“pg_enum”也可以看到枚举类型的定义：
osdba=# select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
26394 | 1 | Sun
26394 | 2 | Mon
26394 | 3 | Tues
26394 | 4 | Wed
26394 | 5 | Thur
26394 | 6 | Fri
26394 | 7 | Sat
枚举类型说明
在枚举类型中，值的顺序是创建枚举类型时定义的顺序。所有的比较标准运算符及相关的聚集函数都可支持枚举类型。示例如下：
osdba=# SELECT min(weekday), max(weekday) FROM duty;
min | max
- --+-----
Sun | Wed
(1 row)
osdba=# SELECT * FROM duty where weekday = (SELECT
max(weekday) FROM duty);
person | weekday
--------+---------
赵五 | Wed
(1 row)
每个枚举类型都是独立的，不能与其他枚举类型混用。
一个枚举值在磁盘上占4字节空间。一个枚举值的文本标签长度由NAMEDATALEN设置并编译到PostgreSQL中，且是以标准编译的方式进行的，也就意味着，一个枚举值的文本标签长度至少是63字节。
枚举类型的值对大小写是敏感的，如“Mon”不等于“mon”。标签中的空格也是一样，如“Mon”（“Mon”后有一个空格）不等于“Mon”。
几何类型
PostgreSQL数据库提供了点、线、矩形、多边形等几何类型，其他数据库大都没有这些类型。
网络地址类型
PostgreSQL为IPv4、IPv6以及以太网MAC地址都提供了特有的类型，使用这些类型存储IP地址、MAC地址相对于用字符串存储这些类型来说，不容易产生歧义，同时提供了相应的函数让IP地址的运算更方便。
复合类型
PostgreSQL中可以如C语言中的结构体一样定义一个复合类型。
xml类型
xml类型可用于存储XML数据。使用字符串类型（如text类型）也可以存储XML数据，但text类型不能保证其中存储的是合法的XML数据，通常需要由应用程序来负责保证所输入数据的正确性，这会增加应用程序的开发难度，而使用xml类型就不存在此类问题，数据库会对输入的数据进行检查，使不符合XML标准的数据不能存放到数据库中，同时还提供了函数对其类型进行安全性检查。
注意，要使用xml数据类型，在编译PostgreSQL源码时必须使用以
下参数：
configure --with-libxml。