数字类型
| 类型名称 | 描述 |
|---|---|
| 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');
