【postgresql 基础入门】数据类型介绍，整型，字符串，浮点数，日期时间类型特点，精度及表示范围，选择合适类型来提升性能_scale6 must round to an absolute value less than 1

数据类型

​专栏内容：

• postgresql内核源码分析
• 手写数据库toadb
• 并发编程

个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

系列文章

• 入门准备
• postgrersql基础架构
• 快速使用
• 初始化集群
• 数据库服务管理
• psql客户端使用
• pgAdmin图形化客户端
• 数据库的使用
• 创建数据库
• 数据库操作
• 表的使用
• 表的创建
• 表的操作
• 数据查询
• 数据查询
• 多表联合查询
• 数据操作
• 插入数据的方式

概述

---

postgresql 数据库作为一款被各领域广泛使用的开源数据库，有丰富的数据类型，像其它编程语言一样，在开始使用编程语言时，先要了解它的数据类型，与自然语言表达的数据能够一一对应起来。

本文将给大家分享postgresql现有的数据类型，对一些常用的数据类型进行详细介绍。

类型总览

---

在postgresql手册中列出了大概43种类型，列表如下：

其中有一些我们常用的，如int, varchar等，还有不常用到的pg_lsn等特殊的类型，下面对于我们常用的几种类型进行详细的说明。

整型类型

---

整型用于存储一个整数，按存储的字节分为以下几种：

• smallint，占2字节，是有符号类型，最小值-2¹⁵，最大值为2¹⁵-1
• integer，占4字节，有符号类型，最小值-2³¹, 最大值为2^31-1
• bigint, 占8字节，有符号类型，最小值-2⁶³, 最大值为2⁶³-1

这里并没有提供无符号的类型，当存储的数据超出类型范围时，就会报错。
在选择类型时，要根据自己的数据的范围选择合适的类型，如果选择过大范围的类型，不仅会占用过多的空间，还会影响数据读写的性能。

浮点类型

---

浮点数值的存储类型，在postgresql主要有以下三种：

• real，对应单精度类型，占4字节，可以记录范围为 1E-37 到 1E+37，最大精度为小数点后6位，第7位是近似值；
• double precision，对于双精度类型，占8字节，可以记录范围为 1E-307 到 1E+308，最大精度为小数点后15位，同样16位是近似值；
• numeric(precision,scale)，任意精度类型，precision指定总的有效位数，scale指定小数部分的位数；

在postgresql中与SQL标准的兼容，也有float类型，写作float(n),n 可以指定精度的二进制位数。 当写为float时，默认对应double precision双精度类型；而float(1)到float(24)对应的是单精度real类型，float(25)到float(53)对应就是双精度；实际按上面三种基础类型处理。

浮点数类型取值，除了正常数字之外，还定义了三种特殊的值：

• Infinity，正的无穷大；无穷大这个数字是数学中的一个概念，符合推理：无穷 + 无穷=无穷；无穷-无穷=NaN；
• -Infinity，负的无穷大；
• NaN，not a number,不是一个数字；在IEEE 754标准中，NaN不等与其它数比较，包括自己；但是在postgresql中为了索引处理方便，两个NaN是相等的，同时NaN大于任何非NaN值。

下面通过实践来看一下以上用法：

先来创建一张表，含有三种基本类型：

postgres=# create table floating(a real,b double precision, c numeric(10,6));
CREATE TABLE
1
2

插入数据，并且含有特殊的值：

postgres=# insert into floating values('Infinity','NaN',10.2023333);
INSERT 0 1
postgres=# insert into floating values(2.8,1.0,1555555.2023333);
ERROR:  numeric field overflow
DETAIL:  A field with precision 10, scale 6 must round to an absolute value less than 10^4.
postgres=# insert into floating values(2.8,1.0,155.2023333);
INSERT 0 1
postgres=# select * from floating ;
    a     |  b  |     c
----------+-----+------------
 Infinity | NaN |  10.202333
      2.8 |   1 | 155.202333
(2 rows)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

在插入数据时，有一个报错，超过了numeric(10,6)的范围，可以看到它不会自动截短处理；
查询可以看到特殊值也可以显示出来。

下面我们进行几个条件判断的处理：

postgres=# select * from floating where b > 10;
    a     |  b  |     c
----------+-----+-----------
 Infinity | NaN | 10.202333
(1 row)

postgres=# select * from floating where a < 'NaN';
    a     |  b  |     c
----------+-----+------------
 Infinity | NaN |  10.202333
      2.8 |   1 | 155.202333
(2 rows)
1
2
3
4
5
6
7
8
9
10
11
12

可以看到第一条查询，NaN是大于任何值的，从第二条查询可以看出NaN居然是大于无穷大的一个特殊值，所以在使用时要特别注意。

字符类型

---

字符类型几乎无处不在，在postgresql提供了以下几种类型：

• character(n),也可以写作char(n)，它是定长的类型，也就是占用的存储空间是固定大小，大小由n来指定，它是正整数；当不指定n时，占一个字符，等同与char(1);
• character varying(n),也可以写作varchar(n)，它是变长的类型，也就是占用的存储空间是实际字符串的长度，而n用来限制最大长度；
• text，变长类型，不限制字符串的长度；

这里需要特别说明几点：

