SQL

update/delete .. order by .. limit ..：

update aa set b='mmmmmmmmbbbbbbbb' where a>10 order by a limit 1;  

会报错：因为update 不能和order by 或limit 联合使用。如果需要使用则

需要打一个补丁：http://blog.163.com/digoal@126/blog/static/1638770402014224113459340/

coalesce：

select coalesce(p1,p2,p3,....);

如果所有参数都为空则返回空（注意是null不是空字符串）。

如果参数从左到右出现的第一个不为空（null）的参数，则返回此参数。

array：

如果其他字段内容都一样，则能使用array作为表字段的话尽量使用array字段。

http://blog.163.com/digoal@126/blog/static/163877040201332210920390/?suggestedreading&wumii

这样可以可以减少表的存储大小 ，和减少SQL扫描block的次数 加快查询速度。

按时间进行分区的分区表demo：

http://blog.163.com/digoal@126/blog/static/163877040201422293824929/

时间相关函数： http://www.postgresql.org/docs/9.3/static/functions-datetime.html#OPERATORS-DATETIME-TABLE

pg_sleep(seconds)：让session睡眠多久。

EXTRACT的使用方法：

http://www.postgresql.org/docs/9.3/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

SELECT EXTRACT(YEAR FROM INTIME) FROM RK 从INTIME字段中提取年份
SELECT EXTRACT(MONTH FROM INTIME) FROM RK 从INTIME字段中提取月份
SELECT EXTRACT(DAY FROM INTIME) FROM RK 从INTIME字段中提取日
SELECT EXTRACT(HOUR FROM INTIME) FROM RK 从INTIME字段中提取时
SELECT EXTRACT(MINUTE FROM INTIME) FROM RK 从INTIME字段中提取分
SELECT EXTRACT(SECOND FROM INTIME) FROM RK 从INTIME字段中提取秒

select...into.../create table...as select...
创建一个表从语句中：

这种创建表不会将表的约束主键索引等一同拷贝。

创建一个所有约束主键等一样的表： CREATE   TABLE   weather_temp ( LIKE   weather INCLUDING CONSTRAINTS);（ http://www.postgresql.org/docs/9.3/static/sql-createtable.html ）

digoal=# \z
                                  Access privileges
 Schema |         Name         | Type  | Access privileges | Column access privileges 
--------+----------------------+-------+-------------------+--------------------------
 public | aa                   | table |                   | 
 public | bb                   | table |                   | 
 public | capitals             | table |                   | 
 public | cities               | table |                   | 
 public | foo                  | table |                   | 
 public | hhj                  | table |                   | 
 public | measurement          | table |                   | 
 public | measurement_y2005m02 | table |                   | 
 public | measurement_y2006m02 | table |                   | 
 public | measurement_y2006m03 | table |                   | 
 public | measurement_y2007m12 | table |                   | 
 public | vvc                  | table |                   | 
 public | xxv                  | table |                   | 
(13 rows)
digoal=# \d+ foo
                           Table "public.foo"
  Column  |  Type   | Modifiers | Storage  | Stats target | Description 
----------+---------+-----------+----------+--------------+-------------
 fooid    | integer |           | plain    |              | 
 foosubid | integer |           | plain    |              | 
 fooname  | text    |           | extended |              | 
Indexes:
    "fooid_pkey" PRIMARY KEY, btree (fooid)
Has OIDs: no
digoal=# create table nna as select * from foo;
SELECT 2
digoal=# select * into aax2 from foo ;
SELECT 2
digoal=# \z
                                  Access privileges
 Schema |         Name         | Type  | Access privileges | Column access privileges 
--------+----------------------+-------+-------------------+--------------------------
 public | aa                   | table |                   | 
 public | aax2                 | table |                   | 
 public | bb                   | table |                   | 
 public | capitals             | table |                   | 
 public | cities               | table |                   | 
 public | foo                  | table |                   | 
 public | hhj                  | table |                   | 
 public | measurement          | table |                   | 
 public | measurement_y2005m02 | table |                   | 
 public | measurement_y2006m02 | table |                   | 
 public | measurement_y2006m03 | table |                   | 
 public | measurement_y2007m12 | table |                   | 
 public | nna                  | table |                   | 
 public | vvc                  | table |                   | 
 public | xxv                  | table |                   | 
(15 rows)
digoal=# \d+ aax2
                          Table "public.aax2"
  Column  |  Type   | Modifiers | Storage  | Stats target | Description 
----------+---------+-----------+----------+--------------+-------------
 fooid    | integer |           | plain    |              | 
 foosubid | integer |           | plain    |              | 
 fooname  | text    |           | extended |              | 
Has OIDs: no

digoal=# \d+ nna
                           Table "public.nna"
  Column  |  Type   | Modifiers | Storage  | Stats target | Description 
----------+---------+-----------+----------+--------------+-------------
 fooid    | integer |           | plain    |              | 
 foosubid | integer |           | plain    |              | 
 fooname  | text    |           | extended |              | 
Has OIDs: no

select into ... * form ...

postgres=# \d+ testtb
                        Table "public.testtb"
 Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
 id     | integer |           | plain    |              | 
 info   | text    |           | extended |              | 
Indexes:
    "idx_1" btree (id)
Has OIDs: no

