PostgreSQL数据类型

2019-01-22 约 1722 字预计阅读 4 分钟次阅读

类型介绍

类型分类

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

目录