详解PostgreSQL数据类型
字符类型
char(n), varchar(n) 和text。
char和varchar超出长度的字符都是空白,这种情况下该字串将被截断为最大长度。
如果没有长度声明,char等于char(1),而varchar则可以接受任何长度的字串。
数字类型
由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
serial:自增的int类型,自动创建了一个序列,把默认的值赋予下一个序列。当insert没有指定serial列的值时,则默认的从序列中取出值,并且将序列的值自动加1。
日期类型
名字
存储尺寸
描述
最小值
最大值
解析度
timestamp [ (p)][withouttimezone]
8字节
包括日期和时间(无时区)
4713 BC
294276 AD
1微秒 / 14位
timestamp [ (p)]withtimezone
8字节
包括日期和时间,有时区
4713 BC
294276 AD
1微秒 / 14位
date
4字节
日期(没有一天中的时间)
4713 BC
5874897 AD
1日
time [ (p)][withouttimezone]
8字节
一天中的时间(无日期)
0:00:00
24:00:00
1微秒 / 14位
time [ (p)]withtimezone
12字节
仅仅是一天中的时间,带有时区
00:00:00+1459
24:00:00-1459
1微秒 / 14位
interval [ fields][(p)]
16字节
时间间隔
-178000000年
178000000年
1微秒 / 14位
枚举(enum)类型
是由一个静态值的有序集合构成的数据类型,它们等效于很多编程语言所支持的enum类型。
枚举类型的一个例子可以是一周中的日期,或者一个数据的状态值集合。
枚举类型可以使用CREATE TYPE命令创建。
一旦被创建,枚举类型可以像很多其他类型一样在表和函数定义中使用。
一个枚举类型的值的排序是该类型被创建时所列出的值的顺序。
几何类型
名字
存储尺寸
表示
描述
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> (center point and radius)
网络地址类型
名字
存储尺寸
描述
cidr
7或19字节
IPv4和IPv6网络
inet
7或19字节
IPv4和IPv6主机以及网络
macaddr
6字节
MAC地址
位串类型
位串就是一串1和0的字串,可以用于存储和视觉化位掩码。
有两种类型的SQL位类型:bit(n)和bit varying(n); n是一个正整数。 bit类型的数据必须准确匹配长度n; 试图存储短些或者长一些的数据都是错误的。 类型bit varying数据是最长n的变长类型;更长的串会被拒绝。 写一个没有长度的bit等效于bit(1),没有长度的bit varying相当于没有长度限制。
数组类型
1、任何基本类型 (不是组合类型或域) 可以用作数组,只要在类型后面加一个中括号 ([]) 。
创建一个二维数组如下: CREATE TABLE a(a int[][5]);
PG不强制要求定义数组大小,甚至维数也可以不定义。所有的数组作为type[].
ANSI 语法只支持一维数组,并且需要定义数组大小: CREATE TABLE a(a int ARRAY[5]);
Arrays 可以为 NULL, 但数组内的元素不能为null。
2、不限长度:int[]和int[10]都不会限定元素个数。
不限维度:int[]和int[][]效果是一样的,都可以存储任意维度的数组。
矩阵强制:多维数组中,同一个维度的元素个数必须相同。
元素强制:元素类型必须一致。
3、扩展: 一维数组支持prepend(往前追加),append(往后追加),cat(两个数组合并)操作。 二维数组仅支持cat操作:array_append、array_prepend、array_cat。 subscript:数组的下标,默认是从1开始编号,除非赋值的时候强制指定subscript。
文本搜索类型
PostgreSQL提供两种数据类型,它们被设计用来支持全文搜索。 tsvector类型:表示一个为文本搜索优化的形式下的文档,去除重复分词后按分词顺序存储 ,可以存储位置信息和权重信息。 tsquery类型:表示一个文本查询,存储查询的分词,可存储权重信息。
XML类型
XML数据类型可以被用来存储XML数据。
XML数据类型比直接在一个text域中存储XML数据的优势在于: 会检查输入值的结构是不是良好; 有支持函数用于在其上执行类型安全的操作。
使用这种数据类型要求在安装时用configure --with-libxml选项编译。
墨天轮原文链接:https://www.modb.pro/db/150306?sjhy(复制链接至浏览器或点击文末阅读原文查看)
关于作者
陈家睿,云和恩墨MySQL技术顾问,拥有MySQL OCP、PGCE、OBCA、SCDP证书,长期服务于电信行业。现负责公司MySQL数据库、分布式数据库运维方面的技术工作;热衷于运维故障处理、备份恢复、升级迁移、性能优化的学习与分享。