postgres=# CREATE OR REPLACE FUNCTION tx() RETURNS text AS  
postgres-#  $BODY$  
postgres$# DECLARE  
postgres$# re RECORD;
postgres$# begin
postgres$# SELECT INTO re * FROM testtb WHERE id=3;
postgres$# return re.info;
postgres$# END  
postgres$# $BODY$  
postgres-# LANGUAGE plpgsql; 

postgres=# update testtb setinfo='llllllll' where id=3;
postgres=# select tx();
    tx    
----------
 llllllll
(1 row)

rowtype:

http://blog.csdn.net/bielidefeng/article/details/27649931

添加新列：

http://blog.csdn.net/bielidefeng/article/details/27649999

自增ID列：ALTER TABLE weather ADD COLUMN id  SERIAL;

删除重复行：

http://my.oschina.net/swuly302/blog/144933

改变字符编码：

SELECT convert(‘foobar_utf8′,’UTF8′,’LATIN1′); #Converts foobar from utf8 to latin1. 
SELECT convert_from(‘foobar_utf8′,’LATIN1′); #Converts foobar to latin1. 
SELECT convert_to(‘foobar’,'UTF8′); #Converts foobar to utf8. 
SELECT to_ascii(‘foobar’,'LATIN1′); #Converts foobar to latin1.

一些PG的小技巧：

http://blog.csdn.net/wanghai__/article/details/6163946

正则：

http://www.postgresql.org/docs/9.3/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP

~/SIMILAR TO ：

“~”此符号用于匹配是否能够匹配到，返回值布尔

digoal=# select 'abc' ~ '(b|d)';
 ?column? 
----------
 t
(1 row)

substring('***' from '***')：

返回正则匹配到的内容，如果正则表达式中含有多个“()”括号，则返回第一个括号匹配到的内容。

digoal=# select substring('foobar' from 'o(.)b(a)') ;
 substring 
-----------
 o
(1 row)

regexp_matches：匹配字符串

返回正则匹配到的内容， 如果正则表达式中含有多个“()”括号，则返回所有括号匹配到的内容为一个array。

digoal=# SELECT regexp_matches('foobarbequebaz', '(bar)(beque)');--返回一个 text[]
 regexp_matches 
----------------
 {bar,beque}
(1 row)
digoal=# select pg_typeof(regexp_matches('foobarbequebaz', '(bar)(beque)'));
 pg_typeof 
-----------
 text[]
(1 row)

如果将regexp_matches的返回值array直接作为any参数会报错 ERROR: op ANY/ALL (array) does not support set arguments：

digoal=# select 'bar'= any(regexp_matches('foobarbequebaz', '(bar)(beque)'));
ERROR:  op ANY/ALL (array) does not support set arguments
digoal=# select 'bar'= any(t) from regexp_matches('foobarbequebaz', '(bar)(beque)') as t;
 ?column? 
----------
 t
(1 row)

regexp_replace：替换字符串

digoal=# select regexp_replace('foobarbaz', 'b..', 'X');
 regexp_replace 
----------------
 fooXbaz
(1 row)

regexp_split_to_table 字符串切割（通过固定字符）：

切割成一个表的形式：

digoal=# SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', E'\\s+') AS foo;
  foo  
-------
 the
 quick
 brown
 fox
 jumps
 over
 the
 lazy
 dog
(9 rows)

切割成一个array：

digoal=# SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', E'\\s+');
             regexp_split_to_array             
-----------------------------------------------
 {the,quick,brown,fox,jumps,over,the,lazy,dog}
(1 row)

string_agg：将多行结合拼接成为一个字符串。

postgres=# select * from ct;
 id | rowid | attribute | value 
----+-------+-----------+-------
  1 | test1 | att1      | val1
  2 | test1 | att2      | val2
  3 | test1 | att3      | val3
  4 | test1 | att4      | val4
  5 | test2 | att1      | val5
  6 | test2 | att2      | val6
  7 | test2 | att3      | val7
  8 | test2 | att4      | val8
(8 rows)
postgres=# select string_agg(attribute,',') from ct;  
               string_agg                
-----------------------------------------
 att1,att2,att3,att4,att1,att2,att3,att4
(1 row)

cast：

SELECT CAST(’1′ AS INTEGER) #Converts the varchar “1″ to integer.

ASCII to Number 字符转数字:

SELECT ASCII(‘A’); #Returns 65.

Number to ASCII 数字转字符:

SELECT CHR(65); #Returns A.

copy：

http://www.postgresql.org/docs/9.3/static/sql-copy.html

copy...to...：拷贝表到线下文件，此文件路径必须是绝对路径（全路径）。

会报 ERROR: relative path not allowed for COPY to file，这是因为拷贝到的文件路径不是绝对路径。

digoal=# copy foo to '99909';
ERROR:  relative path not allowed for COPY to file
digoal=# copy foo to '/home/pg93/ms/master/99909';
COPY 5

copy...from...： 拷贝线下文件到表中。此表必须先建立（空表即可）。注意字段间的分隔号。

建立一个名为44404的文本文件

[pg93@localhost master]$ pwd
/home/pg93/ms/master
[pg93@localhost master]$ vi 44404

44404文本的内容如下：

23432,234245,asdfg
68967,23452,asfergwet
1343245,242524,agtrgewt

建立ttbv表并拷贝：

digoal=# create table ttbv (a int, b int, c text);
CREATE TABLE
digoal=# copy ttbv from '/home/pg93/ms/master/44404' with delimiter ',';
COPY 3