PostgreSQL教程(二)九大类型

发布于:2025-03-17 ⋅ 阅读:(17) ⋅ 点赞:(0)

布尔类型

布尔类型简单的丫批,可以存储三个值,true,false,null

-- 布尔类型的约束没有那么强,true,false大小写随意,他会给你转,同时yes,no这种他也认识,但是需要转换
select true,false,'yes'::boolean,boolean 'no',True,FaLse,NULL::boolean;

数值类型

整型

整型比较简单,主要就是三个:

  • smallint、int2:2字节

  • integer、int、int4:4字节

  • bigint、int8:8字节

正常没啥事就integer,如果要存主键,比如雪花算法,那就bigint。空间要节约,根据情况smallint

浮点型

浮点类型就关注2个(其实是一个)

  • decimal(n,m):本质就是numeric,PGSQL会帮你转换

  • numeric(n,m):PGSQL本质的浮点类型

针对浮点类型的数据,就使用 numeric

序列

序列大多数的应用,是用作表的主键自增效果。默认情况下,seqeunce的起始值是0,每次nextval递增1,最大值9223372036854775807

MySQL中的主键自增,是基于auto_increment去实现。MySQL里没有序列的对象。

PGSQL和Oracle十分相似,支持序列:sequence。

PGSQL可没有auto_increment。序列的正常构建方式:

create sequence laozheng.table_id_seq;
-- 查询下一个值
select nextval('laozheng.table_id_seq');
-- 查询当前值
select currval('laozheng.table_id_seq');
数值的常见操作

针对数值咱们可以实现加减乘除取余这5个操作,另外还有

字符串类型

字符串类型用的是最多的一种,在PGSQL里,主要支持三种:

  • character(就是MySQL的char类型),定长字符串。(最大可以存储1G)

  • character varying(varchar),可变长度的字符串。(最大可以存储1G)

  • text(跟MySQL异常)长度特别长的字符串。

操作没什么说的,但是字符串常见的函数特别多。

字符串的拼接一要要使用||来拼接。其他的函数,可以查看 http://www.postgres.cn/docs/12/functions-string.html

日期类型

在PGSQL中,核心的时间类型,就三个。

  • timestamp(时间戳,覆盖 年月日时分秒)

  • date(年月日)

  • time(时分秒)

在PGSQL中,声明时间的方式:只需要使用字符串正常的编写 yyyy-MM-dd HH:mm:ss 就可以转换为时间类型。直接在字符串位置使用之前讲到的数据类型转换就可以了。

当前系统时间 :

  • 可以使用now作为当前系统时间(没有时区的概念)

-- 直接查询now,没有时区的概念
select timestamp 'now';
-- 也可以使用current_timestamp的方式获取(推荐,默认东八区)
select time with time zone 'now' at time zone '08:00:00'

日期类型的运算

  • 正常对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';

枚举类型

枚举类型MySQL也支持,只是没怎么用,PGSQL同样支持这种数据类型,可以声明枚举类型作为表中的字段类型,这样可以无形的给表字段追加诡异的规范。

-- 声明一个星期的枚举,值自然只有周一~周日。
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');

IP类型

PGSQL支持IP类型的存储,支持IPv4,IPv6这种,甚至Mac内种诡异类型也支持,这种IP类型,可以在存储IP时,帮助做校验,其次也可以针对IP做范围查找

JSON&JSONB类型

JSON在MySQL8.x中也做了支持,但是MySQL支持的不好,因为JSON类型做查询时,基本无法给JSON字段做索引。

PGSQL支持JSON类型以及JSONB类型。JSON和JSONB的使用基本没区别。

本质上JSON格式就是一个字符串,比如MySQL5.7不支持JSON的情况的下,使用text也可以,但是字符串类型无法校验JSON的格式,其次单独的字符串没有办法只获取JSON中某个key对应的value。

JSON和JSONB的区别:

  • JSON类型无法构建索引,JSONB类型可以创建索引。

  • JSON类型的数据中多余的空格会被存储下来。JSONB会自动取消多余的空格。

  • JSON类型甚至可以存储重复的key,以最后一个为准。JSONB不会保留多余的重复key(保留最后一个)。

  • JSON会保留存储时key的顺序,JSONB不会保留原有顺序。

JSON中key对应的value的数据类型

[
  {"name": "张三"},
  {"name": {
      "info": "xxx"
    }}
]

操作JSON

select '9'::JSON,'null'::JSON,'"laozheng"'::JSON,'true'::json;
select '9'::JSONB,'null'::JSONB,'"laozheng"'::JSONB,'true'::JSONB;

-- JSON数组
select '[9,true,null,"我是字符串"]'::JSON;

-- JSON对象
select '{"name": "张三","age": 23,"birthday": "2011-11-11","gender": null}'::json;
select '{"name": "张三","age": 23,"birthday": "2011-11-11","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 json_index on test(info);
create index jsonb_index on test(infob);

复合类型

复合类型就像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;

数组类型

数组还是要依赖其他类型,比如在设置住址,住址可能有多个住址,可以采用数组类型去修饰字符串。

PGSQL中,指定数组的方式就是[],可以指定一维数组,也支持二维甚至更多维数组。

构建数组的方式:

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] <@ array[1,2,4];
-- 是否有相同元素
select array[2,4,4,45,1] && array[1];