数据类型
专栏内容：
postgresql内核源码分析手写数据库toadb并发编程
个人主页：我的主页 管理社区：开源数据库 座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.
系列文章
入门准备postgrersql基础架构快速使用初始化集群数据库服务管理psql客户端使用pgAdmin图形化客户端数据库的使用创建数据库数据库操作表的使用表的创建表的操作数据查询数据查询多表联合查询数据操作插入数据的方式
文章目录
数据类型系列文章概述 类型总览 整型类型 浮点类型 字符类型 超过最大值 三种类型比较
布尔类型 日期时间类型 总结 六、结尾
概述
postgresql 数据库作为一款被各领域广泛使用的开源数据库，有丰富的数据类型，像其它编程语言一样，在开始使用编程语言时，先要了解它的数据类型，与自然语言表达的数据能够一一对应起来。
本文将给大家分享postgresql现有的数据类型，对一些常用的数据类型进行详细介绍。
类型总览
在postgresql手册中列出了大概43种类型，列表如下：
类型名别名描述bigintint8有符号整型，占8字节bigserialserial8自增的8字节整型bit [ (n) ]固定长度的二进制bit位字符串bit varying [ (n) ]varbit [ (n) ]可变长度的二进制bit位字符串booleanbool布尔类型 (true/false)boxrectangular box on a planebytea二进制数据，类似于二进制位的数组character [ (n) ]char [ (n) ]定长的字符串类型character varying [ (n) ]varchar [ (n) ]变长的字符串类型cidrIPv4 or IPv6 network addresscirclecircle on a planedatecalendar date (year, month, day)double precisionfloat8双精度浮点数，占8字节inetIPv4 or IPv6 host addressintegerint, int4有符号4字节整型interval [ fields ] [ § ]time spanjsontextual JSON datajsonbbinary JSON data, decomposedlineinfinite line on a planelsegline segment on a planemacaddrMAC (Media Access Control) addressmacaddr8MAC (Media Access Control) address (EUI-64 format)moneycurrency amountnumeric [ (p, s) ]decimal [ (p, s) ]可以指定精度的类型pathgeometric path on a planepg_lsnPostgreSQL Log Sequence Numberpg_snapshotuser-level transaction ID snapshotpointgeometric point on a planepolygonclosed geometric path on a planerealfloat4单精度浮点数占4字节smallintint2有符号两字节整型smallserialserial2自增的两字节整型serialserial4自增的4字节整型text可变长的字符串类型time [ § ] [ without time zone ]time of day (no time zone)time [ § ] with time zonetimetztime of day, including time zonetimestamp [ § ] [ without time zone ]date and time (no time zone)timestamp [ § ] with time zonetimestamptzdate and time, including time zonetsquerytext search querytsvectortext search documenttxid_snapshotuser-level transaction ID snapshot (deprecated; see pg_snapshot)uuid全局唯一标识xmlXML 数据类型
其中有一些我们常用的，如int, varchar等，还有不常用到的pg_lsn等特殊的类型，下面对于我们常用的几种类型进行详细的说明。
整型类型
整型用于存储一个整数，按存储的字节分为以下几种：
smallint，占2字节，是有符号类型，最小值-215，最大值为215-1integer，占4字节，有符号类型，最小值-231, 最大值为2^31-1bigint, 占8字节，有符号类型，最小值-263, 最大值为263-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
插入数据，并且含有特殊的值：
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)
在插入数据时，有一个报错，超过了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)
可以看到第一条查询，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));
三种类型比较
首先创建含有三种类型的表str，其中char实际存储一个字节；
postgres=# create table str(a char,b varchar(5),c text);
CREATE TABLE
插入符合长度的字符串，可以看到text没有限制。
postgres=# insert into str values('1','12345','123456789');
INSERT 0 1
插入尾部带有空格的字符串，非空格字符长度不超过限制，可以看到空格会被截断，也会插入成功；
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)
而对于字符中间的空格，也会被算为有效字符串中，所以再次在字符头部加入空格，就会超过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)
可以看到，不管我们用什么写法，数据库中只显示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)
日期时间类型
日期时间是经常会用到的，因为不同历法，时间计法不同，以及显示方式的不同，变化非常多样，本文作为基础入门，只列出几种常用的用法，不再深入探讨。
与日期时间相关的类型有：
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)
然后插入数据，这里使用了now()来获取当前的日期和时间，用CURRENT_TIME获取当前的时间；
查询可以看到默认格式的显示，当前时区为东8区。
总结
在本章节中，介绍了postgresql数据库中的数据类型，同时详细对常用的整型，浮点，字符串，日期时间类型进行了介绍，占用的存储空间，表示的范围，以及它们的精度，尤其对于字符类型，最好采用变长的类型来减少存储空间，从而提升查询效率。
六、结尾
非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！
作者邮箱：study@senllang.onaliyun.com 如有错误或者疏漏欢迎指出，互相学习。
注：未经同意，不得转载！
好文推荐
您阅读本篇文章共花了：
发表评论