postgresql系列之数据类型

本文是《postgresql实战》的读书笔记，具体可以参考本书对应章节

一、数字数据类型

1.1 数字类型列表：

类型名称	存储长度	描述	范围
smallint	2 bytes	小范围整数类型	-32768 to +32767
integer	4 bytes	整数类型	-2147483648 to +2147483647
bigint	8 bytes	大范围数据类型	-9223372036854775808 to 9223372036854775807
decimal	可变	用户指定精度	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
numeric	可变	用户指定精度	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
real	4 bytes	变长，不精确	6位十进制精度
double precision	8 bytes	变长，不精确	15 位十进制精度
smallserial	2 bytes	small自增序列	1 to 32767
serial	4 bytes	integer 自增序列	1 to 2147483647
bigserial	8 bytes	bigint 自增序列	1 to 9223372036854775807

decimal 和numeric是等效的。可以存储指定精度的多位数据。比如带小数位的数据。适用于要求计算准确的数值运算。

-- 声明number的语法：
numberic(precision,scale)
-- precision 是指numeric数字里的全部位数
-- scale 是指小数部分的数字位数

smallserial、serial 和 bigserial类型是指自增serial类型。严格意义不算一种数据类型。

-- serial 应用
create tabel test_serial(id serial,flag text)
-- 插入表数据可以不指定serial字段名称

---

1.2 数学函数

函数名称	作用描述
mod()	按模取余
round()	四舍五入
ceil()	向上取整
floor()	向下取整

---

二、字符类型

2.1 字符类型列表

字符类型名称	描述
character varing(n),varchar(n)	变成，字符最大数有限制
character(n),char(n)	定长，字符数没有达到最大值则使用空白填充
text	变成，无长度限制

---

2.2 字符类型函数

函数名称	作用描述
char_length()	字符数
octet_length()	字节数
position()	指定字符在字符串的位置
substring()	提取字符串中的子 串
split_part(string text,delimiter text,field int)	根据delimiter分隔符拆分字符串string,并返回指定字段，字段从1开始

---

三、 时间/日期类型

3.1 列表

字符类型名称	存储长度	描述
timestamp [(p)] [without time zone ]	8 bytes	包括日期和时间，不带时区，简写timestamp
timestamp [(p) ] with time zone	8 bytes	包括日期和时间，带时区，简写timestamp
date	4 bytes	不含时间
time [ (p)] [ without time zone ]	8 bytes	time of day (no date) 00:00:00 24:00:00
time [ (p)] with time zone	12 bytes	times of day only, with time zone 00:00:00+1459 24:00:00-1459
interval [fields ] [(p) ]	12 bytes	time interval -178000000 years 178000000 years

---

3.2 时间/日期类型操作符

SELECT date '2018-11-18' + interval ' 1 days';

-- 2018-11-19 00:00:00

SELECT date '2018-11-18' - interval '1 hour';
 --2018-11-17 23:00:00

---

3.3 时间/日期常用函数

函数名称	作用描述
current_date	当前日期
current_time	当前时间
extract(field FROM source)	可以从日期、时间数据类型中抽取年、月、日、时、分、秒信息 field可以是century、year、day、hour、minute、second等 source类型为timestamp、time、interval的表达式

-- 当天属于当年的第几天
SELECT extract(doy from now())；

---

四、数组类型

postgresql支持一维数组和多维数组。常用的数组类型为数字和字符类型数组。

4.1 数组类型定义

创建表时在字段数据类型后面加[] 即定义数组类型。

--1. 创建表
CREATE TABLE test_array(
	id integer,
	array_i integer[],
	array_t text[]
);

--2.插入数据
--方式一：
INSERT INTO test_array(id,array_i,array_t)
VALUES(1,'{1,2,3}','{"a","b","c"}');

--方式二：
INSERT INTO test_array(id,array_i,array_t)
VALUES(2,array[4,5,6],array['d','e','f']);

-- 3.查询数据元素
select array_i[1],array_t[3] FROM test_array WHERE id = 1;

--4. 修改
UPDATE test_array SET array_i[3]=4 WHERE id = 1;

---

4.2 数据函数

-- 追加(追加末尾)，移除(指定值)
select array_append(array[1,2],3),array_remove(array[1,2],2);
-- 获取维度
SELECT array_ndims(array[1,2]);

---

五、 json/jsonb 类型

postgresql不只是关系型数据库，同时还支持非关系数据类型json。 json属于重量级的非常规数据类型。

5.1 简单应用