• n 的取值最大值为 10485760， 也就是十六进制的0xA00000；
• varchar 不指定n时，与text相同，而指定n时，只是会增加长度的检查；
• 在postgresql 中这三种字符类型的性能相同，而后两者更省空间；
• 对于超出n限制的字符串，当在显示类型转换到字符串时，会自动截断；而当字符串中超出部分都为空格时，也会自动将尾部连续空格截断；

下面举例来说明。

超过最大值

n的最大值限制为0xA00000，如果超过就会失败。

postgres=# create table ch(sname char(1070596096));
ERROR:  length for type char cannot exceed 10485760
LINE 1: create table ch(sname char(1070596096));
1
2
3

三种类型比较

首先创建含有三种类型的表str，其中char实际存储一个字节；

postgres=# create table str(a char,b varchar(5),c text);
CREATE TABLE
1
2

插入符合长度的字符串，可以看到text没有限制。

postgres=# insert into str values('1','12345','123456789');
INSERT 0 1
1
2

插入尾部带有空格的字符串，非空格字符长度不超过限制，可以看到空格会被截断，也会插入成功；

postgres=# insert into str values('2   ','12345   ','1234    56789');
INSERT 0 1
postgres=# select * from str;
 a |   b   |       c
---+-------+---------------
 1 | 12345 | 123456789
 2 | 12345 | 1234    56789
(2 rows)

postgres=# insert into str values('3   ',' 32345   ','1234    56789');
ERROR:  value too long for type character varying(5)
1
2
3
4
5
6
7
8
9
10
11

而对于字符中间的空格，也会被算为有效字符串中，所以再次在字符头部加入空格，就会超过varchar(5)的长度限制。

布尔类型

---

布尔类型也是我们常用的类型，经常表示两种状态，它实际存储时会有三种值的情况：

• true，为真时的情况；
• false，为假时的情况；
• NULL, 没有赋值时的情况；

在给布尔类型赋值时，也有多种写法：

• true，也可以用’1’，’true’,‘t’,‘yes’,‘y’ 一共6种写法；
• false, 也可以用’0’,‘false’,‘f’,‘no’,‘n’ 对应的也有6种写法；

当然在实际应用中，整体采用一种对应的写法规则，避免开发的混乱；
下面我们来举一个例子；

postgres=# create table switchState(sid int primary key, sstate boolean);
CREATE TABLE
postgres=# insert into switchstate values(1,true),(2,'true'),(3,'t'),(4,'no'),(5,'n'),(6,'0');
INSERT 0 6
postgres=# select * from switchstate ;
 sid | sstate
-----+--------
   1 | t
   2 | t
   3 | t
   4 | f
   5 | f
   6 | f
(6 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

可以看到，不管我们用什么写法，数据库中只显示t和f。

为了避免有NULL的情况出现时，可以设置boolean类型的默认值，没有赋值时就会采用默认值。

postgres=# drop table switchstate ;
DROP TABLE
postgres=# create table switchState(sid int primary key, sstate boolean default false);
CREATE TABLE
postgres=# insert into switchstate values(1);
INSERT 0 1
postgres=# select * from switchstate ;
 sid | sstate
-----+--------
   1 | f
(1 row)

1
2
3
4
5
6
7
8
9
10
11
12

日期时间类型

---

日期时间是经常会用到的，因为不同历法，时间计法不同，以及显示方式的不同，变化非常多样，本文作为基础入门，只列出几种常用的用法，不再深入探讨。

与日期时间相关的类型有：

• timestamp with time zone，含有日期和时间，占8个字节，带有时区；最小精度到微秒，可以表示的日期范围为公元前4713年到公元294276年，非常遥远；

• timestamp [without time zone]，与上一类型一样，只是不带时区；

• date, 日期类型，表示年月日，占4个字节；默认格式为yyyy-mm-dd, 最小单位为天，可以表示的日期范围为公元前4713年到公元294276年；

• time with time zone ，表示一天中的时间，不带日期；占12个字节，默认格式为HH:MI:SS，表示范围为00:00:00到24:00:00，最小精度到微秒，同样带有时区；

• time [without time zone]，与上一类型一样，只是不带时区，所以占8字节；

• interval，时间间隔类型，占16字节，间隔时间范围可以为-178000000年到178000000年，最小精度为微秒；

下面我们来使用一下日期和时间的类型，首先创建一张表，带有日期，日期时间，时间类型。

postgres=# create table tbl_datetime(day date, year timestamp with time zone, hour time);
CREATE TABLE
postgres=# insert into tbl_datetime values(now(), now(), CURRENT_TIME);
INSERT 0 1
postgres=# select * from tbl_datetime ;
    day     |             year              |      hour
------------+-------------------------------+-----------------
 2024-03-13 | 2024-03-13 22:53:13.555858+08 | 22:53:13.555858
(1 row)
1
2
3
4
5
6
7
8
9

然后插入数据，这里使用了now()来获取当前的日期和时间，用CURRENT_TIME获取当前的时间；

查询可以看到默认格式的显示，当前时区为东8区。

总结

---

在本章节中，介绍了postgresql数据库中的数据类型，同时详细对常用的整型，浮点，字符串，日期时间类型进行了介绍，占用的存储空间，表示的范围，以及它们的精度，尤其对于字符类型，最好采用变长的类型来减少存储空间，从而提升查询效率。

六、结尾

---

非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！

作者邮箱：study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出，互相学习。

注：未经同意，不得转载！