PostgreSQL常用数据类型

  • 一、数值类型
  • 二、货币类型
  • 三、字符类型
  • 四、日期/时间类型
  • 五、JSON类型
  • 创建JSON类型的表
  • 插入JSON数据
  • 查询JSON数据
  • 六、其他数据类型
  • 总结


一、数值类型

类型名

存储长度

描述

范围

smalint

2字节

小范围整数

-2^15 ~ 2^15 -1

intefer

4字节

常用的整数

-2^31 ~ 2^31 -1

bigint

8字节

大范围整数

-2^63 ~ 2^63 -1

decimal

可变长

用户指定的精度

小数点前131072位；小数点后16383位

smailserial

2字节

自增小范围整数

1 ~ 2^15 -1

serial

4字节

自增整数

1 ~ 2^31 -1

bigserial

8字节

自增大范围整数

1 ~ 2^63 -1

二、货币类型

money 类型存储带有固定小数精度的货币金额。

numeric、int 和 bigint类型的值可以转换为 money ， 浮点数处理货币类型可能存在舍入错误。

类型名

存储长度

描述

范围

money

8字节

货币金额

-92233720368547758.08 到 +92233720368547758.07

注意：bigint的范围为-2^63 ~ 2^63 - 1
其中：
2^63 = 9223372036854775808
2^63 - 1 = 9223372036854775807
所以货币类型就是将整型的后两位转换成了小数点后两位，与Java中的高精度计数原理相同。

三、字符类型

类型名

存储长度

描述

varchar(n)

n

变长，长度有限制

char(n)

n

定长，不足的字符补空白

text

~

变长，无长度限制

四、日期/时间类型

类型名

存储长度

描述

timestamp[ (p) ] [ without time zone ]

8字节

日期和时间（无时区）

timestamp[ (p) ] with time zone

8字节

日期和时间（有时区）

date

4字节

只用于日期

time[ (p) ] [without time zone]

8字节

只用于一日内时间

time[ (p) ] with time zone

12字节

只用于一日内时间，带时区

注意： 可选精度值p 以指明秒域中小数部分的位数。

五、JSON类型

JSON 数据类型可以用来存储JSON数据，JSON数据类型更有利于检查每个存储的数值是可用的JSON值。

JSON数据类型在存储时类似于text文本存储，但是其增添了获取JSON值内容的功能。

通过操作符进行查询，可以查询到JSON中键对应的值。

创建JSON类型的表

CREATE TABLE test(
	id serial NOT NULL PRIMARY KEY ,
	info json NOT NULL
);

test表包含两列：id为主键（自增），info为存储json数据的列。

插入JSON数据

INSERT INTO test(info)
VALUES(
	'{"student":"lmz","num":"20191110101"}'
);

插入数据，student名为lmz，学号为20191110101

查询JSON数据

查询信息为整个JSON数据。

SELECT info
FROM test;

查询JSON中学生信息作为键。

SELECT info -> 'student'
FROM test ;

查询值为"lmz" 

SELECT info ->> 'student'
FROM test ;

查询键为值的文本： lmz

即：-> 操作符表示查出的数据依旧为JSON数据，->> 查出的数据为具体的值，此时的值已经表述为字符串的形式了。

六、其他数据类型

除了上述常用的基本类型外，PostgreSQL还有枚举类型、几何类型、网络地址类型、位串类型、文本搜索类型、UUID类型、数组类型等等。

这些数据类型比MySQL的数据类型更具有多样性，可以应用于更多的不同的场景，让数据库更增加了灵活性。

总结

PostgreSQL比MySQL新增了更多的数据类型，丰富了类型库，可以在不同的场景下选用不同的类型，但是也要注意数据库与Java格式的匹配问题，PostgreSQL的日期类型虽然更丰富，但是在转换为Java类型时可能会出现格式不匹配问题，所以在开发时要注意。