PostgreSQL数据类型

不要慨叹生活底痛苦！---慨叹是弱者...

Author：李东阳

PostgreSQL的数据类型是很多的，而且用户可以使用命令CREATE TYPE来添加新的数据类型。所以我就挑了一些来做笔记，想要了解更多可以看官方文档。

数据类型表：

名字	别名	描述
bigint	int8	有符号的8字节整数
bigserial	serial8	自动增长的8字节整数
bit [ (*n*) ]		定长位串
bit varying [ (*n*) ]	varbit	变长位串
boolean	bool	逻辑布尔值（真/假）
box		平面上的普通方框
bytea		二进制数据（“字节数组”）
character [ (*n*) ]	char [ (*n*) ]	定长字符串
character varying [ (*n*) ]	varchar [ (*n*) ]	变长字符串
cidr		IPv4或IPv6网络地址
circle		平面上的圆
date		日历日期（年、月、日）
double precision	float8	双精度浮点数（8字节）
inet		IPv4或IPv6主机地址
integer	int, int4	有符号4字节整数
interval [ *fields* ] [ (*p*) ]		时间段
json		文本 JSON 数据
jsonb		二进制 JSON 数据，已分解
line		平面上的无限长的线
lseg		平面上的线段
macaddr		MAC（Media Access Control）地址
macaddr8		MAC (Media Access Control) 地址 (EUI-64 格式)
money		货币数量
numeric [ (*p*, *s*) ]	decimal [ (*p*, *s*) ]	可选择精度的精确数字
path		平面上的几何路径
pg_lsn		PostgreSQL日志序列号
point		平面上的几何点
polygon		平面上的封闭几何路径
real	float4	单精度浮点数（4字节）
smallint	int2	有符号2字节整数
smallserial	serial2	自动增长的2字节整数
serial	serial4	自动增长的4字节整数
text		变长字符串
time [ (*p*) ] [ without time zone ]		一天中的时间（无时区）
time [ (*p*) ] with time zone	timetz	一天中的时间，包括时区
timestamp [ (*p*) ] [ without time zone ]		日期和时间（无时区）
timestamp [ (*p*) ] with time zone	timestamptz	日期和时间，包括时区
tsquery		文本搜索查询
tsvector		文本搜索文档
txid_snapshot		用户级别事务ID快照
uuid		通用唯一标识码
xml		XML数据

1、数字类型

数字类型由2、4或8字节的整数以及4或8字节的浮点数和可选精度小数组成。

名字	存储尺寸	描述	范围
smallint	2字节	小范围整数	-32768 to +32767
integer	4字节	整数的典型选择	-2147483648 to +2147483647
bigint	8字节	大范围整数	-9223372036854775808 to +9223372036854775807
decimal	可变	用户指定精度，精确	最高小数点前131072位，以及小数点后16383位
numeric	可变	用户指定精度，精确	最高小数点前131072位，以及小数点后16383位
real	4字节	可变精度，不精确	6位十进制精度
double precision	8字节	可变精度，不精确	15位十进制精度
smallserial	2字节	自动增加的小整数	1到32767
serial	4字节	自动增加的整数	1到2147483647
bigserial	8字节	自动增长的大整数	1到9223372036854775807

1.1、整数类型

类型smallint、integer和bigint存储各种范围的整数。

常用的类型是integer，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用 smallint类型。而只有在integer的范围不够的时候才使用bigint。

1.2、任意精度数字

类型numeric可以存储非常多位的数字。不过，numeric类型上的算术运算比整数类型或者浮点数类型要慢很多。

一个numeric的比例是到小数部分的位数，numeric的精度是整个数字里全部位的数目，也就是小数点两边的位数目。因此数字 23.5141 的精度为6而比例为4。你可以认为整数的比例为零。

numeric类型的声明：

NUMERIC(precision, scale)

精度必须为正数，比例可以为零或者正数。

除了普通的数字值之外，numeric类型允许特殊值NaN， 表示“不是一个数字”。任何在 NaN上面的操作都生成另外一个NaN。 如果在 SQL 命令里把这些值当作一个常量写，你必须在其周围放上单引号，例如UPDATE table SET x = 'NaN'。

1.3、浮点类型

数据类型real和double precision是不准确的、变精度的数字类型，也就是浮点数类型。

浮点类型还有几个特殊值：

• Infinity：正无穷大

• -Infinity：负无穷小

• NaN：不是一个数字

PostgreSQL还支持 SQL 标准表示法float和float(*p*)用于声明非精确的数字类型。p指定以*二进制*位表示的最低可接受精度

1.4、序数类型

