PostgreSQL之数据类型(一)数字、货币、字符、二进制、日期、枚举、几何类型
数字类型
| 名字 | 存储尺寸 | 描述 | 范围 |
|---|---|---|---|
smallint |
2字节 | 小范围整数 | -2^15 (-32,768) 到 2^15 - 1 (32,767) |
integer |
4字节 | 整数的典型选择 | -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) |
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 |
1.整数类型
类型smallint、integer和bigint存储各种范围的全部是数字的数，也就是没有小数部分的数字。类型int2、int4和int8都是扩展
2.任意精度数字
类型numeric可以存储非常多位的数字。我们特别建议将它用于货币金额和其它要求计算准确的数量。numeric值的计算在可能的情况下会得到准确的结果，例如加法、减法、乘法。不过，numeric类型上的算术运算比整数类型或者下一节描述的浮点数类型要慢很多。
一个numeric的precision（精度）是整个数中有效位的总数，也就是小数点两边的位数。numeric的scale（刻度）是小数部分的数字位数，也就是小数点右边的部分。因此数字 23.5141 的精度为6而刻度为4。可以认为整数的刻度为零。
3.浮点类型
数据类型real和double precision是不精确的、变精度的数字类型。 在所有当前支持的平台上，这些类型是IEEE标准 754 二进制浮点算术（分别对应单精度和双精度）的实现， 一直到下层处理器、操作系统和支持它的编译器。
不准确意味着一些值不能准确地转换成内部格式并且是以近似的形式存储的，因此存储和检索一个值可能出现一些缺失。
4.序数类型
smallserial、serial和bigserial类型不是真正的类型，它们只是为了创建唯一标识符列而存在的方便符号。（类似其它一些数据库中支持的AUTO_INCREMENT属性）
货币类型
money类型存储固定小数精度的货币数字。小数的精度由数据库的lc_monetary设置决定。表中展示的范围假设有两个小数位。可接受的输入格式很多，包括整数和浮点数文字，以及常用的货币格式，如'$1,000.00'。 输出通常是最后一种形式，但和区域相关。
| 名字 | 存储尺寸 | 描述 | 范围 |
|---|---|---|---|
money |
8 bytes | 货币额 | -92233720368547758.08到+92233720368547758.07 |
由于这种数据类型的输出是区域敏感的，因此将money数据装入到一个具有不同lc_monetary设置的数据库是不起作用的。为了避免这种问题，在恢复一个转储到一个新数据库中之前，应确保新数据库的lc_monetary设置和被转储数据库的相同或者具有等效值。
一个money值被一个整数值除的除法结果会被截去分数部分。要得到圆整的结果，可以除以一个浮点值，或者在除法之前把money转换成numeric然后在除法之后转回money（如果要避免精度丢失的风险则后者更好）。当一个money值被另一个money值除时，结果是double precision（即一个纯数字，而不是金额），在除法中货币单位被约掉了。
字符类型
| 名字 | 描述 |
|---|---|
| varchar(n) | 有限制的变长 |
| char(n) | 定长，空格填充 |
| text | 无限变长 |
二进制类型
bytea数据类型允许存储二进制串，二进制串是一个八位位组（或字节）的序列。 二进制串和字符串的区别有两个：
- 首先，二进制串明确允许存储零值的字节以及其它“不可打印的”字节（通常是位于十进制范围32到126之外的字节）。 字符串不允许零字节，并且也不允许那些对于数据库的选定字符集编码是非法的任何其它字节值或者字节值序列。
- 其次，对二进制串的操作会处理实际上的字节，而字符串的处理和取决于区域设置。 简单说，二进制字串适用于存储那些程序员认为是“裸字节”的数据，而字符串适合存储文本。
| 名字 | 存储尺寸 | 描述 |
|---|---|---|
bytea |
1或4字节外加真正的二进制串 | 变长二进制串 |
bytea在mybatis中对应的jdbcType是BINARY，在java中对应的类型是byte[]
日期
| 名字 | 存储尺寸 | 描述 |
|---|---|---|
| date | 4字节 | 日期（没有一天中的时间） 2021-01-25 |
| time | 8字节 | 一天中的时间（无日期） 17:46:34 |
| timestamp | 8字节 | 包括日期和时间（无时区） 2021-01-25 17:46:36 |
| timestamptz | 8字节 | 包括日期和时间（有时区） 2021-01-25 17:46:39+00 |
| timetz | 12字节 | 一天中的时间（没有日期），带有时区 17:46:42+00 |
特殊时间关键字：
| 输入串 | 合法类型 | 描述 |
|---|---|---|
epoch |
date, timestamp |
1970-01-01 00:00:00+00（Unix系统时间0） |
infinity |
date, timestamp |
比任何其他时间戳都晚 |
-infinity |
date, timestamp |
比任何其他时间戳都早 |
now |
date, time, timestamp |
当前事务的开始时间 |
today |
date, timestamp |
今日午夜 (00:00) |
tomorrow |
date, timestamp |
明日午夜 (00:00) |
yesterday |
date, timestamp |
昨日午夜 (00:00) |
allballs |
time |
00:00:00.00 UTC |
获取当前时间戳：
(select floor(extract(epoch from now())) * 1000)
枚举
枚举（enum）类型是由一个静态、值的有序集合构成的数据类型。它们等效于很多编程语言所支持的enum类型。一个枚举类型的值的排序是该类型被创建时所列出的值的顺序。枚举类型的所有标准的比较操作符以及相关聚集函数都被支持。枚举标签是大小写敏感的，因此'happy'与'HAPPY'是不同的。标签中的空格也是有意义的。
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); CREATE TABLE person ( name text, current_mood mood ); INSERT INTO person VALUES ('Moe', 'happy'); SELECT * FROM person WHERE current_mood = 'happy'; name | current_mood ------+-------------- Moe | happy (1 row)
几何类型
几何数据类型表示二维的空间物体。
| 名字 | 存储尺寸 | 表示 | 描述 |
|---|---|---|---|
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>（中心点和半径） |
网络地址类型
PostgreSQL提供用于存储 IPv4、IPv6 和 MAC 地址的数据类型。用这些数据类型存储网络地址比用纯文本类型好，因为这些类型提供输入错误检查以及特殊的操作符和函数
| 名字 | 存储尺寸 | 描述 |
|---|---|---|
cidr |
7或19字节 | IPv4和IPv6网络 |
inet |
7或19字节 | IPv4和IPv6主机以及网络 |
macaddr |
6字节 | MAC地址 |
macaddr8 |
8 bytes | MAC地址（EUI-64格式） |
