PostgreSQL数据类型及基本操作
文章目录
- 一、数据类型
- 二、对比MySQL
- 三、基本操作
- 1、单引号和双引号
- 2、数据类型转换
- 3、数字类型
- 1）整数
- 2）浮点数
- 3）序列
- 4）常见操作
- 4、 字符串类型
- 5、 日期类型
- 1）声明时间
- 2）日期类型的运算
- 6、 布尔类型
- 7、 枚举类型
- 8、IP类型
- 9、 JSON&JSONB类型
- 10、 复合类型
- 11、 数组类型
一、数据类型
详细查看：中文官方文档
二、对比MySQL
名字 说明 对比MySQL 整数类型 smallint（2字节），integer（4字节），bigint（8字节） 无区别 浮点类型 decimal，numeric（和decimal是等效的），real（float），double precision（double），money（货币类型） MySQL不支持 货币类型 字符类型 varchar(n)，char(n)，text 无区别 日期/时间类型 date（年月日），time（时分秒），timestamp（年月日时分秒）【time和timestamp可以设置时区】 MySQL多了一个datetime 布尔类型 boolean MySQL中的tinyint类型与之对应，都是1字节 枚举类型 enum 无区别 几何类型 点、线、圆、多边形、路径… MySQL不支持 数组类型 在类型后，追加[]，代表存储数组 MySQL不支持 JSON类型 文本 JSON 数据、二进制 JSON 数据jsonb MySQL8.x才支持 等等 其它不常用的 … 三、基本操作
1、单引号和双引号
- 单引号：用来标识实际的值
- 双引号：用来标识关键字
比如，下面的key会报错
2、数据类型转换
方式1：数据类型 值
在值的前面加上具体数据类型
-- 将字符串转成int4类型 select int '123';
方式2：值 :: 数据类型
在值的后面加上具体数据类型，用::拼接
-- 将字符串转成date类型 select '2023-12-18'::date;'
方式3：使用CAST函数
CAST(当前类型 值 as 转后的类型)
select CAST(varchar '123' as int);
3、数字类型
1）整数
有3个类型，如下：
- smallint、int2：2字节
- integer、int、int4：4字节
- bigint、int8：8字节
2）浮点数
浮点类型就关注2个，但两个基本等效
- decimal(n,m)：本质就是numeric，PGSQL会帮你转换
- numeric(n,m)：PGSQL本质的浮点类型
针对浮点类型的数据，底层使用的是 numeric
3）序列
构建方式使用方式如下：
-- 构建序列 create sequence forlanSchema.table_id_seq; -- 查询下一个值 select nextval('forlanSchema.table_id_seq'); -- 查询当前值 select currval('forlanSchema.table_id_seq');默认情况下，seqeunce的起始值是0，每次nextval递增1，最大值9223372036854775807
一般用作表的主键自增效果，如下：
-- 表自增 create table forlanSchema.xxx( id int8 default nextval('forlanSchema.table_id_seq'), name varchar(16) ); insert into forlanSchema.xxx (name) values ('xxx'); select * from forlanSchema.xxx;上面的写法很麻烦，其实PostgreSQL提供了序列的数据类型，可以在声明表结构时，直接指定序列的类型即可。
针对不同类型都有各自的序列自增
- smallserial
- serial
- bigserial
-- 表自增 create table forlanSchema.xxx( id bigserial, name varchar(16) ); insert into forlanSchema.xxx(name) values ('xxx');4）常见操作
操作符 描述 示例 结果 + 加 5 + 3 8 - 减 11 - 3 8 * 乘 2 * 3 8 / 除 10 / 3 3 % 取余 10 % 3 1 ^ 幂 2 ^ 3 8 |/ 平方根 |/ 36 6 @ 绝对值 @ -5 5 & 与 31 & 16 16 | 或 31|32 63 1 8 除了上面这些，还有一些函数计算，比如pi()，round(数值，位数)，floor()，ceil()
4、 字符串类型
字符串类型用的是最多的一种，PostgreSQL主要支持三种：
- character varying(n), varchar(n)，有限制的变长
- character(n), char(n)，定长，空格填充
- text，无限变长
常见字符串函数：
其他的函数，可以查看官方中文文档
5、 日期类型
在PostgreSQL中，核心的时间类型，就三个：
- timestamp（时间戳，覆盖 年月日时分秒）
- date（年月日）
- time（时分秒）
1）声明时间
只需要使用字符串正常的编写 yyyy-MM-dd HH:mm:ss 就可以转换为时间类型。
在字符串位置使用数据类型转换就可以了。
- 可以使用now作为当前系统时间
- 也可以使用current_timestamp的方式获取（推荐，默认东八区）
-- 直接查询now，无时区 select timestamp 'now'; -- 直接查询now，有时区 select time with time zone 'now' at time zone '08:00:00'
2）日期类型的运算
- 正常对date类型做+，-操作，默认单位就是天~
- date + time = timestamp~~~
select date '2011-11-11' + time '12:12:12' ;
- 可以针对timestamp使用interval的方式进行 +，-操作，在查询以时间范围为条件的内容时，可以使用
select timestamp '2011-11-11 12:12:12' + interval '1day' + interval '1minute' + interval '1month';
6、 布尔类型
布尔类型，可以存储三个值有：true，false，null
-- 布尔类型的约束没有那么强，true，false大小写随意，他会给你转，同时yes，no这种他也认识，但是需要转换 select true,false,'yes'::boolean,boolean 'no',True,FaLse,NULL::boolean;
布尔类型在做and和or的逻辑操作时，结果如下：
字段A 字段B a and b a or b true true true true true false false true true NULL NULL true false false false false false NULL false NULL NULL NULL NULL NULL 7、 枚举类型
枚举类型MySQL也支持，只是没怎么用，PostgreSQL同样支持这种数据类型
可以声明枚举类型作为表中的字段类型，用来约束字段的值。
-- 声明一个星期的枚举，值自然只有周一~周日。 create type week as enum ('Mon','Tues','Sun'); -- 声明一张表，表中的某个字段的类型是上面声明的枚举。 drop table test; create table test( id bigserial , weekday week ); insert into test (weekday) values ('Mon'); -- 这个会报错，因为不存在该枚举值 insert into test (weekday) values ('Fri');8、IP类型
PostgreSQL支持IP类型的存储，支持IPv4，IPv6这种，甚至Mac内种诡异类型也支持这种IP类型，可以在存储IP时，帮助做校验，其次也可以针对IP做范围查找。
IP校验效果，256已经超出了范围
IP也支持范围查找
9、 JSON&JSONB类型
JSON在MySQL8.x中也做了支持，但是MySQL支持的不好，因为JSON类型做查询时，基本无法给JSON字段做索引。
本质上JSON格式就是一个字符串，比如MySQL5.7不支持JSON的情况的下，使用text也可以，但是字符串类型无法校验JSON的格式，其次单独的字符串没有办法只获取JSON中某个key对应的value。
PostgreSQL支持JSON类型以及JSONB类型，两者使用基本无区别。
JSON和JSONB的区别：
- JSON类型无法构建索引，JSONB类型可以创建索引。
- JSON类型的数据中多余的空格会被存储下来。JSONB会自动取消多余的空格。
- JSON类型甚至可以存储重复的key，以最后一个为准。JSONB不会保留多余的重复key，覆盖更新。
- JSON会保留存储时key的顺序，JSONB不会保留原有顺序。
JSON中key对应的value的数据类型
JSON PGSQL String text number numeric boolean boolean null (none) [ {"name": "张三"}, {"name": { "info": "xxx" }} ]操作JSON：
- 四种JSON存储的类型：
select '8'::JSON,'null'::JSON,'"forlan"'::JSON,'true'::json; select '8'::JSONB,'null'::JSONB,'"forlan"'::JSONB,'true'::JSONB;
- JSON数组
select '[9,true,null,"我是字符串"]'::JSON;
- JSON对象
select '{"name": "张三","age": 23,"birthday": "2023-12-18","gender": null}'::json; select '{"name": "张三","age": 23,"birthday": "2023-12-18","gender": null}'::jsonb;- 构建表存储JSON
create table test( id bigserial, info json, infob jsonb ); insert into test (info,infob) values ('{"name": "张三" ,"age": 23,"birthday": "2011-11-11","gender": null}', '{"name": "张三" ,"age": 23,"birthday": "2011-11-11","gender": null}') select * from test;- 构建索引
create index jsonb_index on test(infob);
JSON还支持很多函数，具体可以查看9.15. JSON 函数和操作符
10、 复合类型
复合类型就好像Java中的一个对象，Java中有一个User，User和表做了一个映射，User中有个人信息对象。可以基于符合类型对映射上个人信息。
public class User{ private Integer id; private Info info; } class Info{ private String name; private Integer age; }按照上面的情况，将Info构建成一个复合类型
-- 构建复合类型，映射上Info create type info_type as (name varchar(32),age int); -- 构建表，映射User create table tb_user( id serial, info info_type ); -- 添加数据 insert into tb_user (info) values (('张三',23)); insert into tb_user (info) values (('露丝',233)); insert into tb_user (info) values (('jack',33)); insert into tb_user (info) values (('李四',24)); select * from tb_user;11、 数组类型
数组需要依赖其他类型，比如收获地址，可能有多个，就可以采用数组类型去修饰字符串。
PostgreSQL中，指定数组的方式就是[]，可以指定一维数组，也支持二维甚至更多维数组。
构建数组的方式：
drop table test; create table test( id serial, col1 int[], col2 int[2], col3 int[][] ); -- 构建表指定数组长度后，并不是说数组内容只有2的长度，可以插入更多数据 -- 甚至在你插入数据，如果将二维数组结构的数组扔到一维数组上，也可以存储。 -- 数组编写方式 select '{{how,are},{are,you}}'::varchar[]; select array[[1,2],[3,4]]; insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}','{7,8,9}'); insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}',array[[1,2],[3,4]]); insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}','{{1,2},{3,4}}'); select * from test;如果现在要存储字符串数组，如果存储的数组中有双引号怎么办，有大括号怎么办。
-- 如果存储的数组中的值，有单引号怎么办？ -- 使用两个单引号，作为一个单引号使用 select '{''how''}'::varchar[]; -- 如果存储的数组中的值，有逗号怎么办？ -- 不包起来的话，相对于分隔符了(PGSQL中的数组索引从1开始算，写0也是从1开始算。) -- 用双引号将数组的数据包起来~ select ('{"how,are"}'::varchar[])[2]; -- 如果存储的数组中的值，有双引号怎么办？ -- 如果要添加双引号，记得转义。 select ('{"\"how\",are"}'::varchar[])[1];数组的比较方式
-- 包含 select array[1,2] @> array[1]; -- 被包含 select array[1,2]
- 构建索引
- 构建表存储JSON
- JSON对象
- JSON数组
- 四种JSON存储的类型：
- 可以针对timestamp使用interval的方式进行 +，-操作，在查询以时间范围为条件的内容时，可以使用
免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理!
部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理!
图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们，邮箱：ciyunidc@ciyunshuju.com。本站只作为美观性配图使用,无任何非法侵犯第三方意图,一切解释权归图片著作权方,本站不承担任何责任。如有恶意碰瓷者,必当奉陪到底严惩不贷!
