PostgreSQL 数据类型

数字类型

  • 整数
  • 用户指定精度类型
  • 浮点类型
  • serial类型（自增整数）
名字 存储长度 描述 范围
smallint 2 字节 小范围整数 -32768 到 +32767
integer 4 字节 常用的整数 -2147483648 到 +2147483647
bigint 8 字节 大范围整数 -9223372036854775808 到 +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

字符类型

名称 描述
character varying(n), varchar(n) 变长，有长度限制
character(n), char(n) n定长,不足补空白
text 变长，无长度限制

PostGreSQL支持最大的字段大小为1GB，所以text的无限制并非真的无限制。

相关函数

char_length('abcd')求字符数
octet_length('abcd') 求字符占用字节数
position('a' in 'abcd')字符在字符串的位置
substring('francs' from 3 for t)字符串切片
split_part(string text, delimiter text, field int)切割字符串

时间/日期类型

名称 描述
timestamp 日期和时间
date 日期
time 只用于一日内的时间
interval 时间间隔

相关函数

select now() 显示当前日期和时间

select current_date, current_time; 显示当前日期和时间

select extract(year from now()); 从时间中抽出{year}

布尔类型

名称 描述
boolean true/false

true的有效值可以是TRUE, t, true, y, yes, on, 1
false的有效值是FALSE, f, false, n, no, off, 0

网络地址类型

名称 存储空间 描述
cidr 7或19字节 IPv4或IPv6网络
inet 7或19字节 IPv4或IPv6主机和网络
macaddr 6字节 MAC地址

可以使用转换来检查类型是否合法：
select '192.168.2.1000'::inet;

数组类型

数字的定义是在字段数据类型后面加上[]。

比如：

1
2
3
4
5
CREATE TABLE test_array1 (
	id integer,
	array_i integer[],
	array_t text[]
);

数组类型的插入有两种方式。
第一种使用花括号：

1
2
INSERT INTO test_array1(id, array_i, array_t)
	VALUES (1, '{1, 2, 3}', '{"A", "B", "c"}');

第二种使用ARRAY关键字：

1
2
INSERT INTO test_array1(id, array_i, array_t)
	VALUES (2, array[4,5,6], array['d', 'e', 'f']);

数组的操作

查询

正常进行查询即可，返回整个数组

1
SELECT array_i FROM test_array1 WHERE id=1;

也可以使用下标：

1
SELECT array_i[1] FROM test_array1 WHERE id=1;

追加

语法：array_append(anyarray, anyelement)

例子：

1
SELECT array_append(array[1,2,3], 4);

也可以使用||操作符：

1
2

SELECT array[1,2,3]||4;

删除

语法：array_remove(anyarray, anyelement)

例子：

1
SELECT array_remove(array[1,2,2,3], 2);

修改

1
UPDATE test_array1 SET array_i[3]=4 WHERE id=1;

数组操作符

相关函数

SELECT array_ndims(array[[1,2],[3,4]]); 获取数组维度

SELECT array_length(array[1,2],1); 获取数组从长度

SELECT array_position(array['a','b','c','d'], 'd') 返回元素在数组的位置

SELECT array_replace(array[1,2,5,4], 5, 10); 替换元素

SELECT array_to_string(array[1,2,3,4], ',', 'None'); 元组转字符串显示，第二个参数为分隔符，第三个参数为值为NULL的显示默认值

范围类型

名称 描述
int4range integer的范围
int8range bigint的范围
numrange numeric的范围
tsrange timestamp without time zone的范围
tstzrange timestamp with time zone的范围
daterange date的范围

用户可以自定义范围数据类型：

1
SELECT int4range(1, 5);

每个类型都包含上界，和下界。[代表包含下界，(代表排除下界。
声明范围类型时，定义上，下界的方式：

1
SELECT int4range(1, 5, '(]');

Json/Jsonb类型

1
SELECT '{"a":1, "b":2}'::json;

为了方便说明例子，创建一个带有json类型的表：

1
CREATE TABLE test_json1(id serial primary key,name json);

插入元素

1
2
3
INSERT INTO test_json1(name) VALUES
('{"col1":1, "col2":"francs", "col3": "male"}'),
('{"col1":2, "col2":"fp", "col3": "female"}');

查询数据

1
2
# id为1，字段name的json下键为col2的值
SELECT name -> 'col2' FROM test_json1 WHERE id=1;

以文本格式返回json字段键值：

1
SELECT name ->> 'col2' FROM test_json1 WHERE id=1;

json和jsonb差异

json存储格式为文本，而jsonb存储格式为二进制。

  • json写入快，jsonb检索快
  • jsonb输出的键的顺序和输入不一样
  • jsonb会去掉输入数据中键值的空格
  • jsonb会删除重复的键，仅保留最后一个

大多数应用场景下建议使用jsonb。

操作符

SELECT '{"a": 1, "b": 2}'::jsonb ? 'a'; 键是否在json中

SELECT '{"a": 1, "b": 2}'::jsonb - 'a';删除键值

相关函数

SELECT * FROM json_each('{"a": 1, "b": 2}'); 以键值对输出

SELECT * FROM json_each_text('{"a": 1, "b": 2}'); 以文本形式返回

SELECT row_to_json(test_copy) FROM test_copy WHERE id=1; 将一行数据通过json格式返回（实用）

SELECT * FROM json_object_keys('{"a": 1, "b": 2}'); 返回键集合

jsonb键值操作

追加

1
2
SELECT '{"name": "framcs", "age": "31"}'::jsonb ||
	'{"sex": "male"}'::jsonb;

删除

1
SELECT '{"name": "framcs", "age": "31"}': - 'age';

更新

1
2
SELECT '{"name": "framcs", "age": "31"}'::jsonb ||
	'{"age": "32"}'::jsonb;