PostgreSQL特殊语法

本文整理了一些PostgreSQL相对于标准SQL或者MySQL的一些特殊语法。

Limit

PG:

limit n
limit n offset m

MySQL:

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:tmppersons.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:tmppersons_db.csv' DELIMITER ',' CSV HEADER;

如果不包含头部，则将HEADER去掉：

COPY persons(email)
TO 'C:tmppersons_email_db.csv' DELIMITER ',' CSV;

也可以使用copy命令（在psql工具中）

copy (SELECT * FROM persons) to 'C:tmppersons_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/

psql命令行

psql命令行：
http://www.postgresqltutorial.com/psql-commands/