PostgreSQL 数据类型

数字类型

类型名称	描述
int2 / smallint	2 字节, 小范围整数
int4 / integer	4 字节, 整数类型
int8 / bigint	8 字节, 长整形
decimal / numeric	可变长度, 用户制定精度
real	4 字节，6 位十进制精度
double precision / float	8 字节，float 会自动转换为 double

在 PostgreSQL 中，decimal 和 numeric 是相同的:

create table public.test(
  f1 int4, -- 32 位, 相当于 Java 中的 int
  f2 real, -- 相当于 float, 而 PostgreSQL 中的 float 则会自动转化为 double precision
  f3 decimal(5, 3) -- 5: 全部位数, 3: 小数部分位数, 如 33.123
)

除了基本的数字类型外，PostgreSQL 中还提供了自增的数据类型：

create table public.test(
  id1 smallserial, -- int2 自增
  id2 serial, -- int4 自增
  id3 bigserial -- int8 自增
)

数字类型函数：

• 四则运算

select
     (5 + 3) as f1, -- 8
     (5 - 3) as f2, -- 2
     (5 * 3) as f3, -- 15
     (5 / 3) as f4; -- 1

• 取余函数

select mod(9, 2); -- 1

• 四舍五入函数

select round(11.2), -- 11
     round(11.6); -- 12

字符类型

类型名称	描述
varchar(n)	变长, n 大小没有限制
char(n)	定长, n 大小没有现在
text	变长, 任意长度字符串

在 PostgreSQL 中，varchar(n) = character varying(n)，char(n) = character(n).

字符类型函数：

• 字符长度函数

select char_length('abcd'); -- 4

• 字符在字符串的位置

select position('ab' in 'alabb'); -- 3, 下标从 1 开始

• 提取子串

select substring('abcdefg' from 3 for 2); -- cd, 从 3 开始, 截取两个

• 切分字符串

select split_part('aladdin.im', '.', 1); -- aladdin, 原字符串, 分隔符, 第几个字段

时间类型

类型名称	描述
timestamp	8 字节, 日期 +时间格式
timestamp with time zone	8 字节, 日期 + 时间 + 时区格式
date	4 字节, 日期格式(无时间)
time	8 字节, 时间格式(无日期)
time with time zone	12 字节, 时间 + 时区
interval	16 字节时间间隔

Demo：

select now()::timestamp; -- 2019-03-22 06:32:57.754700
select now()::timestamp with time zone; -- 2019-03-22 06:35:43.484050
select now()::date; -- 2019-03-22
select now()::time; -- 06:37:26
select now()::time with time zone; -- 06:38:18.979388
select now() + interval '1 day'; -- 明天的时间
-- interval '1 day' = 将 1 day 转换成时间间隔

四则运算：

• 加 & 减

select now() + interval '1 day';
select now() + interval '1 hour';
select now() - interval '1 second';

• 乘

select interval '1 second' * 100; -- 0 years 0 mons 0 days 0 hours 1 mins 40.00 secs

• 除

select interval '1 hour' / 3; -- 0 years 0 mons 0 days 0 hours 20 mins 0.00 secs

常用函数：

• 抽取函数

select extract(year from now()); -- 2019
select extract(week from now()); -- 12, 一年中的第几周
select extract(doy from now()); -- 81, 一年中的第几天

布尔类型

类型名称	描述
boolean	true / false

给布尔类型赋值：

insert into table3 values (true);
insert into table3 values ('true');
insert into table3 values ('t');
insert into table3 values ('1'); -- true

网络地址类型

类型名称	描述
cidr	IPv4 和 IPv6 网络地址, 默认带子掩码
inet	IPv4 和 IPv6 网络地址, 带不带子掩码看输入
macaddr	MAC 地址
macaddr8	MAC 地址(EUI-64 格式)

Demo:

select '192.168.0.1'::cidr; -- 192.168.0.1/32
select '192.168.0.1'::inet; -- 192.168.0.1
select '192.168.0.1/16'::inet; -- 192.168.0.1/16
select '00-01-6C-06-A6-29'::macaddr; -- 00:01:6c:06:a6:29

常用函数：

• 获取主机

select host(inet '192.168.0.1/16'); -- 192.168.0.1

• 转换成文本

select text(inet '192.168.0.1/16'); -- 192.168.0.1/16

• 获取子掩码

select netmask(inet '192.168.0.1/16'); -- 255.255.0.0

