数值类型
货币类型
money 8字节
money 类型存储带有固定小数精度的货币金额,可以与numeric、int、bigint进行转化。不建议使用浮点数处理货币类型,因为可能存在精度问题。
字符类型
char(n) ,character(n)定长,不足补空白
varchar(n) ,character varying(n)变长,有长度限制
text 变长,无长度限制
日期 / 时间
布尔类型
boolean 一字节 true / false / null
枚举类型
创建枚举
create type 枚举名 as enum('值1', '值2', ..., '值n');
例如:create type mood enum as ('aaa','bbb');
使用枚举
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
几何类型
网络地址类型
PostgreSQL 提供用于存储 IPv4 、IPv6 、MAC 地址的数据类型。
用这些数据类型存储网络地址比用纯文本类型好, 因为这些类型提供输入错误检查和特殊的操作和功能。
位串类型
位串就是一串 1 和 0 的字符串。它们可以用于存储和直观化位掩码。 我们有两种 SQL 位类型:bit(n) 和bit varying(n), 这里的n是一个正整数。
bit 类型的数据必须准确匹配长度 n, 试图存储短些或者长一些的数据都是错误的。bit varying 类型数据是最长 n 的变长类型;更长的串会被拒绝。 写一个没有长度的bit 等效于 bit(1), 没有长度的 bit varying 意思是没有长度限制。
json类型
JSON 代表 JavaScript Object Notation。JSON是开放的标准格式,由key-value对组成。JSON的主要用于在服务器与web应用之间传输数据。与其他格式不同,JSON是人类可读的文本格式。
从PostgreSQL 9.3开始,json就成了postgres里的一种数据类型,也就是和varchar、int一样,我们表里的一个字段的类型可以为json了。
与此同时,postgres还提供了jsonb格式,jsonb格式是json的二进制形式,二者的区别在于json写入快,读取慢,jsonb写入慢,读取快,但在操作上,二者是没有区别的。
注意:键值对的键必须使用双引号
json、jsonb通用操作符
jsonb额外操作符
创建表
假设我们要存储的json数据是这样的:
{ "id": ID "name":"名字", "age":年龄 }
建表语句如下:
create table if not exists user (
info jsonb
)
好了,这样就创建了一张表,里面只有一个 info
字段,下面开始进行CRUD操作。
插入数据
插入数据可以直接以json格式插入:
insert into user values('{"id":1,"name":"小明", "age":18}')
在json里插入新的key值gender(info中的id为1时),如下:
select info || '{"gender":2}'::jsonb from user where (info->>'id')::int4 = 1;
注:|| 为json连接符
查询数据
Postgres里的查询需要用到查询符。比如说,我们要查询id为1的数据,语句如下:
select info from user where info @> '{"id":1}'::jsonb
用到了 @>
这个查询符,表明info当前这条记录里的顶层json中有没有id为1的key-value对;有的话则满足条件。
再来一个复杂一点的查询的,查询 age>16
的记录,并且只显示 name
,语句如下:
select info->'name' from user where (info->>'age')::int4 > 16
修改数据
下面,将info中id为1的age改为22 ,SQL语句:
select info || '{"age":22}'::jsonb from user where (info->>'id')::int4 = 1;
上述用法仅适用于9.5以上,9.5以下需要整个记录更新,不可以单独修改某个值。
删除数据
删除所以info的age这个key,SQL如下:
select info-'age' from user
直接用操作符 -
即可。
数组类型
PostgreSQL 允许将字段定义成变长的多维数组。
数组类型可以是任何基本类型或用户定义类型,枚举类型或复合类型。
声明数组
创建表的时候,我们可以声明数组,方式如下:
CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] );
pay_by_quarter 为一维整型数组、schedule 为二维文本类型数组。
我们也可以使用 "ARRAY" 关键字,如下所示:
CREATE TABLE sal_emp ( name text, pay_by_quarter integer ARRAY[4], schedule text[][] );
插入值
插入值使用花括号 {},元素在 {} 使用逗号隔开:
INSERT INTO sal_emp VALUES ('Bill', '{10000, 10000, 10000, 10000}', '{{"meeting", "lunch"}, {"training", "presentation"}}'); INSERT INTO sal_emp VALUES ('Carol', '{20000, 25000, 25000, 25000}', '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
访问数组
现在我们可以在这个表上运行一些查询。
首先,我们演示如何访问数组的一个元素。 这个查询检索在第二季度薪水变化的雇员名
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2]; 查询结果 name ------- Carol (1 row)
修改数组
我们可以对数组的值进行修改:
UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}' WHERE name = 'Carol';
数组中检索
要搜索一个数组中的数值,你必须检查该数组的每一个值。
比如:
SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR pay_by_quarter[2] = 10000 OR pay_by_quarter[3] = 10000 OR pay_by_quarter[4] = 10000;
另外,你可以用下面的语句找出数组中所有元素值都等于 10000 的行:
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
复合类型
1、创建符合类型
create type 类型名 as (
属性1 属性1类型
属性2 属性2类型
...
属性n 属性n类型
);
例如:
create type complex as(
id int,
name text
);
2、创建表使用复合类型
create table user (
complex_value complex,
age int
);
3、向表中插入数据 -
insert into user(complex_value, age) values((1,"张三"), 20);
4、访问复合类型 -- (复合类型名).复合类型属性,加括号防止与 表名.表属性 混合
select (complex).name from user where (complex).id = 1;
若需要添加表名也可这样写:
select (user.complex).name from user where (user.complex).id = 1;
范围类型
范围数据类型代表着某一元素类型在一定范围内的值。
例如,timestamp 范围可能被用于代表一间会议室被预定的时间范围。
PostgreSQL 内置的范围类型有:
-
int4range — integer的范围
-
int8range —bigint的范围
-
numrange —numeric的范围
-
tsrange —timestamp without time zone的范围
-
tstzrange —timestamp with time zone的范围
-
daterange —date的范围
此外,你可以定义你自己的范围类型。
CREATE TABLE reservation (room int, during tsrange); INSERT INTO reservation VALUES (1108, '[2010-01-01 14:30, 2010-01-01 15:30)'); -- 包含 SELECT int4range(10, 20) @> 3; -- 重叠 SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0); -- 提取上边界 SELECT upper(int8range(15, 25)); -- 计算交叉 SELECT int4range(10, 20) * int4range(15, 25); -- 范围是否为空 SELECT isempty(numrange(1, 5));
范围值的输入必须遵循下面的格式:
(下边界,上边界) (下边界,上边界] [下边界,上边界) [下边界,上边界] 空
圆括号或者方括号显示下边界和上边界是不包含的还是包含的。注意最后的格式是 空,代表着一个空的范围(一个不含有值的范围)。
-- 包括3,不包括7,并且包括二者之间的所有点 SELECT '[3,7)'::int4range; -- 不包括3和7,但是包括二者之间所有点 SELECT '(3,7)'::int4range; -- 只包括单一值4 SELECT '[4,4]'::int4range; -- 不包括点(被标准化为‘空’) SELECT '[4,4)'::int4range;