PostgreSQL中几个需要注意的数据类型

1. 概述

使用PostgreSQL一段时间后，就发现PG对数据类型处理上有个特点，基本上都以字符串形式进行数据的转换和传输。对于非文本型将其存储格式转化为字符串形式，或反过来将字符串解析为特定数据类型往往依赖上下文环境（locale，数据库参数等）所规定的格式。比如日期型的格式就由datestyle定义。

不同datestyle值的情况下，timestamptz型的输出形式不同：

postgres=# set datestyle=SQL;
SET
postgres=# select now();
             now
-----------------------------

 18/08/2013 22:56:33.457 HKT
(1 行记录)


postgres=# set datestyle=ISO;
SET
postgres=# select now();
            now
----------------------------

 2013-08-18 22:56:52.001+08
(1 行记录)

这种表现形式不仅出现在psql的终端输入输出上，也作为jdbc等API和服务端交互时数据传输形式。为了能以简便的方式从字符串中提取出需要的值，各API往往在连接时就通过连接参数把输出格式确定下来。然而有些数据类型的存储和输入输出上的差异不太好处理，需要特别留意，比如下面几个。

2. money

和timestamptz类似，money的输入输出形式依赖于数据库的lc_monetary参数：

postgres=# show lc_monetary;
                     lc_monetary
-----------------------------------------------------

 Chinese (Simplified)_People's Republic of China.936
(1 行记录)


postgres=# select 1.1::money;
 money
--------

 ￥1.10
(1 行记录)


postgres=# set lc_monetary='C';
SET
postgres=# select 1.1::money;
 money
-------

 $1.10
(1 行记录)

这里却有一个不同的地方，lc_monetary参数不仅仅影响了money的输入输出形式，还规定的money的语义（币种）。我们知道10美元和10人民币是不能划上等号的，而单独的money的存储格式里却不包含币种信息。所以money还不能算作真正的货币类型，这其实也限制了money在整个数据库里只能有一个统一的币种。
不仅如此，有些API在处理money时也犯了难，比如：pgjdbc将money映射为java的double，并假设货币为美元对服务端返回的money字符串进行解析。
这导致对服务端的lc_monetary参数值为美元以外的货币时，pgjdbc将会解析失败(*1)。npgsql将money映射为.net的decimal类型,并在解析时假设货币的小数位数一定是2位。这样在那些不符合这一假设的货币上就悲剧了，比如100日圆通过npgsql读出来就成了1日圆(*2)。

鉴于以上种种，建议避开money的使用，改用decimal 或者 numeric。

*1)回避方法为使用ResultSet.getString()直接获取字符串避开解析过程。
*2)开发版的npgsql已经解决了这个问题，方法为在建立连接时强制将lc_monetary参数值设为C，即美元。
*)在8.2及之前版本的文档里，曾声明货币数据类型是将要被废弃的一种数据类型，不推荐使用。但8.3有对其进行了改进，存储大小也有4字节扩大到8字节。手册追加了一些使用上的说明，并删掉了“将要被废弃”的描述。

3. timestamptz和timetz

和money类似的是timestamptz。timestamptz的存储格式里并不包括时区信息，在输出时使用timezone参数规定的时区格式化成带时区的字符串形式。但timetz的存储格式里是包含时区的，所以你会看到下面的现象。

timestamptz使用timezone参数规定的时区格式化输出形式:

postgres=# show timezone;
    TimeZone
----------------

 Asia/Hong_Kong
(1 行记录)

postgres=# select '2013-08-18 11:39:11.153-04'::timestamptz;
        timestamptz
----------------------------

 2013-08-18 23:39:11.153+08
(1 行记录)

timetz使用自带的时区格式化输出形式:

postgres=# select '11:39:11.153-04'::timetz;
     timetz
-----------------

 11:39:11.153-04
(1 行记录)

比较timestamptz数据值时只比较是不是同一时刻：

postgres=# select '2013-08-18 11:39:11.153-04'::timestamptz = '2013-08-18 23:39:
11.153+08'::timestamptz;
 ?column?
----------

 t
(1 行记录)

比较timetz数据值时不仅比较时刻还比较时区:

postgres=# select '11:39:11.153-04'::timetz = '23:39:11.153+08'::timetz;
 ?column?
----------

 f
(1 行记录)

4. char(n)

char(n)也是一种需要留心的类型，它在运算时会截掉末尾的空格，输出时在末尾补上空格直到长度等于n。

postgres=# select length('xx    '::char(30));
 length
--------

      2
(1 行记录)


postgres=# select 'xx    '::char(30);
   bpchar
--------------------------------

 xx
(1 行记录)