3.1 数字类型
PostgreSQL支持的数字类型有整数类型、用户指定精度类型、浮点类型、serial类型。
3.1.1 数字类型列表：
这里重点说明smallserial、serial、bigserial类型，这些是指自增serial类型，如下代码创建一张测试表，定义test_serial表的id字段类型为serial类型：
图中可以看到表定义Default列为nextval('company_02_id_seq'::regclass)，现在插入数据，其中id可以不用插入，根据定义会自动生成（类似MySQL中autoincrement属性）：
图中可以看到id列会自动插入连续值。
3.1.2 数字类型操作符合数据函数：
加减乘除取模运算，例如：
tt=# select 1+1,3-2,2*3,6/2,8%3;?column? | ?column? | ?column? | ?column? | ?column?----------+----------+---------+----------+----------2 | 1 | 6 | 3 | 2(1 row)取模运算：
tt=# select mod(8,3);mod-----2(1 row)四舍五入函数运算：
tt=# select round(10.2),round(10.6);round | round-------+-------10 | 11(1 row)向上取整、向下取整函数运算：
tt=# select ceil(2.4),ceil(-2.4),floor(2.4),floor(-2.4);ceil | ceil | floor | floor------+------+-------+-------3 | -2 | 2 | -3(1 row)
3.2 字符类型
字符类型
3.2.2字符类型函数：
计算字符串占用字符数：
tt=# select char_length('abcde');char_length-------------5(1 row)计算字符串占用字节数：
tt=# select octet_length('abcde');octet_length--------------5(1 row)指定字符在字符串中的位置：
tt=# select position('a' in 'abcea');position----------1(1 row)截取字符串子串：
tt=# select substring('abce' from 2 for 2);substring-----------bc(1 row)拆分字符串：
tt=# select split_part('abc@123','@',2);split_part------------123(1 row)
3.3 时间/日期类型
3.3.1 字符类型列表：
例子：
tt=# select now();now -------------------------------2021-03-05 05:19:23.143058+08(1 row)日期类型转换：
tt=# select now()::timestamp without time zone;now----------------------------2021-03-05 05:20:59.151295(1 row)注意这里变化：没有时区
转换成data类型：
tt=# select now()::date;
now
------------
2021-03-05
(1 row)
转为没有时区的time：
tt=# select now()::time without time zone;
now
-----------------
05:23:34.217903
(1 row)
interval指时间间隔，可以是hour、day、month、year等，举例如下：
tt=# select now(),now()+interval'1 day';
now | ?column?
-------------------------------+-------------------------------
2021-03-05 05:26:01.496226+08 | 2021-03-06 05:26:01.496226+08
(1 row)
其中timestamp(精度)，例如：
tt=# select now(),now()::timestamp(0);
now | now
-------------------------------+---------------------
2021-03-05 05:27:45.025553+08 | 2021-03-05 05:27:45
(1 row)
3.3.2 时间/日期操作符：
时间、日期也支持加减乘除
日期相加、如下所示：
tt=# select date '2021-03-06'+ interval'1 days';
?column?
---------------------
2021-03-07 00:00:00
(1 row)
日期相减、如下所示：
tt=# select date '2021-03-06'- interval'1 days';
?column?
---------------------
2021-03-05 00:00:00
(1 row)
日期相乘、如下所示：
tt=# select 1000*interval'1 second';
?column?
----------
00:16:40
(1 row)
日期相除、如下所示：
tt=# select interval'1 hour'/double precision '3';
?column?
----------
00:20:00
(1 row)
3.3.3 时间/日期常用函数：
显示当前时间，如下：
tt=# select current_date,current_time;
current_date | current_time
--------------+--------------------
2021-03-05 | 05:35:52.289618+08
(1 row)
另一个非常重要的函数EXTRACT函数，可以从日期、时间中抽取年、月、日、时、分、秒信息，语法如下：
tt=# select EXTRACT(year from now());
date_part
-----------
2021
(1 row)
对于timestamp类型，取月份和月份里的第几天，代码如下：
tt=# select EXTRACT(month from now()),EXTRACT(day from now());
date_part | date_part
-----------+-----------
3 | 5
(1 row)
取小时、分钟、秒，如下：
tt=# select EXTRACT(hour from now()),EXTRACT(minute from
now()),extract(second from now());
date_part | date_part | date_part
-----------+-----------+-----------
5 | 40 | 22.557332
(1 row)
取当前日期所在年份的第几周，所在年份的第几天，如下：
tt=# select EXTRACT(week from now()),EXTRACT(doy from now());
date_part | date_part
-----------+-----------
9 | 64
(1 row)
3.4 布尔类型
PostgreSQL支持的布尔类型如下：
列表
其中true状态的有效值可以是TRUE、t、true、y、yes、on、1;false状态的有效值可以是FALSE、f、false、n、no、off、0传一张表演示：
示例
查询结果如下：
结果
3.5 网络地址类型
当存储IP地址需求的业务场景是，对于PostgreSQL并不熟悉的开发者可能会使用字符类型存储，实际上PostgreSQL提供用于存储IPv4、IPv6、MAC网络地址的专有网络地址数据类型，使用网络地址数据类型存储IP地址要优于字符类型，因为网络地址类型一方面会对数据合法性进行检查，另一方面也提供了网络地址操作符和函数，方便应用程序开发。
3.5.1 网络地址类型列表：
inet和cidr类型存储的网络地址格式为address/y，其中address表示IPv4或IPv6网络地址，y表示网络掩码位数，如果y省略，则对于IPv4网络掩码为32，对于IPv6网络掩码为128，所以该值表示一台主机。
inet和cidr都会对数据合法进行检查，如果数据不合法会报错，如下所示：
inet和cidr存在以下差别。
1) cidr类型输出默认带子网掩码信息，而inet不一定，如下所示：
tt=# select '192.168.2.100'::cidr;cidr------------------192.168.2.100/32(1 row)
tt=# select '192.168.2.100/32'::inet;
inet
---------------
192.168.2.100
(1 row)
tt=# select '192.168.0.0/16'::inet;
inet
----------------
192.168.0.0/16
(1 row)
2) cidr类型会对IP地址和子网掩码合法性进行检查，而inet不会，如下所示：
tt=# select '192.168.2.0/8'::cidr;
ERROR: invalid cidr value: "192.168.2.0/8"
LINE 1: select '192.168.2.0/8'::cidr;
^
DETAIL: Value has bits set to right of mask.
tt=# select '192.168.2.0/8'::inet;
inet
---------------
192.168.2.0/8
(1 row)
tt=# select '192.168.2.0/24'::cidr;
cidr
----------------
192.168.2.0/24
(1 row)
从以上演示可以看出cidr要比inet网络类型更加严谨。
3.5.2 网络地址操作符：
3.5.3 网络地址函数：
取IP地址，返回文本格式，如下所示：
tt=# select host(cidr '192.168.1.0/24');
host
-------------
192.168.1.0
(1 row)
取IP地址和子网掩码信息，返回文本格式，如下所示：
tt=# select text(cidr '192.168.1.0/24');
text
----------------
192.168.1.0/24
(1 row)
取网络地址子网掩码，返回文本格式，如下所示：
tt=# select netmask(cidr '192.168.1.0/24');
netmask
---------------
255.255.255.0
(1 row)
3.6 数组类型
PostgreSQL支持一维数组和多维数组，常用的数组类型为数字类型数组和字符型数组，也支持枚举类型、复合类型数组。
3.6.1 数组类型定义：
先来看数组类型的定义，创建表时在字段数据类型后面加方括号"[]"即可定义数组类型，如下所示：
tt=# create table test_array(id integer,array_i integer[],array_t text[]);
CREATE TABLE
以上integer[]表示integer类型一维数组，text[]表示text类型一维数组。
3.6.2 数组类型值输入：
数组类型的插入有两种方式，第一种方式使用花括号方式，如下所示：
'{val1 delim val2 delim...}'
将数组元素用花括号"{}"包围并用delim分隔符分开，数组元素值通常可以用双引号引用，delim分隔符通常为逗号，如下所示：
tt=# select '{1,2,3}';
?column?
----------
{1,2,3}
(1 row)
往test_array表中插入一条数据，如下：
tt=# insert into test_array(id,array_i,array_t) values(1,'{1,2,3}','{"a","b","c"}');
INSERT 0 1
数组类型插入的第二种方式使用ARRAY关键字，如下所示：
tt=# select array[1,2,3];
array
---------
{1,2,3}
(1 row)
往test_array1表中插入一条数据，如下：
tt=# insert into test_array1(id,array_i,array_t)
values(2,array[4,5,6],array['d','e','y']);
INSERT 0 1
表test_arrya1中的数据为：
tt=# select * from test_array1;
id | array_i | array_t
----+---------+---------
2 | {4,5,6} | {d,e,y}
(1 row)
3.6.3 查询数组元素：
如果想要查询数组所有元素值，只需查询数组名称即可，如下所示：
tt=# select array_i from test_array1 where id = 2;
array_i
---------
{4,5,6}
(1 row)
数据元素通过"[]"的方式，数据下标写在方括号内，范围编号为1到n,n为数组长度，如下所示：
tt=# select array_i[1],array_t[3] from test_array1 where id = 2;
array_i | array_t
---------+---------
4 | y
(1 row)
3.6.4 元素的追加、删除、更新：
追加有两种方式，如下：
tt=# select array_append(array[1,2,3],4),array[1,2,3]||4;
array_append | ?column?
--------------+-----------
{1,2,3,4} | {1,2,3,4}
(1 row)
删除用array_remove函数，如下所示：
tt=# select array[1,2,3,4],array_remove(array[1,2,3,4],4);
array | array_remove
-----------+--------------
{1,2,3,4} | {1,2,3}
(1 row)
修改，如下所示：
tt=# update test_array1 set array_i[3] = 10 where id = 2;
UPDATE 1
tt=# select * from test_array1;
id | array_i | array_t
----+----------+---------
2 | {4,5,10} | {d,e,y}
(1 row)
3.6.5 数组操作符：
3.6.6 数组函数：
添加、删除元素：
tt=# select array_append(array[1,2],4),array_remove(array[4,5,6],4);
array_append | array_remove
--------------+--------------
{1,2,4} | {5,6}
(1 row)
获取数组维度：
tt=# select array_ndims(array[1,2,4]),array_ndims(array[[1,2,3],[4,5,6]]);
array_ndims | array_ndims
-------------+-------------
1 | 2
(1 row)
获取数组长度、第一次出现的位置：
tt=# select array_length(array[1,2,3,4],1);
array_length
--------------
4
tt=# select array_position(array[1,2,3,4,3],'3');
array_position
----------------
3
(1 row)
字符串替换：
tt=# select array_replace(array[1,2,3,5],5,4);
array_replace
---------------
{1,2,3,4}
(1 row)
将数组元素输出到字符串，将null替换为4,5：
tt=# select array_to_string(array[1,2,3,null,6],',','4,5');
array_to_string
-----------------
1,2,3,4,5,6
(1 row)
3.9 数据类型转换
前面几小节介绍了 PostgreSQL 常规数据类型和非常规数据类型，这一小节将介绍数据
类型转换， PostgreSQL 数据类型转换主要有三种方式 ： 通过格式化函 数、 CAST 函 数、．．
操作符，下面分别介绍 。
3.9.1 通过格式化函数进行转换
3.9.2 通过CAST函数进行转换
将varchar类型转换为text类型，如下所示：
tt=# select 'abc',cast('abc' as text);
?column? | text
----------+------
abc | abc
(1 row)
将varchar类型转换为int4类型，如下所示：
tt=# select cast('123' as int4),cast('123' as int);
int4 | int4
------+------
123 | 123
(1 row)
3.9.3 通过操作符::进行转换
将int类型转换为numeric，如下所示：
tt=# select 1::int4,1/3::numeric;
int4 | ?column?
------+------------------------
1 | 0.33333333333333333333
(1 row)
另一个例子，通过 SQL 查询给定表的字段名 称，先根据表名在系统表 pg_class 找到表
的 OID ，其中 OID 为隐藏的系统宇段：
tt=# select oid,relname from pg_class where relname = 'test_json1';
oid | relname
-------+------------
16454 | test_json1
(1 row)
之后根据 test_json 1 表的 OID ， 在 系 统表 pg_attribute 中根据 attrelid （ 即 表 的 OID ）找
到表的字段，如下所示：
tt=# select attname from pg_attribute where attrelid='16454' and attnum>0;
attname
---------
id
name
(2 rows)
上述操作需通过两步完成，但通过类型转换可一步到位 ， 如下所示 ：
tt=# select attname from pg_attribute where attrelid='test_json1'::regclass and ``````attnum>0;
attname
---------
id
name
(2 rows)
这节介绍了三种数据类型转换方法，第－种方法兼容性相对较好，第三种方法用法简捷。
网友评论