PostgreSQL06-数据类型

数字类型

数字类型操作符和数学函数

select 1+2,5-2.2*2,10/2,8%3;   加、减、乘、除、取余
select mod(8,3);   按模取余
select round(5.3),round(8.9);    四舍五入
select ceil(3.6),ceil(-8.2);      向上取整
select floor(5.8),floor(-6.9);     向下取整

---

字符类型

字符类型操作符和函数

select char_lentch('id') from product;    查看字符串长度
select octet_lentch('id') from product;   查看字符串字符数
select position('e' in 'hello');     指定字符在字符串中的位置
select substring('hello' from 2 for 2);     提取字符串，从2位开始提取2位
select split_part('hello world', ' ', 2);       拆分字符串，以空格为间隔，取第二部分

---

日期/时间类型

时间类型操作与函数

select now();     查看时间，有时区
select now()::timestamp without time zone;      ::表示转换类型，转换为'timestamp without time zone'类型
select now(),now()+interval '1 day' as "now";     指定时间间隔（hour,month,year）
select now()::timestamp(0);     精度，0表示秒后的小数点位数，默认6，只有类型的括号内能加精度

加，减，乘，除
select date '2020-09-20' + interval '1 day';
select date '2020-10-01' - intreval '1 hour';
select 100 * interval '1 second';
select interval '1 hour' / 2;

select current_date,current_time;    当前日期和时间
select extract( year from now() );      从时间格式中抽取时间单位（世纪，年月日，时分秒）
select extract( week from now() );    当前日期是该年的第几周    
select extract( doy from now() );      当前日期是该年的第几天

---

布尔类型

true  状态的有效值可以是 TRUE,  t, true,  y, yes, on,  1
false 状态的有效值可以是 FALSE, f, false, n, no,  off, 0

---

网络地址类型

函数

select host(cidr '192.168.13.113/24');    取IP
select text(cidr '192.168.13.113/24');    取整体
select netmask(cidr '192.168.13.113/24'); 取掩码

---

数组类型

定义

建表时，在字段类型后添加[]
create table test_array1(
    id         integer,
    array_i    integer[],
    array_t    test[]
);

数据输入

使用{}包围数组的值
insert into test_array1 values (1, '{1,2,3}', '{"a","b","c"}');
或使用array关键字
insert into test_array1 values (2, array[4,5,6], array['d','e','f']);

增删改查

select array_i[1],array_t[3] from test_array1 where id=1;   查询

select array_append(array[1,2,3],4);  向数组追加4  
select array[1,2,3] || 10;            向数组追加10

select array_remove(array[1,2,3,4],2);  从数组中移除元素2

update test_array1 set array_i[3]=30 where id=1;             修改数组中的元素
update test_array1 set array_i=array[10,20,30] where id=1;   修改数组整体

数组函数

array_ndims
array_length
array_position
array_replace
array_to_string

---

范围类型

---

json/jsonb类型

增删改查

创建表
create table test_json(
    id serial primary key,
    name json
);

插入
insert into test_json(name)
values('{"first":1,"second":"dier","third":3}');  字符加双引号

通过键名查询
select name -> 'first' from test_json where id=1;  返回json格式，使用->>返回文本格式，（当插入键值有引号时，返不返回引号）

删除键和值
select '{"hello":1, "world":2}'::jsonb -"hello"      删除"hello":1
select '{"name": "tom", "content": {"phone": "12345678", "fax": "12345678"}}'::jsonb #- '{content,fax}'::text[];  删除嵌套的json数据
select '{"name": "tom", "alias": ["tom", "TOM", "Tom"]}'::jsonb #- '{alias,1}'::text[];    删除嵌套的列表中的数据（1为索引，第二个元素） 

更新
select '{"name": "tom", "age": 10}'::jsonb || '{"age": 20}'::jsonb;   修改
select jsonb_set('{"name": "tom", "age": 10}'::jsonb,'{sex}','"man"'::jsonb,true);   jsonb_set函数，true表示，键值不存在就添加

json和jsonb的区别

1. json  写入比 jsonb 快，但检索比 jsonb 慢
2. jsonb 输出的键的顺序和输入不一样
3. jsonb 类型会去掉输入数据中键值的空格
4. jsonb 会删除重复的键，仅保留最后一个

函数

SELECT row_to_json(test_copy) FROM test_copy WHERE id=1; 将test_copy表中的数据转换为json格式

---

数据类型转换

select cast(varchar '123' as text);    cast函数
select 1::int4, 3/2::numeric;