数组类型

定义：

create table table4(
  intarr int4[],
  textarr text[]
);

插入：

-- 方式一
insert into 
  public.table4(intarr, textarr) 
  values
         ('{1, 2, 3}', '{"a", "b", "c"}');

-- 方式二
insert into 
  public.table4(intarr, textarr) 
  values 
         (array[1, 2, 3], array['a', 'b', 'c']);

查询：

-- 下标从 1 开始，而非 0
select intarr[1], intarr[2]
from table4;

数组元素增删改：

-- 增加
select array_append(intarr, 4) from table4; -- {1， 2， 3， 4}
-- 删除
select array_remove(intarr, 2) from table4;
-- 上面并未更改真正的数据

-- 增加
update table4 set intarr = array_append(intarr, 4); -- where ...
-- 更新
update table4 set intarr[2] = 8; -- where ...

数组函数：

• 数组维度

select array_ndims(intarr) from table4; -- 1

• 数组长度

select array_length(intarr, 1) from table4; -- array_length(arr, dim), dim: 维度

• 元素位置

select array_position(intarr, 8) from table4; -- 下标从一开始

• 元素替换

select array_replace(intarr, 8, 2) from table4; -- 将 8 替换成 2

范围类型

类型名称	描述
int4range	integer 范围类型
int8range	bigint 范围类型
numrange	numeric 范围类型
tsrange	不带时区的 timestamp 范围类型
tstrange	带时区的 timestamp 范围类型
daterange	date 范围类型

Demo：

select int4range(4, 9); -- [4,9)
select daterange('2018-9-1', '2019-9-1'); -- [2018-09-01,2019-09-01)

范围操作符：

select int4range(1, 10) @> 4; -- true
select int4range(1, 10) @> 15; -- false

范围类型函数：

• 上下限

select lower(int4range(2, 6)); -- 2
select upper(int4range(2, 6)); -- 6

Json 类型

创建：

create table public.table1(
  id int4,
  info json
);

插入：

insert into public.table1 values (1001, '{"name": "aladdin", "addr": "in china"}');

查询：

select info ->> 'name' as name from table1; -- aladdin
select info -> 'name' as name from table1; -- "aladdin"

Jsonb 类型

Jsonb 与 Json 使用上完全相同，但是 Json 类型会以文本的形式存入，Jsonb 会先对 Json 串进行编译，然后存入。

由于 Json 格式不需要编译就可以直接以文本的形式存入，所以插入速度更快，但是每次查询都需要对 Json 进行解析，查询速度较慢；Jsonb 格式恰恰相反，Jsonb 格式会先将 Json 串进行编译然后存储，所以插入速度较慢，查询速度较快。

如果做数据分析，一般都会选用 Jsonb 格式。

创建：

create table public.table2(
  id int4,
  info jsonb
);

插入：

insert into public.table2 values (1001, '{"name": "aladdin", "addr": "in china"}');

查询：

select info ->> 'name' as name from table2; -- aladdin
select info -> 'name' as name from table2; -- "aladdin"

键值增删改操作：

-- " || " 表示增加 / 更新
select '{"name": "aladdin", "addr": "in china"}'::jsonb || '{"hobby": "computer games"}'::jsonb;
-- 
select '{"name": "aladdin", "addr": "in china"}'::jsonb || '{"name": "aladdinxyz"}'::jsonb;

-- " - " 表示删除
select '{"name": "aladdin", "addr": "in china"}'::jsonb - 'addr';

Json 与 Jsonb 常用函数：

• 组成结果集

select * from json_each_text('{"name": "aladdin", "addr": "in china"}');
select * from json_each('{"name": "aladdin", "addr": "in china"}'); -- value 带 "" 号

• 数据集转 Json

select row_to_json(table3) from table3;

数据类型的转换

• CAST 转换：

select cast(varchar'123' as int4);

• 通过 " :: " 进行转换：

select 1::int4, 3/2::double precision;

• 转换函数：

-- timestamp to char
select to_char(current_timestamp, 'yyyy:MM:dd');

-- 分钟为 mi 而不是 mm
select to_char(interval '10h 5m 20s', 'HH24:mi:ss');

select to_char(123, '0999'); -- 0123
select to_char(123.5, '999D99'); -- 123.50

-- char to date
select to_date('2018-9-13', 'yyyy-MM-dd');

-- char to timestamp
select to_timestamp('2018-9-13', 'yyyy-MM-dd');