PostgreSQL特殊语法_sql

概述本文整理了一些PostgreSQL相对于标准SQL或者MySQL的一些特殊语法。 Limit PG: limit nlimit n offset m MySQL: limit n limit m,n Like/ILIKE: 提供的一个扩展ILIKE：同LIKE，只是不区分大小写。符号表示： LIKE: ~~ ILIKE: ~~* NOT LIKE: !~~* NOT ILIKE: !~~*

本文整理了一些Postgresql相对于标准sql或者MysqL的一些特殊语法。

limit

PG:

limit nlimit 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:\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 1select 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-8022143select 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