PostgreSQL 数据类型

╰+攻爆jí腚メ 2023-10-15 22:27 47阅读 0赞

#### 文章目录 ####

*  PostgreSQL数据类型说明
 *  PostgreSQL数据类型使用
 *   *  单引号和双引号
     *  数据类型转换
     *  布尔类型
     *  数值类型
     *   *  整型
         *  浮点型
         *  序列
         *  数值的常见操作
     *  字符串类型
     *  日期类型
     *  枚举类型
     *  IP类型
     *  JSON&JSONB类型
     *  复合类型
     *  数组类型

## PostgreSQL数据类型说明 ##

PGSQL支持的类型特别丰富，大多数的类型和MySQL都有对应的关系

<table> 
 <thead> 
  <tr> 
   <th>名称</th> 
   <th>说明</th> 
   <th>对比MySQL</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>布尔类型</td> 
   <td>boolean，标准的布尔类型，只能存储true，false</td> 
   <td>MySQL中虽然没有对应的boolean，但是有替换的类型，数值的tinyint类型，和PGSQL的boolean都是占1个字节。</td> 
  </tr> 
  <tr> 
   <td>整型</td> 
   <td>smallint（2字节），integer（4字节），bigint（8字节）</td> 
   <td>跟MySQL没啥区别。</td> 
  </tr> 
  <tr> 
   <td>浮点型</td> 
   <td>decimal，numeric（和decimal一样一样的，精准浮点型），real（float），double precision（double），money（货币类型）</td> 
   <td>和MySQL基本也没区别，MySQL支持float，double，decimal。MySQL没有这个货币类型。</td> 
  </tr> 
  <tr> 
   <td>字符串类型</td> 
   <td>varchar(n)（character varying），char(n)（character），text</td> 
   <td>这里和MySQL基本没区别。 PGSQL存储的varchar类型，可以存储一个G。MySQL好像存储64kb。</td> 
  </tr> 
  <tr> 
   <td>日期类型</td> 
   <td>date（年月日），time（时分秒），timestamp（年月日时分秒）（time和timestamp可以设置时区）</td> 
   <td>没啥说的，和MySQL基本没区别。 MySQL有个datetime。</td> 
  </tr> 
  <tr> 
   <td>二进制类型</td> 
   <td>bytea-存储二进制类型</td> 
   <td>MySQL也支持，MySQL中是blob</td> 
  </tr> 
  <tr> 
   <td>位图类型</td> 
   <td>bit(n)（定长位图），bit varying(n)（可变长度位图）</td> 
   <td>就是存储0，1。MySQL也有，只是这个类型用的不多。</td> 
  </tr> 
  <tr> 
   <td>枚举类型</td> 
   <td>enum，跟Java的enum一样</td> 
   <td>MySQL也支持。</td> 
  </tr> 
  <tr> 
   <td>几何类型</td> 
   <td>点，直线，线段，圆…………</td> 
   <td>MySQL没有，但是一般开发也用不到</td> 
  </tr> 
  <tr> 
   <td>数组类型</td> 
   <td>在类型后，追加[]，代表存储数组</td> 
   <td>MySQL没有~~~</td> 
  </tr> 
  <tr> 
   <td>JSON类型</td> 
   <td>json（存储JSON数据的文本），jsonb（存储JSON二进制）</td> 
   <td>可以存储JSON，MySQL8.x也支持</td> 
  </tr> 
  <tr> 
   <td>ip类型</td> 
   <td>cidr（存储ip地址）</td> 
   <td>MySQL也不支持~</td> 
  </tr> 
 </tbody> 
</table>

更多数据类型见[官网][Link 1]

## PostgreSQL数据类型使用 ##

### 单引号和双引号 ###

在PGSQL中，写SQL语句时，单引号用来标识实际的值。双引号用来标识一个关键字，比如表名，字段名。

-- 单引号写具体的值，双引号类似MySQL的``标记，用来填充关键字
    -- 下面的葡萄牙会报错，因为葡萄牙不是关键字
    select 1.414,'卡塔尔',"葡萄牙";

### 数据类型转换 ###

第一种方式：只需要在值的前面，添加上具体的数据类型即可

-- 将字符串转成位图类型
    select bit '010101010101001';

第二种方式：也可以在具体值的后面，添加上 ::类型 ，来指定

-- 数据类型
    select '2011-11-11'::date;
    select '101010101001'::bit(20);
    select '13'::int;

第三种方式：使用CAST函数

-- 类型转换的完整写法
    select CAST(varchar '100' as int);

### 布尔类型 ###

布尔类型可以存储三个值，true，false，null。

布尔类型的约束没有那么强，true，false大小写随意，他会给你转，同时yes，no这种他也认识，但是需要转换

select true,false,'yes'::boolean,boolean 'no',True,FaLse,NULL::boolean;

boolean类型在做and和or的逻辑操作时的结果

<table> 
 <thead> 
  <tr> 
   <th>字段A</th> 
   <th>字段B</th> 
   <th>A and B</th> 
   <th>A or B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>true</td> 
   <td>true</td> 
   <td>true</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>true</td> 
   <td>false</td> 
   <td>false</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>true</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>false</td> 
   <td>false</td> 
   <td>false</td> 
   <td>false</td> 
  </tr> 
  <tr> 
   <td>false</td> 
   <td>NULL</td> 
   <td>false</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
 </tbody> 
</table>

### 数值类型 ###

#### 整型 ####

整型比较简单，主要就是三个：

