PG中那些神奇的数据类型

数组类型

    PostgreSQL 允许记录的字段定义成定长或不定长的多维数组。 数组类型可以是任何基本类型或用户定义类型。（不过，复合类型和域的数组还不支持。）

    如何使用呢？看下面

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

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)

JSON类型

    从PostgreSQL 9.3版本开始，JSON已经成为内置数据类型，“一等公民”啦。

    如何使用呢？看下面

postgres=# insert into testjson (data) values('{"a": 1, "b": 2}'::json);

postgres=# select * from testjson;
 id |           data
----+--------------------------
  1 | {"a": 1, "b": 2}
  2 | {"a": 3, "b": 4, "c": 5}
  3 | {"a": 6, "c": 7}
(3 rows)

    json类型还可以根据key值还检索

postgres=# select * from testjson where (data->>'a')::int>1;
 id |           data
----+--------------------------
  2 | {"a": 3, "b": 4, "c": 5}
  3 | {"a": 6, "c": 7}

    另外，自从9.4开始，pg开始支持jsonb，性能大幅提高

xml类型

    pg中使用xml类型还是略微有些麻烦，需要使用一些pg内置的xml做转换，将一个xml文档转换成pg能识别的xml类型，个人建议如果不是老旧系统的话，还是使用json类型比较好

UUID类型

    这个类型很简单，就是自动生成一个uuid，对于一些需要唯一键值的需求还是很方便的

HSTORE类型

    个人认为这是pg中最方便，而且使用率颇高的高级类型，hstore的本质是一个map，可以存储key-value对

    如何使用呢？看下面

postgres=# INSERT INTO hstore_test (data) VALUES ('"key1"=>"value1", "key2"=>"value2", "key3"=>"value3"');

INSERT 0 1

postgres=# select * from hstore_test;
 item_id |                         data                         
---------+------------------------------------------------------
       1 | "key1"=>"value1", "key2"=>"value2", "key3"=>"value3"
(1 row)

hstore类型可以通过key进行检索

postgres=# SELECT * FROM hstore_test WHERE data ? 'key4';
 item_id |                           data                           
---------+----------------------------------------------------------
       1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
(1 row)

以上所有这些神奇的数据类型，都支持索引，而且还有各种的专属操作符，通过专属操作符和不同类型的索引配合，可以最大化数据库操作的性能