postgresql系列之数据类型

一、数字数据类型

1.1 数字类型列表：

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 数学函数

二、字符类型

2.1 字符类型列表

2.2 字符类型函数

三、 时间/日期类型

3.1 列表

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 时间/日期常用函数

-- 当天属于当年的第几天
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 数据类型转换函数

-- 通过CAST函数进行转换

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


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