postgresql数据类型
数据类型
0. 数据类型的分类
| 分类名称 | 说明 | 与其他数据库对比 |
|---|---|---|
| 布尔类型 | 支持SQL标准boolean类型 | 与MYSQL中的bool，boolean类型一样，占用1字节空间 |
| 数值类型 | 整数类型有2字节的smallint，2字节int 8字节bigint ；精确数字类型numeric;非精确类型的浮点小数real和double precision 还有8字节的money（货币类型） | 无MYSQL中的unsigned类型，也无mysql中的1字节长tinint和3字节长的mediumint整数类型 |
| 字符类型 | 有varchar(n)，char(n)，text三种类型 | pg中最大可存储1G mysql中字符最大可存储64KB,PG中的text类型相当于MYSQL中的longtext类型 |
| 二进制数据类型 | bytea类型 | 对应的MYSQL中的blob和longblob类型 |
| 位串类型 | 由 0 和 1组成的字符串，有bit(n),bit varying(n)两种类型 | PG最大可支持8388080个bit位 |
| 日期和时间类型 | date ,time, timestamp | PG可精确到秒以下，MYSQL可精确到毫秒 |
| 枚举类型 | enum | PGSQL枚举需要预先使用create type来创建该类型 |
| 几何类型 | 包括点(point)，直线(line)，线段(lseg)，路径(path)，多边形(polygon),圆(cycle) | PG特有的类型 |
| 网络地址类型 | 有cidr,inet,macaddr 类型 | PG特有 |
| 数组类型 | 可以存储一个数组 | PG特有类型 |
| 复合类型 | 可以把已有的类型组合成用户自定义类型 | 对应其他数据库的自定义类型 |
| xml | XML相关数据存储 | |
| JSON/JSONB类型 | JSON类型 | MYSQL在8.0后可支持 |
| range类型 | 范围类型，可存储范围数据 | 无 |
| 对象标识符类型 | PG内部标识对象类型，如oid类型，regproc类型，regclass类型 | |
| 伪类型 | 不能作为字段的数据类型，但他可用于声明函数的参数或者结果的类型 | |
| UUID类型 | ||
| 其他类型 | ||
1. 数值类型
| 类型名称 | 存储长度 | 描述 | 范围 |
|---|---|---|---|
| smallint | 2字节 | 小范围整数类型 | -3276832767（-2^152^15-1） |
| integer | 4字节 | 整数类型 | -21474836482147493647（-2^312^31-1） |
| bigint | 8字节 | 大范围整数类型 | （-263~263-1） |
| decimal | 可变 | 用户指定精度 | 无限制 |
| numeric | 可变 | 用户指定精度 | 无限制 |
| real | 4字节 | 变长，不精确 | 6位十进制精度 |
| double precision | 8字节 | 变长，不精确 | 15位十进制精度 |
| smallserial | 2字节 | smallint自增序列 | 1~32767 |
| serial | 4字节 | integer自增序列 | 1~2147483647 （1-2^31-1） |
| bigserial | 8字节 | bigInteger自增序列 | 1~922372036854775807（1-2^64-1） |
注意：
- decimal和numeric是等效的
- real和double precison 浮点类型
- smallSerial和serial 和bingSerial指的是自增类型，但是其实是在创建后再后面默认添加了一个函数
- 浮点类型中还有几个特殊值
- Infinity
- -Infinity
- NaN
2. 字符类型
| 字符类型名称 | 描述 |
|---|---|
| varchar(n) | 变长类型，字符最大有限制 ，最大1G 存储空间为:4+实际字符长度。 |
| char(n) | 定长字段，没达到最大用空白填充 最大1G ,存储空间为4+n |
| text | 文本类型，变长，无限制 |
注意：
- 如果character varying(n)如果不声明长度，则和text一样，可以没有限制长度
- pgsql最大支持长度为1GB
2.1 字符串函数
- 计算字符数：char_length(xxx);
- 计算占用字节数：octet_length(xxx);
- 位置：postition(a in b)
- 子串：substring(str from 1 for 4)
- 拆分字符串：split_part(String text,delimiter text,filed int)
3. 时间类型
| 字符类型名称 | 存储长度 | 描述 |
|---|---|---|
| timestamp | 8字节 | 包括日期和时间，不带时区。简写成timestamp |
| Timestamp with time zone | 8字节 | 包括时间和日期，带时区，简写timestamptz |
| date | 4字节 | 日期，但不包含一天中的时间 |
| time | 8字节 | 一天中的时间，不包含日期，不带时区 |
| time with time zone | 12字节 | 一天中的时间，不包含日期，带时区 |
| interval | 16字节 | 时间间隔 |
3.1 时间类型函数
日期相加
select date '2017-07-29'+ interval'1 days'
日期相减
select date '2017-07-29'- interval'1 days'
常用函数
- 显示当前时间：
select current_date,current_time; - 抽取年月日:
select extract(field from source)
时间函数
- CURREN_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP
- CURRENT_TIME(precision)
- now()
4. 布尔类型(bool)
1 字节，状态为true或false
5. 网络地址类型
| 字符类型名称 | 存储长度 | 描述 |
|---|---|---|
| cidr | 7或者19字节 | IPV4或者IPV6 |
| inet | 7或者19字节 | IPV4或者IPV6 |
| macaddr | 6字节 | MAC地址 |
| macaddr8 | 8字节 | MAC地址（EUI-64格式） |
6. 数组类型
在创建的时候添加[]就可以变成数组类型
CREATE table text_arr1(
id integer,
arr_i integer[],
arr_t text[]
)
插入
insert into text_arr1 values(1,'{1,2,3}','["dadsa","dsada"]')
insert into text_arr1 values(1,array[1,2,3],array["dadsa","dsada"])
数组追加：array_append(anyArr,anyElem)
数组remove: array_remove(anyArr,anyElem);
获取数组维度：array_ndims(数组)
获取数组的位置:array_postition(arr,findElem)
替换:array_replace(arr,sourceElem,target)
TOString:array_to_string(arr,sourceElem,target)
7. 范围类型
- int4range:integer范围类型
- int8range:bigint范围类型
- numrange:numeric 范围类型
- tsrange :不带时区的timestamp范围类型
- tstzrange:带时区的timstamp范围类型
- date:date范围类型
8. json/jsonb类型
这两种类型使用上完全一致，JSON是把输入的数据原封不动的存储到数据中，但是jsonb类型是在存储的时候就把JSON解析成为了二进制类型，JSONB类型会比JSON类型效率高
8.0 JSON支持的类型
| JSON的类型 | PG上的类型 | 注意事项 |
|---|---|---|
| string | text | 字符集只支持UTF-8 |
| number | numeric | JSON中没有PG中的NaN和infinity的值 |
| boolean | boolean | JSON中只能接受小写的true或者false |
| null | none | SQL中的NULL代表不同的意思 |
8.1 json类型
创建表
create table table_name(id serial primary key,name json);
插入数据
insert into table_name(name) value ('{"col1":"dd","col2":"col2value"}')
查询
select * from table_name -- 全部
select name->'col2' from table_name --单字段，显示键值
select name ->>'col2' from table_name -- 单独字段，以文本格式
8.2 jsonb类型：其实就是以二进制存储，并且会去除一些不必要的空格和删除重复键
-- 将josn对象转换成键值对象
select * from json_each('{"col1":"dd","col2":"col2value"}')
--以文本形式返回
select * from json_each_text('{"col1":"dd","col2":"col2value"}')
-- 将行转换成json
select row_to_json(test_copy) from test_copy where id=1
9. 数据类型转换
| 函数 | 返回类型 | 描述 | 实例 |
|---|---|---|---|
| to_char(timestamp,text) | text | 把时间戳转换成字符串 | to_char(current_timestamp,"HH12:MI:SS") |
| to_char(interval,text) | text | 把间隔转换成字符串 | to_char(interval '15h 2m 12s',"HH12:MI:SS") |
| To_char(int,text) | text | 把整数类型转换成字符串 | |
| to_char(numeric,text) | text | 把数字转换成字符串 | |
| to_date(text,text) | date | 把字符转换成日期 | |
| to_number(text,text) | numeric | 把字符转换成数字 | |
| to_timestamp(text,text) | Timestamp with time zone | 把字符串转换成时间戳 |
- 用case 转换
select cast(varchar'123' as text);
select cast(varchar'123' as int4);
- 通过
::进行转换
select 1::int4,3/2::numeric;
