【postgresql 基础入门】数据类型介绍,整型,字符串,浮点数,日期时间类型特点,精度及表示范围,选择合适类型来提升性能

数据类型

专栏内容

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

系列文章

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

概述


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

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

类型总览


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

类型名 别名 描述
bigint int8 有符号整型,占8字节
bigserial serial8 自增的8字节整型
bit [ (n) ] 固定长度的二进制bit位字符串
bit varying [ (n) ] varbit [ (n) ] 可变长度的二进制bit位字符串
boolean bool 布尔类型 (true/false)
box rectangular box on a plane
bytea 二进制数据,类似于二进制位的数组
character [ (n) ] char [ (n) ] 定长的字符串类型
character varying [ (n) ] varchar [ (n) ] 变长的字符串类型
cidr IPv4 or IPv6 ***work address
circle circle on a plane
date calendar date (year, month, day)
double precision float8 双精度浮点数,占8字节
i*** IPv4 or IPv6 host address
integer int, int4 有符号4字节整型
interval [ fields ] [ § ] time span
json textual JSON data
jsonb binary JSON data, de***posed
line infinite line on a plane
lseg line segment on a plane
macaddr MAC (Media A***ess Control) address
macaddr8 MAC (Media A***ess Control) address (EUI-64 format)
money currency amount
numeric [ (p, s) ] decimal [ (p, s) ] 可以指定精度的类型
path geometric path on a plane
pg_lsn PostgreSQL Log Sequence Number
pg_snapshot user-level transaction ID snapshot
point geometric point on a plane
polygon closed geometric path on a plane
real float4 单精度浮点数占4字节
smallint int2 有符号两字节整型
smallserial serial2 自增的两字节整型
serial serial4 自增的4字节整型
text 可变长的字符串类型
time [ § ] [ without time zone ] time of day (no time zone)
time [ § ] with time zone timetz time of day, including time zone
timestamp [ § ] [ without time zone ] date and time (no time zone)
timestamp [ § ] with time zone timestamptz date and time, including time zone
tsquery text search query
tsvector text search document
txid_snapshot user-level transaction ID snapshot (deprecated; see pg_snapshot)
uuid 全局唯一标识
xml XML 数据类型

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

整型类型


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

  • smallint,占2字节,是有符号类型,最小值-215,最大值为215-1
  • integer,占4字节,有符号类型,最小值-231, 最大值为2^31-1
  • bigint, 占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)

可以看到,不管我们用什么写法,数据库中只显示tf

为了避免有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:0024: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.***
如有错误或者疏漏欢迎指出,互相学习。

注:未经同意,不得转载!

转载请说明出处内容投诉
CSS教程_站长资源网 » 【postgresql 基础入门】数据类型介绍,整型,字符串,浮点数,日期时间类型特点,精度及表示范围,选择合适类型来提升性能

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买