本文整理了一些Postgresql相对于标准sql或者MysqL的一些特殊语法。
Limit
PG:
limit n limit n offset m
limit n limit m,n
Like/ILIKE:
提供的一个扩展ILIKE：同LIKE，只是不区分大小写。
符号表示：
LIKE: ~~
ILIKE: ~~*
NOT LIKE: !~~*
NOT ILIKE: !~~*
获取最新插入的id：
INSERT INTO link (url,NAME,last_update) VALUES('http://www.postgresql.org','Postgresql',DEFAULT) RETURNING id;
insert into weibo.weibo_may(content) values('test') returning id;
也可以插入之后返回整条数据：
insert into weibo.weibo_may(content) values('test') returning *;
自增：
创建表的时候使用SERIAL数据类型。
CREATE TABLE weibo.weibo_may ( id serial NOT NULL,content text,CONSTRAINT idx_pk_id PRIMARY KEY (id) DEFERRABLE INITIALLY IMMEDIATE ) WITH ( OIDS=FALSE );
ALTER TABLE weibo.weibo_may OWNER TO postgres;
注意这个类型只能create的时候使用，已经存在的表无法修改字段为SERIAL类型。（不同于MysqL的auto increment）
参考： http://stackoverflow.com/questions/787722/postgresql-autoincrement
DELETE:
DELETE USING：使用另一张表的数据作为删除条件：
delete from a using b where a.id=b.id
删除后返回：使用delete returning
delete from weibo.weibo_may where id=1 returing *;
更新：
update from: 使用一张表的数据更新另一张表
update a set name=b.name from b where a.id=b.id
类似于：
update a inner join b on a.id=b.id set a.name=b.name
update returning:更新后返回：
UPDATE link SET description = 'Learn Postgresql fast and easy',rel = 'follow' WHERE ID = 1 RETURNING id,description,rel;
导入导出：
导入CSV文件：
使用copy from语句导入
COPY persons(first_name,last_name,dob,email) FROM 'C:\tmp\persons.csv' DELIMITER ',' CSV HEADER;
persons括号中指定要导入的字段，顺序必须与csv文件中的顺序一样，如果导入全部字段，可以忽略不写。
from指定文件路径
delimiter指定分隔符
CSV HEADER表示忽略第一行。
在pgadmin中，可以在表对象的右键菜单中使用import导入，参考
http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/
使用copy to语句导出：
COPY persons TO 'C:\tmp\persons_db.csv' DELIMITER ',' CSV HEADER;
如果不包含头部，则将HEADER去掉：
COPY persons(email) TO 'C:\tmp\persons_email_db.csv' DELIMITER ',' CSV;
也可以使用\copy命令（在psql工具中）
\copy (SELECT * FROM persons) to 'C:\tmp\persons_client.csv' with csv
参考：
http://www.postgresqltutorial.com/export-postgresql-table-to-csv-file/
表继承inherits
一个表继承的例子：
CREATE TABLE table_name ( column_name TYPE column_constraint,table_constraint table_constraint ) INHERITS existing_table_name;
此语句创建的表包含existing_table_name的所有字段，以及新制定的字段。
列级别的约束：
NOT NULL: 非空
UNIQUE：唯一
PRIMARY KEY：主键
CHECK：条件约束，例如大于0
REFERENCES：外表引用
表级别约束：
UNIQUE(column_list)
PRIMARY KEY(column_list)
CHECK(cindition)
REFERENCES
临时表使用temparory关键字，即create temparory table delete_users …
CREATE TABLE account( user_id serial PRIMARY KEY,username VARCHAR (50) UNIQUE NOT NULL,password VARCHAR (50) NOT NULL,email VARCHAR (355) UNIQUE NOT NULL,created_on TIMESTAMP NOT NULL,last_login TIMESTAMP );
数组：
在Postgresql中，每一种数据类型都有其相应的数组类型。如果自定义数据类型，pg也会创建相应的数组类型。
指定列为数组类型
create table contacts( id serial primary key,name varchar(100),phones text[] );
插入数组类型的值
insert into contacts(names,phones) values ('Jon',ARRAY['1355126748','0676-2174228'] );
insert into contacts(names,phones) values ('Jack','{"1355126748","0676-2174228"}' );
查询：
select name,phones[1] from contacts;
-- start with number 1
select name,phones from contacts;
select * from contacts where phones[1]='1355126748';
更新：
update contacts set phones[1]='177777777' where id=1;
搜索数组元素：
select * from contacts where '1355126748' = ANY(phones);
-- 任一个数组元素
展开数组：
select name,unnest(phones) from contacts;
-- 数组被拆分成多行，跟其他非数组字段组成多行结果;
-- Jack,1355126748
-- Jack,0676-2174228
Postgresql hstore
hstore，一种特殊的数据类型，用于存储字符串的key-value，注意是单值，多值不支持。
作为一个扩展模块实现。
启动该模块：
create extension hstore;
创建表：
create table books( id,serial primary key,tite varchar(255),attr hstor );
插入：
insert into books(title,attr) values ('Postgresql Tutorial','"paperback"=>"243","piblisher"=>"hsdhf.com","language"=>"English","ISBN" => "732-8022143" ');
查询：
select attr from books;
"paperback"=>"243","ISBN" => "732-8022143"
select attr->'ISBN' from books;
732-8022143
select attr->'language' from books where attr->'ISBN'='732-8022143'
还有很多特性，例如
获取值： avals()函数、svals()函数
获取键： akeys() skeys()
hstore转为json：hstore_to_json()函数
hstore转set： each()函数
判断是否包含特定key： ?& 操作符
判断是否含有特殊的键值对： @>
删除： delate()函数
更多特性推荐参考：
http://www.postgresqltutorial.com/postgresql-hstore/
JSON数据类型:
原生支持，无需安装extension（像hstore那样）：
create table orders( ID serial not null primary key,info json not null );
insert into orders (info) values ('{"customer":"John","items":{"product":"Beer","qty":6}}');
select infn from orders;
-- {"customer":"John","qty":6}}
两个原生操作：
-> 获取字段（JSON形式）,即返回JSON对象， “John”
->> 获取字段（文本形式） John
连起来操作：
select info->items-->'product' as product select * from orders where info->items-->'product' ='Diaper';
select info->>'customer' as customer,info -> 'items'-->'product' as product from orders where cast(info->'items'->>'qty' as integer)=2 ``` 对JSON数据进行聚合： ```sql SELECT MIN ( CAST ( info -> 'items' ->> 'qty' AS INTEGER ) ),MAX ( CAST ( info -> 'items' ->> 'qty' AS INTEGER ) ),SUM ( CAST ( info -> 'items' ->> 'qty' AS INTEGER ) ),AVG ( CAST ( info -> 'items' ->> 'qty' AS INTEGER ) ) FROM orders
pg JSON函数：
json_typeof()
json_object_keys()
json_each()
更多函数：http://www.postgresql.org/docs/9.4/static/functions-json.html
关于JSON的教程：
http://www.postgresqltutorial.com/postgresql-json/
