PostgreSQL 数据库

PostgreSQL 数据库

一 ：简介

PostgreSQL是关系数据库，支持json。语法和mysql类似。事实上，PostgreSQL，MongoDB（NoSQL），mysql均支持json，但从丰富度、速度等等各方面而言，PostgreSQL > MongoDB > >mysql(个人认为)。
因此在存储NoSQL类型数据时，以json方式存储时，PostgreSQL为最优。

二：PostgreSQL基础

（一）：创建表

CREATE TABLE orders (
 ID serial NOT NULL PRIMARY KEY,
 info json NOT NULL
);

注意这里我们要存储的是json类型数据，因此每次插入时插入的都是各种json数据。而关系型数据库需要有唯一性约束，因此需要另外一个唯一性数据，此处为id，作为主键。 serial 表示插入数据时，不需要指定ID段的值，即只需要指定info段的json数据值即可，ID段的值会自动从一递增。此时，ID值每行都不相同，满足唯一性约束。

（二）：插入json数据
插入前，首先得保证插入数据为正确的json格式。
插入一条数据：

INSERT INTO orders (info)
VALUES
 (
 '{ "customer": "sjk66", "items": {"product": "Beer","qty": 6}}'
 );

也可以一次性插入多条数据：

INSERT INTO orders (info)
VALUES
 (
 '{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'
 ),
 (
 '{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'
 ),
 (
 '{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}'
 );

（三）：查询json数据
PostgreSQL 提供了两个原生运算符来查询 JSON 数据：

• -> 运算符通过键来返回 JSON 对象
• ->> 运算符将 JSON 对象返回为文本
• #> 运算符通过路径返回文本

（四）：json 和 jsonb的区别和选择
两者从用户操作的角度来说没有区别，区别主要是存储和读取的系统处理（预处理）和耗时方面有区别。json写入快，读取慢，jsonb写入慢，读取快。
具体来说的话，
Json 数据类型：

• json 数据类型储存输入文本的精确拷贝，处理函数在每次执行的时候，都必须对这些文本重新进行分析。
• json 数据类型会保留文本中与 Json 语义完全无关的空白字符，各个键在 Json 对象内的排列顺序以及具有相同键的值。

Jsonb 数据类型：

• Jsonb 数据类型以无压缩（decomposed）二进制格式来储存数据，因为格式转换带来的花销，这种类型在处理输入的时候速度会稍微慢一些，但是因为这种类型的数据并不需要重新进行分析，所以这种数据的处理速度会明显地快很多。
• jsonb 支持索引特性，这是一个明显的优点。
• jsonb 不会保留任何无关的空白，不会保留对象键的排列顺序，也不会保留任何重复的对象键。如果输入里面指定了重复的键，那么只有最后一个值会被保留。
• jsonb 类型可以检测一个 jsonb 值是否包含了另一个 jsonb 值，而 json 类型并不具备这样的特性。
• jsonb 会拒绝那些超出 PostgreSQL 数字类型范围的数字，而 json 则不会这样做。

jsonb支持更多的操作符@>，?等，具体可以在https://www.postgresql.org/docs/9.4/functions-json.html查看，
另外还有一些json和jsonb处理函数也可以在上述网站查看。

所以，从工程项目的角度，建议josnb。

（五）：josnb的索引
jsonb类型的列上可以直接建索引，支持BTree索引和GIN索引(通用倒排索引)。其中，GIN索引可以高效的从JSONB内部的key/value对中搜索数据，因此JSONB类型上常用GIN索引。
JSONB上创建GIN索引的方式有两种：

• （1）使用默认的jsonb_ops操作符创建。

• （2）使用jsonb_path_ops操作符创建。

  两者的区别是：

• （1）jsonb_ops的GIN索引中，JSONB数据中的每个key和value都是作为一个单独的索引项。

• （2）jsonb_path_ops只为每一个value创建一个索引项。

  例如，一个项{“foo”:{“bar”:“baz”}}，jsonb_ops会分别为“foo”,“bar”,"baz"创建索引项，共创建了三个。jsonb_path_ops则是把“foo”,“bar”,"baz"组合成一个hash值作为索引项。因此，通常jsonb_path_ops的索引较小。

因此，jsonb_path_ops类型的索引比jsonb_ops的小，但查询速度比jsonb_ops慢，为性能考虑，一般考虑jsonb_ops。

三：PostgreSQL与python的连接——psycopg2

PostgreSQL可以使用psycopg2模块与Python集成。
具体操作可以百度，基本上python连接mysql的操作类似，此处不再过多解释。