*  smallint、int2：2字节
 *  integer、int、int4：4字节
 *  bigint、int8：8字节

一般就用integer，如果要存主键，比如雪花算法，那就bigint。如果要节约空间，根据情况可选smallint。

#### 浮点型 ####

浮点类型就关注2个

*  decimal(n,m)：本质就是numeric，PGSQL会帮你转换
 *  numeric(n,m)：PGSQL本质的浮点类型

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

#### 序列 ####

MySQL中的主键自增，是基于auto\_increment去实现。MySQL里没有序列的对象。PGSQL和Oracle十分相似，支持序列：sequence。序列的正常构建方式：

create sequence erdan.table_id_seq;
    -- 查询下一个值
    select nextval('erdan.table_id_seq');
    -- 查询当前值
    select currval('erdan.table_id_seq');

序列大多数的应用，是用作表的主键自增效果。

-- 表自增
    create table erdan.xxx(
        id int8 default nextval('erdan.table_id_seq'),
        name varchar(16)
    );
    insert into erdan.xxx (name) values ('xxx');
    select * from erdan.xxx;

PGSQL提供了序列的数据类型，可以在声明表结构时，直接指定序列的类型即可。

*  smallserial
 *  serial
 *  bigserial

-- 表自增（爽）
    create table erdan.yyy(
        id bigserial,   
        name varchar(16)
    );
    insert into erdan.yyy (name) values ('yyy');

在drop表之后，序列不会被删除，但是序列会变为不可用的状态。因为序列在使用serial去构建时，会绑定到指定表的指定列上。如果是单独构建序列，再构建表，使用传统方式实现，序列和表就是相对独立的。

#### 数值的常见操作 ####

针对数值下面实现加减乘除取余这5个操作

数值操作也提供了一些函数，比如pi()，round(数值，位数)，floor()，ceil()

### 字符串类型 ###

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

*  character（就是MySQL的char类型），定长字符串。（最大可以存储1G）
 *  character varying（varchar），可变长度的字符串。（最大可以存储1G）
 *  text（跟MySQL异常）长度特别长的字符串。

操作可以查看[官方说明][Link 2]

### 日期类型 ###

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

*  timestamp（时间戳，覆盖 年月日时分秒）
 *  date（年月日）
 *  time（时分秒）

在PGSQL中，声明时间只需要使用字符串正常的编写 `yyyy-MM-dd HH:mm:ss` 使用数据转换方式就可以转换为时间类型。

**当前系统时间** ：

*  可以使用now作为当前系统时间（没有时区的概念）

select timestamp 'now';
      -- 直接查询now，没有时区的概念
      select time with time zone 'now' at time zone '08:00:00'

*  也可以使用current\_timestamp的方式获取（推荐，默认东八区）

**日期类型的运算**:

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

![image.png][]

### IP类型 ###

PGSQL支持IP类型的存储，支持IPv4，IPv6这种，甚至Mac那种诡异类型也支持。这种IP类型，可以在存储IP时，帮助做校验，其次也可以针对IP做范围查找。IP校验的效果如下：

![image.png][image.png 1]

IP也支持范围查找：

![image.png][image.png 2]

### JSON&JSONB类型 ###

JSON在MySQL8.x中也做了支持，但是MySQL支持的不好，因为JSON类型做查询时，基本无法给JSON字段做索引。PGSQL支持JSON类型以及JSONB类型，两者使用基本没区别。

JSON和JSONB的区别：

*  JSON类型无法构建索引，JSONB类型可以创建索引。
 *  JSON类型的数据中多余的空格会被存储下来。JSONB会自动取消多余的空格。
 *  JSON类型甚至可以存储重复的key，以最后一个为准。JSONB不会保留多余的重复key（保留最后一个）。
 *  JSON会保留存储时key的顺序，JSONB不会保留原有顺序。

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

<table> 
 <thead> 
  <tr> 
   <th>JSON</th> 
   <th>PGSQL</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>String</td> 
   <td>text</td> 
  </tr> 
  <tr> 
   <td>number</td> 
   <td>numeric</td> 
  </tr> 
  <tr> 
   <td>boolean</td> 
   <td>boolean</td> 
  </tr> 
  <tr> 
   <td>null</td> 
   <td>(none)</td> 
  </tr> 
 </tbody> 
</table>

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

操作JSON：

*  上述的四种JSON存储的类型：

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

*  JSON数组

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

*  JSON对象![image.png][image.png 3]

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;

*  构建索引的效果![image.png][image.png 4]

create index json_index on test(info);
      create index jsonb_index on test(infob);

JSON还支持很多函数，可以直接查看[官方][Link 3]

### 复合类型 ###

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

[Link 1]: http://www.postgres.cn/docs/12/datatype.html
[Link 2]: http://www.postgres.cn/docs/12/functions-string.html
[image.png]: https://img-blog.csdnimg.cn/img_convert/1334ebc73e18b573e262b76f005afaf3.png
[image.png 1]: https://img-blog.csdnimg.cn/img_convert/445488a226d4ebc333455dcf0ba8d502.png
[image.png 2]: https://img-blog.csdnimg.cn/img_convert/488696b60a68b2327b023fc0836c3ab3.png
[image.png 3]: https://img-blog.csdnimg.cn/img_convert/0df6da16ced0a308f7159f714884c6fa.png
[image.png 4]: https://img-blog.csdnimg.cn/img_convert/bd72513f8fe84c17432c67bddc8ca813.png
[Link 3]: http://www.postgres.cn/docs/12/functions-json.html