smallserial、serial和bigserial类型不是真正的类型，它们只是为了创建唯一标识符列而存在的方便符号（类似其它一些数据库中支持的AUTO_INCREMENT属性）。

2、货币类型

money类型存储固定小数精度的货币数字。

名字	存储尺寸	描述	范围
money	8 bytes	货币额	-92233720368547758.08到+92233720368547758.07

3、字符类型

名字	描述
character varying(*n*), varchar(*n*)	有限制的变长
character(*n*), char(*n*)	定长，空格填充
text	无限变长

SQL定义了两种基本的字符类型： character varying(*n*)和character(*n*)， 其中n是一个正整数。两种类型都可以存储最多n个字符长的串。试图存储更长的串到这些类型的列里会产生一个错误， 除非超出长度的字符都是空白，这种情况下该串将被截断为最大长度。 如果要存储的串比声明的长度短，类型为character的值将会用空白填满；而类型为character varying的值将只是存储短些的串。

提示

这三种类型之间没有性能差别，只不过是在使用填充空白的类型的时候需要更多存储尺寸，以及在存储到一个有长度约束的列时需要少量额外CPU周期来检查长度。虽然在某些其它的数据库系统里，character(*n*)有一定的性能优势，但在PostgreSQL里没有。事实上，character(*n*)通常是这三种类型之中最慢的一个，因为它需要额外的存储开销。在大多数情况下，应该使用text或者character varying。

字符类型的使用

CREATE TABLE test1 (a character(4));
INSERT INTO test1 VALUES ('ok');
SELECT a, char_length(a) FROM test1; -- (1)
  a   | char_length
------+-------------
 ok   |           2

CREATE TABLE test2 (b varchar(5));
INSERT INTO test2 VALUES ('ok');
INSERT INTO test2 VALUES ('good      ');
INSERT INTO test2 VALUES ('too long');
ERROR:  value too long for type character varying(5)
INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation
SELECT b, char_length(b) FROM test2;
   b   | char_length
-------+-------------
 ok    |           2
 good  |           5
 too l |           5

4、二进制数类型

bytea数据类型允许存储二进制串

名字	存储尺寸	描述
bytea	1或4字节外加真正的二进制串	变长二进制串

简单说，二进制字串适用于存储那些程序员认为是“裸字节”的数据，而字符串适合存储文本。

SQL标准定义了一种不同的二进制串类型， 叫做BLOB或者BINARY LARGE OBJECT。其输入格式和bytea不同，但是提供的函数和操作符大多一样。

5、日期/时间类型

PostgreSQL支持SQL中所有的日期和时间类型，如下表所示。

名字	存储尺寸	描述	最小值	最大值	解析度
timestamp [ (*p*) ] [ without time zone ]	8字节	包括日期和时间（无时区）	4713 BC	294276 AD	1微秒 / 14位
timestamp [ (*p*) ] with time zone	8字节	包括日期和时间，有时区	4713 BC	294276 AD	1微秒 / 14位
date	4字节	日期（没有一天中的时间）	4713 BC	5874897 AD	1日
time [ (*p*) ] [ without time zone ]	8字节	一天中的时间（无日期）	00:00:00	24:00:00	1微秒 / 14位
time [ (*p*) ] with time zone	12字节	一天中的时间（不带日期），带有时区	00:00:00+1459	24:00:00-1459	1微秒 / 14位
interval [ *fields* ] [ (*p*) ]	16字节	时间间隔	-178000000年	178000000年	1微秒 / 14位

time、timestamp和interval接受一个可选的精度值 p，这个精度值声明在秒域中小数点之后保留的位数。缺省情况下，在精度上没有明确的边界，p允许的范围是从 0 到 6。

interval类型有一个附加选项，它可以通过写下面之一的短语来限制存储的fields的集合：

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND

注意如果fields和p被指定，fields必须包括SECOND，因为精度只应用于秒。

5.1、日期/时间输入

