类型介绍

类型分类

PostgreSQL支持的数据类型分类如表:

类型 说明 对比其他数据库
布尔类型 PostgreSQL支持SQL标准的boolean数据类型 与MySQL的BOOL、BOOLEAN类型相同,使用一字节存储空间
数值类型 整数类型有2字节的smallint、4字节的int、8字节的bigint,十进制精确类型有numeric,浮点类型有real和double precision。还有8字节的货币(money)类型 无MySQL的unsigned整数类型,也无MySQL 1字节长的tinyint整数类型和3字节长的medium int整数类型
字符类型 有varchar(n)、char(n)、text三种类型 PostgreSQL中的varchar(n)最大可以存储1GB,而MySQL中的varchar(n)最大只能是64KB。PostgreSQL中的text类型相当于MySQL中的LONGTEXT类型
二进制数据类型 只有一种bytea 对应MySQL的BLOB和LONGBLOB类型
位串类型 位串就是一串1和0的字符串,有bit(n)、bit varying(n)两种 N/A
日期和时间类型 有date、time、timestamp,而time和timestamp又分是否包括时区的两种类型 在PostgreSQL中,可以精确到秒以下,如毫秒。而MySQL的时间类型最多只能精确到秒,其日期时间的范围也与MySQL差异较大
枚举类型 枚举类型是一种包含一系列有序静态值集合的数据类型,等于某些编程语言中的enum类型 PostgreSQL使用枚举类型前需要先使用CREATE TYPE创建这个类型。MySQL也有枚举类型(ENUM)
几何类型 包括了点(point)、直线(line)、线段(lseg)、路径(path)、多边形(polygon)、圆(cycle)等类型 N/A
网络地址类型 有cidr、inet、macaddr三种类型 N/A
数组类型 可以存储一个数组 N/A
复合类型 可以把已有的简单类型组合成用户自定义的类型,就如C语言中的结构体一样 对应其他数据库的自定义类型
xml类型 可以存储XML数据的类型 N/A
json类型 可以存储json类型的数据 N/A
range类型 可以存储范围数据 N/A
对象标识符类型 PostgreSQL内部标识对象的类型,如oid类型、regproc类型、regclass类型等 N/A
伪类型 伪类型不能作为字段的数据类型,但是它可以用于声明一个函数的参数或者结果类型。有any、anyarray、anyelement、cstring、internal、language_handler、record、trigger、void、opaque N/A
其他类型 一些不好分类的类型都放到这里,如UUID类型、pg_Isn类型 N/A

为了提高SQL的兼容性,部分类型还有很多别名。

类型 别名
integer int、int4
smallint int2
bigint int8
char varying(n) varchar(n)
numeric(m,n) decimal(m,n)

类型输入与转换

对于一些简单的类型,如数字及字符串,使用通常的方法输入就可以了:

1
2
3
4
5
postgres=# select 1, 1.1421, 'hello world';
 ?column? | ?column? |  ?column?
----------|----------|-------------
        1 |   1.1421 | hello world
(1 row)

对于其他的复杂类型,可以使用“类型名”加上单引号括起来的类型值进行输入:

1
2
3
4
5
postgres=# select bit '11100011';
   bit
----------
 11100011
(1 row)

所有类型都可以使用上面的输入方法:

1
2
3
4
5
postgres=# select int '2' * int '5';
 ?column?
----------
       10
(1 row)

PostgreSQL支持使用标准SQL的类型转换函数cast进行类型转换:

1
2
3
4
5
postgres=# select cast('5' as int), cast('2018-09-12' as date);
 int4 |    date
------|------------
    5 | 2018-09-12
(1 row)

此外,PostgreSQL还有一种更简洁的类型转换方式,即双冒号转换:

1
2
3
4
5
postgres=# select '5'::int, '2018-09-12'::date;
 int4 |    date
------|------------
    5 | 2018-09-12
(1 row)

布尔类型

类型
bool true, false, ‘true’, ‘false’, ‘yes’, ‘no’(不区分大小写)
1
2
3
4
5
postgres=# select true, false, 'true'::bool, 'false'::bool, 'yes'::bool, 'no'::bool;
 bool | bool | bool | bool | bool | bool
------|------|------|------|------|------
 t    | f    | t    | f    | t    | f
(1 row)

SQL使用三值的布尔逻辑:TRUE、FALSE和NULL,其中NULL代表“未知“

a b a and b a or b
t t t t
t f f t
t null null t
f f f f
f null f null
null null null null
a not a
t f
f t
null null

布尔类型可以使用“IS”比较运算符:

1
2
3
4
5
6
expression is true
expression is not true
expression is false
expression is not false
expression is unknown
expression is not unknown

数值类型

类型名 存储空间 范围
smallint 2字节 -2^15~2^15-1
integer 4字节 -2^31~2^31-1
bigint 8字节 -2^63~2^63-1
numeric 变长 无限制
real 4字节 6位十进制数字精度
double precision 8字节 15位十进制数字精度
serial 4字节 1~2^31-1
bigserial 8字节 1~2^63-1

精确的小数类型