PostgreSQL初识

一.PG介绍

PG历史

基于伯克利的 POSTGRES 学院派出身，有很多牛逼特性，在学术圈有无数拥趸

Postgres95 使用标准sql作为内置语言，代码公布到互联网，华裔在pg历史上最浓墨重彩的一笔。

PostgreSQL, Postgres 96年8月，第一个开源版本诞生。

详细历史 http://www.postgresql.org/docs/9.3/static/history.html

PG下载版本9.4.4 http://www.postgresql.org/9.4/release-9-4.html

PG特性

SQL标准最规范的执行者，不管是原来的SQL92还是最新的SQL2011

SQL类别	含义	对应的SQL命令
DDL	Data Definition Language	CRAETE,DROP,ALTER,TRUNCATE
DML	Data Manipulation Language	SELECT,INSERT,DELETE,UPDATE
DCL	Data Control Language	GRANT,REVOKE
DTL	Data Transaction Language	BEGIN,COMMIT,SAVEPOINT,ROLLBACK

主要关系数据库型各种SQL标准实现的对比：http://troels.arvin.dk/db/rdbms/

丰富的数据类型（这里不具体介绍，详细可参考链接）

任意精度的数值类型

 decimal&numeric：https://www.postgresql.org/docs/9.3/static/datatype-numeric.html

无限长度的文本类型

text：http://www.postgresql.org/docs/9.3/static/datatype-character.html

NoSQL特性的集合类型

array：http://www.postgresql.org/docs/9.3/static/datatype-xml.html

xml：http://www.postgresql.org/docs/9.3/static/datatype-xml.html

jsonb：http://www.postgresql.org/docs/9.3/static/datatype-json.html

hstore：https://www.postgresql.org/docs/current/static/hstore.html

ltree树状结构

ltree：http://www.postgresql.org/docs/9.3/static/ltree.html

IPv4和IPv6类型

ip4r：http://wiki.corp.qunar.com/display/searchdev/ip4r

Postgis地理信息

postgis：http://postgis.net/

全文索引类型

Text Search Type：http://www.postgresql.org/docs/9.3/static/datatype-textsearch.html

二.PG基本的CRUD

创建数据库：create database test_db

创建表：

增加一条数据

查看数据

修改数据

删除数据、表

三.Json和Jsonb的区别

 PostgreSQL9.4 新增 JSONB 数据类型， JSONB 同时属于 JSON (JavaScript Object Notation) 数据类型，jsonb 和 json 的输入数据几乎完全通用

最大的差别体现在效率上

区别一：json 存储的数据几乎和输入数据一样，存储的是未解析的数据，调用函数时使用效率较低; 而 jsonb 存储的是分解的 binary 格式数据，使用时不需要再解析了，因此使用上效率较高；

区别二： json 在写入时较快，而 jsonb 写入时由于需要转换导致写入较慢；

区别三：json不支持索引，jsonb支持索引；

区别四：json会保留所有重复的key，而 jsonb 对于重复的元素仅保留最后出现的重复元素；

区别五：json 类型输出的内容和写入的内容一样，不会对输出的结果改变，而 jsonb不保证顺序

下面通过些简单的例子了解两者的差异。

  

--1 这个例子两者没啥差异

 

francs =>  SELECT  '[1, 2, "foo", null]' :: json ;

        json         

---------------------

  [ 1 ,   2 ,   "foo" ,   null ]

( 1  row )

francs =>  SELECT  '[1, 2, "foo", null]' :: jsonb ;

        jsonb        

---------------------

  [ 1 ,   2 ,   "foo" ,   null ]

( 1  row )

备注:  json 类型输出的内容和写入的内容一样，不会对输出的结果改变，而 jsonb不一样，看下面的例子。

--2 jsonb 输出内容顺序不一样

 

francs =>  SELECT  '{"bar": "baz", "balance": 7.77, "active":false}' :: json ;

                      json                       

-------------------------------------------------

  { "bar" :   "baz" ,   "balance" :   7.77 ,   "active" : false }

( 1  row )

francs =>  SELECT  '{"bar": "baz", "balance": 7.77, "active":false}' :: jsonb ;

                      jsonb                       

--------------------------------------------------

  { "bar" :   "baz" ,   "active" :   false ,   "balance" :   7.77 }

( 1  row )

--3 jsonb: 整数类型输出不一样

 

francs =>  SELECT  '{"reading": 1.230e-5}' :: json ,   '{"reading": 1.230e-5}' :: jsonb ;

         json           |           jsonb          

-----------------------+-------------------------

  { "reading" :   1.230e-5 }   |   { "reading" :   0.00001230 }

( 1  row )

--4 jsonb: 去掉了空格

 

francs =>   select   ' {"id":1,

"name":"francs",

"remark":"a good guy!"

}' :: json ;

          json          

------------------------

   { "id" : 1 ,               +

  "name" : "francs" ,        +

  "remark" : "a good guy!" +

  }

( 1  row )

francs =>   select   ' {"id":1,

"name":"francs",

"remark":"a good guy!"

}' :: jsonb ;

                        jsonb                         

------------------------------------------------------

  { "id" :   1 ,   "name" :   "francs" ,   "remark" :   "a good guy!" }

( 1  row )

--5 jsonb:  重复的元素值仅保留最后一个

 

francs =>   select   ' {"id":1,

"name":"francs",

"remark":"a good guy!",

"name":"test"

}' :: jsonb ;

                       jsonb                        

----------------------------------------------------

  { "id" :   1 ,   "name" :   "test" ,   "remark" :   "a good guy!" }

( 1  row )