日期和时间的输入可以接受几乎任何合理的格式，把[DateStyle参数设置为MDY，就是选择“月－日－年”的解释，设置为DMY就是 “日－月－年”，而YMD是 “年－月－日”。

SQL语法：

type [ (p) ] 'value'

其中p是一个可选的精度声明，它给出了在秒域中的小数位数目。精度可以被指定给time、timestamp和interval类型，范围是0到6。如果在一个常数声明中没有指定任何精度，它将默认取文字值的精度（但是不能超过6位）。

5.1.1、日期

date类型可能的输入方式：

例子	描述
1999-01-08	ISO 8601; 任何模式下的1月8日 （推荐格式）
January 8, 1999	在任何datestyle输入模式下都无歧义
1/8/1999	MDY模式中的1月8日；DMY模式中的8月1日
1/18/1999	MDY模式中的1月18日；在其他模式中被拒绝
01/02/03	MDY模式中的2003年1月2日； DMY模式中的2003年2月1日； YMD模式中的2001年2月3日
1999-Jan-08	任何模式下的1月8日
Jan-08-1999	任何模式下的1月8日
08-Jan-1999	任何模式下的1月8日
99-Jan-08	YMD模式中的1月8日，否则错误
08-Jan-99	1月8日，除了在YMD模式中错误
Jan-08-99	1月8日，除了在YMD模式中错误
19990108	ISO 8601; 任何模式中的1999年1月8日
990108	ISO 8601; 任何模式中的1999年1月8日
1999.008	年和一年中的日子
J2451187	儒略日期
January 8, 99 BC	公元前99年

5.1.2、时间

当日时间类型是time [ (*p*) ] without time zone和time [ (*p*) ] with time zone。 只写time等效于time without time zone。

time类型可能的输入方式：

例子	描述
04:05:06.789	ISO 8601
04:05:06	ISO 8601
04:05	ISO 8601
040506	ISO 8601
04:05 AM	和04:05一样，AM并不影响值
04:05 PM	和16:05一样，输入的小时必须为 <= 12
04:05:06.789-8	ISO 8601
04:05:06-08:00	ISO 8601
04:05-08:00	ISO 8601
040506-08	ISO 8601
04:05:06 PST	缩写指定的时区
2003-04-12 04:05:06 America/New_York	全名指定的时区

5.1.3、时间戳

时间戳类型的有效输入由一个日期和时间的串接组成，后面跟着一个可选的时区，一个可选的AD或者BC

下面是一些时间戳的写法：

1999-01-08 04:05:06

1999-01-08 04:05:06 -8:00

January 8 04:05:06 1999 PST

5.1.4、特殊值

输入串	合法类型	描述
epoch	date, timestamp	1970-01-01 00:00:00+00（Unix系统时间0）
infinity	date, timestamp	比任何其他时间戳都晚
-infinity	date, timestamp	比任何其他时间戳都早
now	date, time, timestamp	当前事务的开始时间
today	date, timestamp	当日午夜
tomorrow	date, timestamp	明日午夜
yesterday	date, timestamp	昨日午夜
allballs	time	00:00:00.00 UTC

了解即可

5.2、日期/时间输出

时间/日期类型的输出格式可以设成四种风格之一： ISO 8601、SQL（Ingres）、传统的POSTGRES（Unix的date格式）或 German 。

日期/时间输出风格

风格声明	描述	例子
ISO	ISO 8601, SQL标准	1997-12-17 07:37:16-08
SQL	传统风格	12/17/1997 07:37:16.00 PST
Postgres	原始风格	Wed Dec 17 07:37:16 1997 PST
German	地区风格	17.12.1997 07:37:16.00 PST

日期顺序习惯

datestyle设置	输入顺序	例子输出
SQL, DMY	日/月/年	17/12/1997 15:37:16.00 CET
SQL, MDY	月/日/年	12/17/1997 07:37:16.00 PST
Postgres, DMY	日/月/年	Wed 17 Dec 07:37:16 1997 PST

6、布尔类型

PostgreSQL提供标准的SQL类型boolean，boolean可以有多个状态：“true（真）”、“false（假）”和第三种状态“unknown（未知）”，未知状态由SQL空值表示。

名字	存储字节	描述
boolean	1字节	状态为真或者假

“真”状态的有效文字值是：

TRUE
't'
'true'
'y'
'yes'
'on'
'1'

而对于“假”状态，你可以使用下面这些值：

FALSE
'f'
'false'
'n'
'no'
'off'
'0'

推荐使用TRUE和FALSE

boolean类型的使用

CREATE TABLE test1 (a boolean, b text);
INSERT INTO test1 VALUES (TRUE, 'sic est');
INSERT INTO test1 VALUES (FALSE, 'non est');
SELECT * FROM test1;
 a |    b
---+---------
 t | sic est
 f | non est

SELECT * FROM test1 WHERE a;
 a |    b
---+---------
 t | sic est

7、JSON类型

JSON 数据类型是用来存储 JSON（JavaScript Object Notation） 数据的。这种数据也可以被存储为text，但是 JSON 数据类型的 优势在于能强制要求每个被存储的值符合 JSON 规则。也有很多 JSON 相关的函 数和操作符可以用于存储在这些数据类型中的数据。

jSON的俩种数据类型：json和jsonb

它们几乎相同，唯一的实际区别之一就是效率。

json数据类型存储输入文本的精准拷贝，处理函数必须在每 次执行时必须重新解析该数据。而jsonb数据被存储在一种分解好的 二进制格式中，它在输入时要稍慢一些，因为需要做附加的转换。但是 jsonb在处理时要快很多，因为不需要解析。jsonb也支 持索引，这也是一个令人瞩目的优势。

JSON 基本类型和相应的PostgreSQL类型

JSON 基本类型	PostgreSQL类型	注释
string	text	不允许\u0000，如果数据库编码不是 UTF8，非 ASCII Unicode 转义也是这样
number	numeric	不允许NaN 和 infinity值
boolean	boolean	只接受小写true和false拼写
null	(无)	SQL NULL是一个不同的概念

回头在写

8、数组

PostgreSQL允许一个表中的列定义为变长多维数组。可以创建任何内建或用户定义的基类、枚举类型或组合类型的数组。

8.1、数组类型的定义

为了展示数组类型的使用，我们创建这样一个表：

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

一个数组数据类型可以通过在数组元素的数据类型名称后面加上方括号（[]）来命名。

CREATE TABLE的语法允许指定数组的确切大小，例如：

CREATE TABLE tictactoe (
    squares   integer[3][3]
);

8.2、数组值输入

一个数组常量的一般格式如下：

'{ val1 delim val2 delim ... }'

这里delim是类型的定界符，比如说逗号、分号（用于box类型）。

数组常量的例子：

'{{1,2,3},{4,5,6},{7,8,9}}'

要设置一个数组常量的一个元素为NULL，在该元素值处写NULL。如果你需要一个真正的字符串值“NULL”，你必须在它两边放上双引号。

8.3、具体使用

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

看看结果

SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)