--1.创建
CREATE TABLE test_json1(
	id serial PRIMARY key,
	name json
);
--2.插入数据
INSERT INTO test_json1(name) VALUES ('{"col1":1,"col2":"jack","col3":"male"}');
--3.查询
SELECT * FROM test_json1;
--4. 通过 "->"操作符可以查询json数据的键值
SELECT name -> 'col2' AS name FROM test_json1 WHERE id = 1;
--5.如果以文本格式返回json字段键值可以使用 "->>"操作符
SELECT name ->> 'col2' FROM test_json1 WHERE id = 1;

---

5.2 jsonb 与json差异

json存储格式为文本而jsonb存储为二进制。。 json类型以文本存储并储存的内容和输入数据一样，当检索json数据时必须重新解析，而jsonb以二进制形式存储已解析好的数据。当检索jsonb数据时不需要重新解析，因此json写入比jsonb快，但检索比jsonb慢。

• json保证输入和输出顺序，jsonb不保证加粗样式
• jsonb类型会去掉输入数据中键值的空格，另外会删除重复的键，仅保留最后一个。

--1.删除json数据的键/值。
SELECT '{"a":1,"b":2}'::jsonb - 'a';

--jsonb与json函数

--扩展最外层的json对象称为一组 键/值得结果集。
SELECT * FROM json_each('{"a":"foo","b":"bar"}');

/*
key	value
a	"foo"
b	"bar"
*/

--以文本格式返回
SELECT * FROM json_each_text('{"a":"foo","b":"bar"}');
/*
key	value
a	foo
b	bar
*/

--row_to_json()函数
-- 能够将行作为json对象返回，此函数常用来生成json测试数据。
-- 比如将普通表转换成json类型表。
create TABLE test_copy(
	id int4,
	name varchar(20)
);
INSERT INTO test_copy(id,name) VALUES(1,'a'),(2,'b');

-- 将普通表转成json类型表

SELECT row_to_json(test_copy) FROM test_copy;
/**
row_to_json
{"id":1,"name":"a"}
{"id":2,"name":"b"}
*/

-- 返回最外层的json对象中的键的集合
SELECT * FROM json_object_keys('{"a":"foo","b":"bar"}');
/*
json_object_keys
a
b
*/

-- jsonb 键/值的追加、删除、更新

-- jsonb 追加 通过 "||"操作符

SELECT  '{"name":"jack","age":"31"}'::jsonb || '{"sex":"male"}'::jsonb;
-- {"age": "31", "sex": "male", "name": "jack"}

-- json 键/值删除有两种方式 ：① "-" ②"#-"

SELECT '{"age": "31", "sex": "male", "name": "jack"}' ::jsonb - 'sex';
-- {"age": "31", "name": "jack"}

-- 第二种："#-" 通常用于嵌套json数据删除的场景

SELECT '{"name":"meki","contact":{"phone":"1234","fax":"0101"}}'::jsonb #- '{contact,fax}'::text[];
-- {"name": "meki", "contact": {"phone": "1234"}}

-- 键值更新 。一种方式通过 "||" ；另外一种是 jsonb_set

SELECT '{"name":"frank","age":"11"}'::jsonb || '{"age":"12"}'::jsonb;
-- {"age": "12", "name": "frank"}

--jsonb_set(target jsonb,path text[],new_value [,create_missing booean)
-- create_missing值为false表示如果键不存在则不添加
SELECT jsonb_set('{"name":"frank","age":"11"}'::jsonb,'{sex}','"male"'::jsonb,true);
-- {"age": "11", "sex": "male", "name": "frank"}

---

六、数据类型转换

postgresql数据类型转换主要有三种方式：

• 通过格式化函数
• CAST函数
• ：：操作符

---

6.1 数据类型转换函数

函数	返回类型	描述	示例
to_char(timestamp,text)	text	把时间戳转换成字符串	to_char(current_timestamp,‘HH12:MI:SS’)
to_char(interval,text)	text	把间隔转换成字符串	interval ‘15h 2m 12s’,HH24:MI:SS
to_char(int,text)	text	把整数转成字符串	to_char(111,‘999’)
to_date(text,text)	date	把字符串转换成日期	to_date(‘05 Dec 2000’,‘DD Mon YYYYY’)

-- 通过CAST函数进行转换

SELECT CAST(VARCHAR'123' as TEXT);
SELECT CAST(VARCHAR '123' AS INT4);


-- 通过 ::操作符进行转换
SELECT 1::int4,3/2::NUMERIC;