多维数组的每一维都必须有相匹配的长度。不匹配会造成错误，例如：

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"meeting"}}');
ERROR:  multidimensional arrays must have array expressions with matching dimensions

8.4、访问数组

查询检索在第二季度工资发生变化的雇员的名字：

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

数组下标写在方括号内。默认情况下，PostgreSQL为数组使用了一种从1开始的编号习惯，即一个具有n个元素的数组从array[1]开始，结束于array[*n*]。

查询检索所有员工第三季度的工资：

SELECT pay_by_quarter[3] FROM sal_emp;

 pay_by_quarter
----------------
          10000
          25000
(2 rows)

这个数组也可以切片哦！

一个数组切片可以通过在一个或多个数组维度上指定*下界*:*上界*来定义

查询检索Bill在本周头两天日程中的第一项：

SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{meeting},{training}}
(1 row)

使用array_dims函数获得任何数组值的当前维度：

SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

 array_dims
------------
 [1:2][1:2]
(1 row)

array_dims产生一个text结果

也可以通过array_upper和array_lower来获得维度，它们将分别返回一个指定数组的上界和下界：

SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_upper
-------------
           2
(1 row)

array_length将返回一个指定数组维度的长度：

SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_length
--------------
            2
(1 row)

cardinality返回一个数组中在所有维度上的元素总数。 这实际上是调用unnest将会得到的行数：

SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';

 cardinality
-------------
           4
(1 row)

8.5、修改数组

数组值可以被整个替换：

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

或者使用ARRAY表达式语法：

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

更新数组上的一个元素：

UPDATE sal_emp SET pay_by_quarter[4] = 15000
    WHERE name = 'Bill';

或者在一个切片上被更新：

UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
    WHERE name = 'Carol';

一个数组也可以通过使用函数array_prepend、array_append或array_cat构建。前两个函数仅支持一维数组，但array_cat支持多维数组。 一些例子：

SELECT array_prepend(1, ARRAY[2,3]);
 array_prepend
---------------
 {1,2,3}
(1 row)

SELECT array_append(ARRAY[1,2], 3);
 array_append
--------------
 {1,2,3}
(1 row)

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
 array_cat
-----------
 {1,2,3,4}
(1 row)

SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
      array_cat
---------------------
 {{1,2},{3,4},{5,6}}
(1 row)

SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
      array_cat
---------------------
 {{5,6},{1,2},{3,4}}

8.6、在数组中搜索

要在一个数组中搜索一个值，每一个值都必须被检查。这可以手动完成，但是我们必须知道数组的尺寸。例如：

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

但是这么写好像有点呆呆的，而且数组长度未知的时候无法使用。那么就可以用到ANY和ALL了。

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

查找所有元素值都为10000的数组所在的行：

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

太多了，记笔记记不完。。。