PostgreSQL和Oracle的函数差异分析
2023-07-31 17:38:02 
阿炯
PostgreSQL和Oracle的差异系列之函数
一、二、三、四、五、六、七
本文总结息《卸甲笔记》，感谢原作者。
--------------------------------------------------------------
一
PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先是迁移数据，然后就是SQL、存储过程、序列等程序中不同的数据库中数据的使用方式的转换。下面根据自己的理解和测试，写了一些SQL以及数据库对象转换方面的文章，不足之处，尚请多多指教。
1、NULL判断函数
Oracle的NULL判断函数是 nvl(A, B) 和 coalesce 两个函数。nvl(A, B) 是判断如果A不为NULL，则返回A，否则返回B。参数需要是相同类型的，或者可以自动转换成相同类型的, 否则需要显式转换。而 coalese 参数可以有多个，返回第一个不为NULL的参数。而参数必须为相同类型的 ，不会自动转换。
PostgreSQL中没有nvl函数。但是有coalesce函数。用法和Oracle的一样。可以使用coalesce来转换Oracle的nvl和coalesce。参数需要使用相同类型，或者可以转换成相同类型的。否则需要手动转换。
Oracle NULL判断函数
SQL> select * from o_test;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
111111 05-8月 -16
1 31-7月 -16
2 222222
SQL> select nvl(value1, 'Hello') value1 from o_test;
select nvl(value1, 'Hello') value1 from o_test
第 1 行出现错误:
ORA-01722: 无效数字
SQL> select nvl(value1, '10000') value1 from o_test;
VALUE1
----------
10000
1
2
SQL> select nvl(value2, 'Hello') value2 from o_test;
VALUE2
----------
111111
Hello
222222
SQL> select nvl(value3, '2010-1-1') value3 from o_test;
select nvl(value3, '2010-1-1') value3 from o_test
第 1 行出现错误:
ORA-01861: 文字与格式字符串不匹配
SQL> select nvl(value3, to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test;
VALUE3
--------------
05-8月 -16
31-7月 -16
01-1月 -10
SQL> select coalesce(value1, '10000') value1 from o_test;
select coalesce(value1, '10000') value1 from o_test
第 1 行出现错误:
ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 CHAR
SQL> select coalesce(value1, 10000) value1 from o_test;
VALUE1
----------
10000
1
2
SQL> select coalesce(value2, '', 'Hello John') value2 from o_test;
VALUE2
----------
111111
Hello John
222222
SQL> select coalesce(value3,'', to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test;
select coalesce(value3,'', to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test
第 1 行出现错误:
ORA-00932: 数据类型不一致: 应为 DATE, 但却获得 CHAR
SQL> select coalesce(value3, null, to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test;
VALUE3
--------------
05-8月 -16
31-7月 -16
01-1月 -10
PostgreSQL NULL判断函数
postgres=# select * from p_test;
value1 | value2 | value3
--------+--------+---------------------
| 11111 | 2010-01-01 00:00:00
1 | | 2010-01-01 00:00:00
2 | 22222 |
postgres=# select coalesce(value1, 'Hello') value1 from p_test;
错误: 无效的整数类型输入语法: "Hello"
第1行select coalesce(value1, 'Hello') value1 from p_test;
postgres=# select coalesce(value1, '10000') value1 from p_test;
value1
--------
10000
1
2
postgres=# select coalesce(value2, null, 'Hello world') value2 from p_test;
value2
-------------
11111
Hello world
22222
postgres=# select coalesce(value3, null, '2012-10-10') value3 from p_test;
value3
---------------------
2010-01-01 00:00:00
2016-08-05 10:01:32
2012-10-10 00:00:00
postgres=# select coalesce(value3, null, '2012-10-A') value3 from p_test;
错误: 无效的类型 timestamp 输入语法: "2012-10-A"
第1行select coalesce(value3, null, '2012-10-A') value3 from p_te...
2、字符串连接
2.1、字符串连接符( || )
Oracle的字符串连接符(||) 和 PostgreSQL的字符串连接符(||)的用法基本相同，不同的地方是
1、当连接的参数有null的时候，Oracle中，null 的连接效果类似于空字符串('')，而PostgreSQL中， 连接的参数中有null的， 连接结果统一都是null。
2、当几个参数都是数字的时候，Oracle会自动把数字转换为字符串。这个和Oracle内部的自动类型转换有关系。而PostgreSQL中，几个参数中至少有一个应该为字符串，否则会报错。
数据迁移的时候，对于Oracle的 A || B 可以使用PostgreSQL的coalesce( A, '') || coalesce( B, '')形式来转换。
Oracle 字符串连接符( || )
SQL> select 'abc' || 'def' value from dual;
VALUE
------
abcdef
SQL> select 123 || 456 value from dual;
VALUE
------
123456
SQL> select null || 456 value from dual;
VAL
---
456
SQL> select null || 'abcdef' value from dual;
VALUE
------
abcdef
SQL> select length(null || null) value from dual;
VALUE
----------
PostgreSQL 字符串连接符( || )
postgres=# select 'abc' || 'def' as value;
value
--------
abcdef
postgres=# select 123 || 456 as value;
错误: 操作符不存在: integer || integer
第1行select 123 || 456 as value;
提示: 没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
postgres=# select 123||'456' as value;
value
--------
123456
postgres=# select null || 456 as value ;
value
-------
postgres=# select null || 'abcdef' as value;
value
-------
postgres=# select length(null || null) as value ;
value
-------
2.2、字符串连接函数concat
Oracle的concat函数类似于字符串连接符（||），但只能够连接两个参数。参数需要是字符串类型，或者可以自动转换成字符串类型。PostgreSQL中也内置了这个方法。
需要注意的是，Oracle的concat，如果两个参数都是null, 则结果是null。而PostgreSQL中，如果两个参数都是null，则结果是空字符串('')。因为PostgreSQL的concat方法内部对于参数做了coalesce(null, '')处理。
Oracle concat
SQL> select concat('abc','def') from dual;
CONCAT
------
abcdef
SQL> select concat(123, 456) from dual;
CONCAT
------
123456
SQL> select concat(null, 456) value from dual;
VAL
---
456
SQL> select concat(null, 'abc') value from dual;
VAL
---
abc
SQL> select concat(null, null) value from dual;
V
-
SQL> select length(concat(null, null)) value from dual;
VALUE
----------
SQL> select * from o_test;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
111111 05-8月 -16
1 31-7月 -16
2 222222
SQL> select concat(value3, value2) from o_test;
CONCAT(VALUE3,VALUE2)
------------------------
05-8月 -16111111
31-7月 -16
222222
PostgreSQL 字符串连接函数
postgres=# select concat('abc','def');
concat
--------
abcdef
postgres=# select concat(123, 456);
concat
--------
123456
postgres=# select concat(null, 456) as value;
value
-------
456
postgres=# select concat(null, 'abc') as value;
value
-------
abc
postgres=# select concat(null, null) as value;
value
-------
postgres=# select length(concat(null, null)) as value;
value
-------
0
postgres=# select * from p_test;
value1 | value2 | value3
--------+--------+---------------------
| 11111 | 2010-01-01 00:00:00
1 | | 2016-08-05 10:01:32
2 | 22222 |
postgres=# select concat(value3, value2) as value from p_test;
value
--------------------------
2010-01-01 00:00:0011111
2016-08-05 10:01:32
22222
--------------------------------------------------------------
二
1、to_number函数
to_number用于将字符类型转换成数字。主要使用在显示格式的控制以及排序等地方。特别是排序的时候，因为按照字符排序和按照数字排序，结果是不同的。
Oracle的to_number函数接受两个参数。第一个参数是需要转换的字符串，第二个参数是要转换的格式。实际使用的时候，除非采用特殊的格式显示，否则一个参数就已经足够了。
PostgreSQL的to_number函数也接受两个参数。使用的时候，不能只使用一个参数。排序的时候，格式字符串中需要设置转换的位数为该字段的最大位数。否则，只使用格式提供的转换位数来排序。比如格式提供了两位，那么就转换最前面的两位字符为数字，然后使用它来排序。
Oracle to_number函数
SQL> desc o_test;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(10)
AGE VARCHAR2(10)
SQL> select * from o_test;
ID NAME AGE
---------- ---------- ----------
赵大 20
钱二 9
孙三 30
李四 110
SQL> select * from o_test order by age;
ID NAME AGE
---------- ---------- ----------
李四 110
赵大 20
孙三 30
钱二 9
SQL> select * from o_test order by to_number(age);
ID NAME AGE
---------- ---------- ----------
钱二 9
赵大 20
孙三 30
李四 110
SQL> select id, name, to_number(age) age from o_test order by to_number(age);
ID NAME AGE
---------- ---------- ----------
钱二 9
赵大 20
孙三 30
李四 110
PostgreSQL to_number函数
postgres=# \d p_test;
数据表 "public.p_test"
栏位 | 类型 | 修饰词
------+-----------------------+----------------------------------------------
id | integer | 非空 默认 nextval('p_test_id_seq'::regclass)
name | character varying(10) |
age | character varying(10) |
postgres=# select * from p_test;
id | name | age
----+------+-----
1 | 赵大 | 20
2 | 钱二 | 9
3 | 孙三 | 30
4 | 李四 | 110
postgres=# select * from p_test order by age;
id | name | age
----+------+-----
4 | 李四 | 110
1 | 赵大 | 20
3 | 孙三 | 30
2 | 钱二 | 9
postgres=# select * from p_test order by to_number(age);
错误: 函数 to_number(character varying) 不存在
第1行select * from p_test order by to_number(age);
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select * from p_test order by to_number(age,'9999999999');
id | name | age
----+------+-----
2 | 钱二 | 9
1 | 赵大 | 20
3 | 孙三 | 30
4 | 李四 | 110
postgres=# select * from p_test order by to_number(age,'99');
id | name | age
----+------+-----
2 | 钱二 | 9
4 | 李四 | 110
1 | 赵大 | 20
3 | 孙三 | 30
postgres=# select id,name, to_number(age,'9999999999') from p_test order by to_number(age,'9999999999');
id | name | to_number
----+------+-----------
2 | 钱二 | 9
1 | 赵大 | 20
3 | 孙三 | 30
4 | 李四 | 110
2、decode函数
decode是Oracle固有的一个函数，用于条件判断。其格式为
decode(条件, 值1, 返回值1, 值2, 返回值2,... 值n, 返回值n, 缺省值)。当条件等于值1的时候返回返回值1，······等于值n的时候返回返回值n。都不等于的时候返回缺省值。
PostgreSQL中，decode函数使用来解码的，和encode函数相对。对于Oracle的decode函数，可以把它转换成case......when....的SQL语句，得到一样的效果。
Oracle也支持case....when。用法和PostgreSQL中类似。
Oracle
SQL> select * from o_test;
ID NAME AGE
---------- ---------- ----------
赵大 20
钱二 9
孙三 30
李四 110
SQL> select decode(age, '20', '赵大', '9', '钱二', '张三') testname from o_test;
TEST
----
赵大
钱二
张三
张三
SQL> select case age when '20' then '赵大' when '9' then '钱二' else '张三' end testname from o_test;
TEST
----
赵大
钱二
张三
张三
SQL> select case when age = '20' then '赵大' when age= '9' then '钱二' else '张三' end testname from o_test;
TEST
----
赵大
钱二
张三
张三
PostgreSQL
postgres=# select * from p_test;
id | name | age
----+------+-----
1 | 赵大 | 20
2 | 钱二 | 9
3 | 孙三 | 30
4 | 李四 | 110
postgres=# select case age when '20' then '赵大' when '9' then '钱二' else '张三' end testname from p_test;
testname
----------
赵大
钱二
张三
张三
postgres=# select case when age='20' then '赵大' when age= '9' then '钱二' else '张三' end testname from p_test;
testname
----------
赵大
钱二
张三
张三
postgres=# select encode('abcdefghijklmn', 'base64');
encode
----------------------
YWJjZGVmZ2hpamtsbW4=
postgres=# select decode('YWJjZGVmZ2hpamtsbW4=', 'base64');
decode
--------------------------------
\x6162636465666768696a6b6c6d6e
3、instr函数
Oracle的instr函数是查找一个字符串中，另一个字符串所在的位置。如果找不到则返回0。instr一共有四个参数。前两个分别表示源字符串和查找字符串，第三个表示开始位置(<0的时候表示从右望左找)。第四个表示第几次出现的值。PostgreSQL中，可以使用position(substring in string) 函数来对应它。position函数没有Oracle的那么复杂，有些复杂的功能只能使用自定义函数来实现它。
Oracle instr
SQL> select instr('helloworld', 'l') from dual;
INSTR('HELLOWORLD','L')
-----------------------
3
SQL> select instr('helloworld', 'l', 5) from dual;
INSTR('HELLOWORLD','L',5)
-------------------------
9
SQL> select instr('helloworld', 'l', -5) from dual;
INSTR('HELLOWORLD','L',-5)
--------------------------
4
SQL> select instr('helloworld', 'l', 4, 2) from dual;
INSTR('HELLOWORLD','L',4,2)
---------------------------
9
PostgreSQL position
postgres=# select position('l' in 'helloworld');
position
----------
3
postgres=# select length(substring('helloworld', 1, 4)) + position('l' in substring('helloworld',5));
?column?
----------
9
postgres=# select instr('helloworld', 'l', -5, 1) ;
错误: 函数 instr(unknown, unknown, integer, integer) 不存在
第1行select instr('helloworld', 'l', -5, 1) ;
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# CREATE FUNCTION instr(string varchar, string_to_search varchar,
postgres(# beg_index integer, occur_index integer)
postgres-# RETURNS integer AS
$$
postgres$# DECLARE
postgres$# pos integer NOT NULL DEFAULT 0;
postgres$# occur_number integer NOT NULL DEFAULT 0;
postgres$# temp_str varchar;
postgres$# beg integer;
postgres$# i integer;
postgres$# length integer;
postgres$# ss_length integer;
postgres$# BEGIN
postgres$# IF beg_index > 0 THEN
postgres$# beg := beg_index;
postgres$# temp_str := substring(string FROM beg_index);
postgres$# FOR i IN 1..occur_index LOOP
postgres$# pos := position(string_to_search IN temp_str);
postgres$# IF i = 1 THEN
postgres$# beg := beg + pos - 1;
postgres$# ELSE
postgres$# beg := beg + pos;
postgres$# END IF;
postgres$# temp_str := substring(string FROM beg + 1);
postgres$# END LOOP;
postgres$# IF pos = 0 THEN
postgres$# RETURN 0;
postgres$# ELSE
postgres$# RETURN beg;
postgres$# END IF;
postgres$# ELSE
postgres$# ss_length := char_length(string_to_search);
postgres$# length := char_length(string);
postgres$# beg := length + beg_index - ss_length + 2;
postgres$# WHILE beg > 0 LOOP
postgres$# temp_str := substring(string FROM beg FOR ss_length);
postgres$# pos := position(string_to_search IN temp_str);
postgres$# IF pos > 0 THEN
postgres$# occur_number := occur_number + 1;
postgres$# IF occur_number = occur_index THEN
postgres$# RETURN beg;
postgres$# END IF;
postgres$# END IF;
postgres$# beg := beg - 1;
postgres$# END LOOP;
postgres$# RETURN 0;
postgres$# END IF;
postgres$#
postgres$# END;
postgres$#
$$
LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# select instr('helloworld', 'l', -5, 1) ;
instr
-------
4
postgres=# select instr('helloworld', 'l', 4, 2);
instr
-------
9
--------------------------------------------------------------
三
1、substr
substr函数是截取一个字符串的一部分，格式是substr(字符串，开始位置，长度)。Oracle和PostgreSQL都支持这个函数。但是具体使用的时候，有些不一样的地方。Oracle中，substr是一系列函数。还包括substrb, substrc,substr2,substr4等多个。分别是按照字符(substr)截取、按字节(substrb)截取、按Unicode码(substrc)截取、按照UCS2编码(substr2)截取和按照UCS4码(substr4)截取。这几个函数的用法是一致的。
PostgreSQL中，substr方法只支持字符方式。不支持其余的方式。参数和Oracle是一样的。而且还有一个叫substring方法，和substr方法是一样的。
Oracle和PostgreSQL中的用法，区别就是当开始位置=0的时候，Oracle表示的含义和1是一样的，从第一个字符开始截取。而PostgreSQL相当于第一个字符再往前一个空字符。当第二个参数<0的时候，Oracle代表开始位置从右侧开始算。而PostgreSQL则表示则是从左侧望再前追加空字符。另外PostgreSQL参数还支持使用关键字from 和 for的写法，分别表示开始位置和长度。
Oracle substr
SQL> select substr('bbb姑苏城外寒山寺aaa',4) from dual;
SUBSTR('BBB姑苏城
-----------------
姑苏城外寒山寺aaa
SQL> select substr('bbb姑苏城外寒山寺aaa',5, 5) from dual;
SUBSTR('BB
----------
苏城外寒山
SQL> select substrb('bbb姑苏城外寒山寺aaa',5, 5) from dual;
SUBST
-----
苏城
SQL> select substrc('bbb姑苏城外寒山寺aaa',5, 5) from dual;
SUBSTRC('B
----------
苏城外寒山
SQL> select substr2('bbb姑苏城外寒山寺aaa',5, 5) from dual;
SUBSTR2('B
----------
苏城外寒山
SQL> select substr4('bbb姑苏城外寒山寺aaa',5, 5) from dual;
SUBSTR4('B
----------
苏城外寒山
SQL> select substr('bbb姑苏城外寒山寺aaa',-8, 5) from dual;
SUBSTR('BB
----------
城外寒山寺
SQL> select substr('bbb姑苏城外寒山寺aaa',0, 5) from dual;
SUBSTR(
-------
bbb姑苏
PostgreSQL substr
postgres=# select substr('bbb姑苏城外寒山寺aaa',4);
substr
-------------------
姑苏城外寒山寺aaa
postgres=# select substr('bbb姑苏城外寒山寺aaa',5, 5);
substr
------------
苏城外寒山
postgres=# select substr('bbb姑苏城外寒山寺aaa',0, 10);
substr
-----------------
bbb姑苏城外寒山
postgres=# select substr('bbb姑苏城外寒山寺aaa',-5, 10);
substr
--------
bbb姑
postgres=# select substring('bbb姑苏城外寒山寺aaa' from 4);
substring
-------------------
姑苏城外寒山寺aaa
postgres=# select substring('bbb姑苏城外寒山寺aaa' from 5 for 5);
substring
------------
苏城外寒山
postgres=# select substring('bbb姑苏城外寒山寺aaa' from 0 for 10);
substring
-----------------
bbb姑苏城外寒山
postgres=# select substring('bbb姑苏城外寒山寺aaa' from -5 for 10);
substring
-----------
bbb姑
2、length
length(字符串)函数是求得字符串的长度。Oracle和PostgreSQL都支持这个函数。
Oracle中，length是一系列函数。还包括lengthb, lengthc, length2, length4等多个。分别是按照字符(length)取长度、按字节(lengthb)取长度、按Unicode码(lengthc)取长度、按照UCS2编码(length2)取长度和按照UCS4码(length4)取长度。这几个函数的用法是一致的。
PostgreSQL中，length方法只支持字符方式， 不支持其余的方式。参数和Oracle是一样的。迁移的时候，按照字符以外的方式取长度，PostgreSQL还不支持。
Oracle length
SQL> select length('bbb姑苏城外寒山寺aaa') from dual;
LENGTH('BBB姑苏城外寒山寺AAA')
------------------------------
13
SQL> select lengthb('bbb姑苏城外寒山寺aaa') from dual;
LENGTHB('BBB姑苏城外寒山寺AAA')
-------------------------------
20
PostgreSQL length
postgres=# select length('bbb姑苏城外寒山寺aaa');
length
--------
13
postgres=# select lengthb('bbb姑苏城外寒山寺aaa');
错误: 函数 lengthb(unknown) 不存在
第1行select lengthb('bbb姑苏城外寒山寺aaa');
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
3、trim/ltrim/rtrim函数
trim函数用来除去字符串开头和结尾的指定字符（默认是空格）。ltrim可以除去左侧开头的指定字符，rtrim除去右侧开头的指定字符。
Oracle的trim只能除去一个特定字符。两个以上不支持。改变字符的时候，使用关键字from。比如trim('a' from 'aabbccaa')这种写法。不支持trim('aabbcc', 'a')的这种写法。但是ltrim和rtrim支持多个字符。并且支持ltrim('aabbcc', 'a')和rtrim('aabbcc', 'c')这种写法，而不支持from关键字的写法。另外，trim的from关键字的写法中还支持Both/ leading/ trailing三个关键字，分别代表除去 开头和结尾 / 开头 / 结尾的指定字符。
PostgreSQL中，都支持除去开始结尾的多个字符。并且PostgreSQL中， trim的两种写法都是支持的，而ltrim和rtrim和oracle一样, 支持ltrim('aabbcc', 'a')和rtrim('aabbcc', 'c')这种写法，而不支持from关键字的写法。PostgreSQL的trim的from关键字的写法也支持Both/ leading/ trailing三个关键字，分别代表除去 开头和结尾 / 开头 / 结尾的指定字符。
迁移的时候，可以直接迁移。
Oracle trim
SQL> select trim(' aa ' ) from dual;
TR
--
aa
SQL> select ltrim(' aa ') from dual;
LTR
---
aa
SQL> select rtrim(' aa ') from dual;
RTR
---
aa
SQL> select trim('aabbccaa', 'a') from dual;
select trim('aabbccaa', 'a') from dual
第 1 行出现错误:
ORA-00907: 缺失右括号
SQL> select trim('a' from 'aabbccaa') from dual;
TRIM
----
bbcc
SQL> select trim('ab' from 'aabbccaa') from dual;
select trim('ab' from 'aabbccaa') from dual
第 1 行出现错误:
ORA-30001: 截取集仅能有一个字符
SQL> select ltrim('ab' from 'aabbccaa') from dual;
select ltrim('ab' from 'aabbccaa') from dual
第 1 行出现错误:
ORA-00907: 缺失右括号
SQL> select ltrim('aabbccaa','ab') from dual;
LTRI
----
ccaa
SQL> select rtrim('aabbccaa','ab') from dual;
RTRIM(
------
aabbcc
SQL> select trim(both 'a' from 'abcdefa') from dual;
TRIM(
-----
bcdef
SQL> select trim(leading 'a' from 'abcdefa') from dual;
TRIM(L
------
bcdefa
SQL> select trim(trailing 'a' from 'abcdefa') from dual;
TRIM(T
------
abcdef
PostgreSQL trim
postgres=# select trim(' aa ') aa ;
aa
----
aa
postgres=# select ltrim(' aa ') aa ;
aa
-----
aa
postgres=# select rtrim(' aa ') aa ;
aa
-----
aa
postgres=# select trim('aabbccaa', 'a') aa;
aa
------
bbcc
postgres=# select trim('ab' from 'aabbccaa') aa;
aa
----
cc
postgres=# select ltrim('ab' from 'aabbccaa') aa;
错误: 语法错误 在 "from" 或附近的
第1行select ltrim('ab' from 'aabbccaa') aa;
postgres=# select ltrim('aabbccaa','ab') ;
ltrim
-------
ccaa
postgres=# select rtrim('aabbccaa','ab') ;
rtrim
--------
aabbcc
postgres=# select trim(both 'a' from 'abcdefa') ;
btrim
-------
bcdef
postgres=# select trim(leading 'a' from 'abcdefa') ;
ltrim
--------
bcdefa
postgres=# select trim(trailing 'a' from 'abcdefa') ;
rtrim
--------
abcdef
--------------------------------------------------------------
四
1、NLSSORT
Oracle的NLSSORT可以改变取得字段的排序方式。默认的情况下，西文的排序是按照二进制编码的顺序排序。对于汉字按照拼音来排序。可以使用NLSSORT来改变字段的默认排序方式，从而取得不同的顺序。
以中文为例，排序可以有：
按照部首（第一顺序）、笔划（第二顺序）排序（SCHINESE_RADICAL_M）
按照笔划（第一顺序）、部首（第二顺序）排序（SCHINESE_STROKE_M）
按照拼音排序（SCHINESE_PINYIN_M）
等三种。默认是按照拼音来排序。
不使用参数的时候，自动采用Session内的默认值。
PostgreSQL中，对于西文按照二进制，对于汉字只是按照拼音来排序。同一种编码，其余的排序方式尚不支持。
Oracle NLSSORT
SQL> select * from test;
NAME
---------------
横看成岭侧成峰
远近高低各不同
不识庐山真面目
只缘身在此山中
SQL> select * from test order by name;
NAME
---------------
不识庐山真面目
横看成岭侧成峰
远近高低各不同
只缘身在此山中
SQL> select * from test order by nlssort(name);
NAME
---------------
不识庐山真面目
横看成岭侧成峰
远近高低各不同
只缘身在此山中
SQL> select * from test order by nlssort(name, 'NLS_SORT=SCHINESE_STROKE_M');
NAME
---------------
不识庐山真面目
只缘身在此山中
远近高低各不同
横看成岭侧成峰
SQL> select * from test order by nlssort(name, 'NLS_SORT=SCHINESE_PINYIN_M');
NAME
---------------
不识庐山真面目
横看成岭侧成峰
远近高低各不同
只缘身在此山中
SQL> select * from test order by nlssort(name, 'NLS_SORT=SCHINESE_RADICAL_M');
NAME
---------------
不识庐山真面目
只缘身在此山中
横看成岭侧成峰
远近高低各不同
PostgreSQL NLSSORT
postgres=# select * from test;
name
----------------
横看成岭侧成峰
远近高低各不同
不识庐山真面目
只缘身在此山中
postgres=# select * from test order by name;
name
----------------
不识庐山真面目
横看成岭侧成峰
远近高低各不同
只缘身在此山中
2、NLS_INITCAP/ NLS_LOWER/ NLS_UPPER
Oracle的NLS_INITCAP(x[,'NLS_SORT=y']) 、NLS_LOWER(x[,'NLS_SORT=y'])、NLS_UPPER(x[,'NLS_SORT=y']) 三个函数都有两个参数，可以根据y参数的不同，提供不同于系统默认值的结果。如果不提供这个参数，那么结果和initcap、lower和upper三个函数是一样的。
另外，Oracle的官方文档（http://docs.oracle.com/database/121/SQLRF/functions122.htm#SQLRF00676 ）上，NLS_INITCAP的例子：
SELECT NLS_INITCAP('ijsland') "InitCap" FROM DUAL;
InitCap
-------
Ijsland
SELECT NLS_INITCAP('ijsland', 'NLS_SORT = XDutch') "InitCap" FROM DUAL;
InitCap
-------
IJsland
我这边做不出相同的效果。网上也没搜到。怀疑是老版的Oracle有这种做法，新版已经取消。PostgreSQL中，对于同一种编码，没有提供多种方式排序。只有简单的Initcap, lower和upper函数。另外，在实际使用中，这几个带NLS_的方法都几乎用不到。
Oracle NLS_INITCAP/ NLS_LOWER/ NLS_UPPER
SQL> select nls_initcap('ijslang') "test" from dual;
test
-------
Ijslang
SQL> select nls_initcap('ijslang', 'NLS_SORT=XDutch') "test" from dual;
test
-------
Ijslang
SQL> select initcap('ijslang') "test" from dual;
test
-------
Ijslang
SQL> SELECT NLS_LOWER('NOKTASINDA', 'NLS_SORT = XTurkish') "Lowercase" from dual;
Lowercase
----------
noktasinda
SQL> SELECT NLS_LOWER('NOKTASINDA') "Lowercase" from dual;
Lowercase
----------
noktasinda
SQL> SELECT LOWER('NOKTASINDA') "Lowercase" from dual;
Lowercase
----------
noktasinda
SQL> SELECT NLS_UPPER('große') "Uppercase" from dual;
Upper
-----
GRO?E
SQL> SELECT NLS_UPPER('große','NLS_SORT=XGerman') "Uppercase" from dual;
Upper
-----
GRO?E
SQL> SELECT UPPER('große') "Uppercase" from dual;
Upper
-----
GRO?E
PostgreSQL INITCAP/ LOWER/ UPPER
postgres=# select initcap('ijslang');
initcap
---------
Ijslang
postgres=# select LOWER('NOKTASINDA');
lower
------------
noktasinda
postgres=# select upper('große');
upper
-------
GRO?E
PostgreSQL 9.4.4 中文手册：字符串函数
http://www.postgres.cn/docs/9.4/functions-string.html
Database SQL Language Reference：
http://docs.oracle.com/database/121/SQLRF/functions122.htm#SQLRF00676
http://docs.oracle.com/database/121/SQLRF/functions123.htm#SQLRF00677
http://docs.oracle.com/database/121/SQLRF/functions125.htm#SQLRF00678
http://docs.oracle.com/database/121/SQLRF/functions124.htm#SQLRF00679
--------------------------------------------------------------
五
1、regexp_replace
regexp_replace是使用正则表达式进行替换的函数。源字符串里面符合正则表达式的，替换成目标字符串。Oracle和PostgreSQL都支持regexp_replace，但是参数有些不同。
Oracle的regexp_replace共有六个参数。分别表示源字符串，正则表达式，目标字符串，开始位置(默认为1)，替换第几个匹配（默认全部）和一个flg值（'i'：大小写不敏感 ‘c’：大小写敏感 'n'：点号，不匹配换行符号 'm‘：多行模式 'x'：扩展模式，忽略正则表达式中的空白字符）。
PostgreSQL中的regexp_replace只有四个参数，分别表示源字符串，正则表达式，目标字符串，替换规则(默认只替换第一个。当替换规则为'g'的时候代表全部替换)。功能上没有Oracle的regexp_replace的功能强。有些比较复杂的替换，只能自己通过函数实现。
Oracle regexp_replace
SQL> select regexp_replace('abc123xyz888', '[0-9]+') from dual;
REGEXP
------
abcxyz
SQL> select regexp_replace('abc123xyz888', '[0-9]+', 'ZZZ') from dual;
REGEXP_REPLA
------------
abcZZZxyzZZZ
SQL> select regexp_replace('abc123xyz888', '[0-9]+', 'ZZZ', 8) from dual;
REGEXP_REPLA
------------
abc123xyzZZZ
SQL> select regexp_replace('abc123xyz888', '[0-9]+', 'ZZZ', 1, 2) from dual;
REGEXP_REPLA
------------
abc123xyzZZZ
SQL> select regexp_replace('abc123xyz888', '[a-c]+', 'ZZZ', 1, 1, 'i') from dual;
REGEXP_REPLA
------------
ZZZ123xyz888
SQL> select regexp_replace('abc123xyz888', '[A-C]+', 'ZZZ', 1, 1, 'i') from dual;
REGEXP_REPLA
------------
ZZZ123xyz888
SQL> select regexp_replace('abc123xyz888', '[A-C]+', 'ZZZ', 1, 1, 'c') from dual;
REGEXP_REPLA
------------
abc123xyz888
PostgreSQL regexp_replace
postgres=# select regexp_replace('abc123xyz888', '[0-9]+') ;
错误: 函数 regexp_replace(unknown, unknown) 不存在
第1行select regexp_replace('abc123xyz888', '[0-9]+') ;
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select regexp_replace('abc123xyz888', '[0-9]+', 'ZZZ');
regexp_replace
----------------
abcZZZxyz888
postgres=# select regexp_replace('abc123xyz888', '[0-9]+', 'ZZZ', 'g');
regexp_replace
----------------
abcZZZxyzZZZ
2、regexp_substr
regexp_substr是Oracle的使用正则表达式进行字符串截取的函数。PostgreSQL中的substring函数中可以实现它的简单功能，但是没有Oracle的功能强。如果需要实现它的复杂功能，需要自己使用函数实现。
Oracle的regexp_substr函数有五个参数。分别表示源字符串，正则表达式，开始位置(默认为1)，截取第几个匹配（默认第一个）和一个flg值（'i'：大小写不敏感 ‘c’：大小写敏感 'n'：点号，不匹配换行符号 'm‘：多行模式 'x'：扩展模式，忽略正则表达式中的空白字符）。
PostgreSQL中的regexp_substr只有三个参数，分别表示源字符串，正则表达式，逃匿符。
Oracle regexp_substr
SQL> select regexp_substr('abc123abc888', '[a-c]+') from dual;
REG
---
abc
SQL> select regexp_substr('abc123abc888', '[a-c]+', 2) from dual;
RE
--
bc
SQL> select regexp_substr('abc123abc888', '[a-c]+', 5) from dual;
REG
---
abc
SQL> select regexp_substr('abc123abc888', '[a-c]+', 1, 2) from dual;
REG
---
abc
SQL> select regexp_substr('abc123abc888', '[A-C]+', 1, 1) from dual;
R
-
SQL> select regexp_substr('abc123abc888', '[A-C]+', 1, 1, 'i') from dual;
REG
---
abc
SQL> select regexp_substr('abc123abc888', '[A-C]+', 1, 1, 'c') from dual;
R
-
PostgreSQL substring
postgres=# select substring('abc123abc888', '[a-c]+') ;
substring
-----------
abc
postgres=# select substring('abc123abc888' from '[a-c]+') ;
substring
-----------
abc
postgres=# select substring('Thomas' , '%#"o_a#"%' ,'#');
substring
-----------
oma
postgres=# select substring('Thomas' from '%#"o_a#"%' for '#');
substring
-----------
oma
3、regexp_instr
regexp_instr是Oracle的一个搜索字符串中符合表达式的字串位置的一个函数。在PostgreSQL中没有对应的函数，需要自己写函数实现它。最简单的功能，可以使用position函数和substring函数来模拟实现。
Oracle regexp_instr
SQL> select regexp_instr('abc123abc156', '[1-2]+') from dual;
REGEXP_INSTR('ABC123ABC156','[1-2]+')
-------------------------------------
4
SQL> select regexp_instr('abc123abc156', '[1-2]+', 7) from dual;
REGEXP_INSTR('ABC123ABC156','[1-2]+',7)
---------------------------------------
10
SQL> select regexp_instr('abc123abc156', '[1-2]+', 1, 2) from dual;
REGEXP_INSTR('ABC123ABC156','[1-2]+',1,2)
-----------------------------------------
10
SQL> select regexp_instr('abc123abc156', '[1-2]+', 1, 1, 0) from dual;
REGEXP_INSTR('ABC123ABC156','[1-2]+',1,1,0)
-------------------------------------------
4
SQL> select regexp_instr('abc123abc156', '[1-2]+', 1, 1, 1) from dual;
REGEXP_INSTR('ABC123ABC156','[1-2]+',1,1,1)
-------------------------------------------
6
SQL> select regexp_instr('abc123abc156', '[A-C]+', 1, 2, 0, 'i') from dual;
REGEXP_INSTR('ABC123ABC156','[A-C]+',1,2,0,'I')
-----------------------------------------------
7
SQL> select regexp_instr('abc123abc156', '[A-C]+', 1, 2, 0, 'c') from dual;
REGEXP_INSTR('ABC123ABC156','[A-C]+',1,2,0,'C')
-----------------------------------------------
0
PostgreSQL position substring
postgres=# select position(substring('abc123abc156' from '[1-2]+') in 'abc123abc156');
position
----------
4
postgres=# select 7 - 1 + position(substring(substring('abc123AC158',7) from '[1-2]+') in 'abc123AC158');
?column?
----------
10
4、regexp_like
regexp_like是Oracle的一个检索正则表达式条件的一个函数，只能用在条件判断部分。在PostgreSQL中没有对应的函数，需要自己写函数实现它。最简单的功能，可以简单的使用substring和length函数来模拟。
Oracle regexp_like
SQL> select * from o_test;
ID NAME AGE
---------- ---------- ----------
1 James1234 20
2 De Haan 20
2 Greenberg 20
2 TomLiu 20
SQL> select * from o_test where regexp_like(name, '([aeiou])\1');
ID NAME AGE
---------- ---------- ----------
2 De Haan 20
2 Greenberg 20
SQL> select * from o_test where regexp_like(name, '([AEIOU])\1');
未选定行
SQL> select * from o_test where regexp_like(name, '([AEIOU])\1', 'i');
ID NAME AGE
---------- ---------- ----------
2 De Haan 20
2 Greenberg 20
SQL> select * from o_test where regexp_like(name, '([AEIOU])\1', 'c');
未选定行
PostgreSQL length substring
postgres=# select * from p_test where length(substring(name, '([aeiou])\1')) > 0;
id | name | age
----+-----------+-----
2 | De Haan | 20
3 | Greenberg | 20
postgres=# select * from p_test where length(substring(name, '([AEIOU])\1')) > 0;
id | name | age
----+------+-----
postgres=# select * from p_test where length(substring(name, '([AEIOUaeiou])\1')) > 0;
id | name | age
----+-----------+-----
2 | De Haan | 20
3 | Greenberg | 20
参考文档：
PostgreSQL 9.4.4 中文手册：
http://www.postgres.cn/docs/9.4/functions-string.html (字符串函数和操作符）
Database SQL Language Reference：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions148.htm#SQLRF06300
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions149.htm#SQLRF06302
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions150.htm#SQLRF06303
http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions007.htm#SQLRF00501
--------------------------------------------------------------
六
1、NVL2
NVL2(expr1, expr2, expr3) 是Oracle的一个NULL判断函数。当expr1 不为 NULL时，返回expr2；为NULL时，返回 expr3。
PostgreSQL中没有对应的函数。 可以使用case when实现相同的意思。
Oracle NVL2
SQL> select * from o_test;
ID NAME AGE
---------- ---------- ----------
1
2
2 zhangsan 18
SQL> select nvl2(name, '有人', '无人') FLG from o_test;
FLG
----
无人
无人
有人
SQL> select case when name is not null then '有人' else '无人' end FLG from o_test;
FLG
----
无人
无人
有人
PostgreSQL CASE
postgres=# select * from p_test;
id | name | age
----+----------+-----
1 | |
2 | |
2 | zhangsan | 18
postgres=# select case when name is not null then '有人' else '无人' end FLG from p_test;
flg
------
无人
无人
有人
2、LNNVL
LNNVL(condition) 是Oracle中进行条件判断的一个函数。当条件返回true时，返回false。当条件为unknow或者返回false的时候返回true。类似于 CASE XXX WHEN is null or is not true THEN true ELSE false END。PostgreSQL中，没有对应的函数，只能使用CASE WHEN函数进行模拟。
Oracle LNNVL
SQL> select * from o_test;
ID NAME AGE
---------- ---------- ----------
1
2
2 zhangsan 18
SQL> select case when lnnvl(name = 'zhangsan') then '无人' else '有人' end flg from o_test;
FLG
----
无人
无人
有人
SQL> select case when name = 'zhangsan' then '有人' else '无人' end FLG from o_test;
FLG
----
无人
无人
有人
PostgreSQL CASE WHEN
postgres=# select * from p_test;
id | name | age
----+----------+-----
1 | |
2 | |
2 | zhangsan | 18
postgres=# select case when name ='zhangsan' then '有人' else '无人' end FLG from p_test;
flg
------
无人
无人
有人
3、BITAND
BITAND(expr1, expr2)函数是Oracle里面的一个对于数字按位进行 AND 运算的函数。PostgreSQL中没有对应的函数，可以直接使用 a&b的方式进行模拟。
Oracle BITAND
SQL> SELECT BITAND(6,3) FROM dual;
BITAND(6,3)
-----------
2
SQL> SELECT BITAND(6, 5) FROM dual;
BITAND(6,5)
-----------
4
PostgreSQL 数字按位与运算
postgres=# SELECT BITAND(6,3) ;
错误: 函数 bitand(integer, integer) 不存在
第1行SELECT BITAND(6,3) ;
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select 6&3;
?column?
----------
2
postgres=# select 6&5;
?column?
----------
4
4、REMAINDER
REMAINDER(n2, n1) 是 Oracle的一个计算余数的函数。它返回 n2/n1 的余数。 和MOD的不同的地方在于： REMAINDER相当于n2-n1 ROUND(n2/n1)。而MOD相当于n2-n1 FLOOR(n2/n1)。PostgreSQL中没有这个 函数。可以使用它的元计算式n2-n1* ROUND(n2/n1)的 形式模拟它。
Oracle REMAINDER
SQL> Select remainder(3.5,2) from dual;
REMAINDER(3.5,2)
----------------
-.5
SQL> select 3.5 - 2 * ROUND(3.5/2) from dual;
3.5-2*ROUND(3.5/2)
------------------
-.5
SQL> select mod(3.5, 2) from dual;
MOD(3.5,2)
----------
1.5
PostgreSQL 数字按位与运算
postgres=# select remainder(3.5, 2);
错误: 函数 remainder(numeric, integer) 不存在
第1行select remainder(3.5, 2);
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select 3.5 - 2 * ROUND(3.5/2) ;
?column?
----------
-0.5
postgres=# select mod(3.5, 2) ;
mod
-----
1.5
5、SYSDATE、CURRENT_TIMESTAMP
Oracle的SYSDATE是去当前的日期的一个函数。返回值只有年月日。取得当前时间戳的函数是CURRENT_TIMESTAMP，返回当前的时间戳。PostgreSQL中，可以使用CURRENT_DATE来对应SYSDATE。至于CURRENT_TIMESTAMP，PostgreSQL也支持。也可以使用now()来对应它。
Oracle SYSDATE、CURRENT_TIMESTAMP
SQL> select sysdate from dual;
SYSDATE
--------------
22-8月 -16
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
22-8月 -16 10.28.25.223000 上午 +08:00
PostgreSQL CURRENT_DATE、CURRENT_TIMESTAMP、NOW()
postgres=# select current_date;
date
------------
2016-08-22
postgres=# select current_timestamp;
now
-------------------------------
2016-08-22 10:28:56.689154+08
postgres=# select now();
now
-------------------------------
2016-08-22 10:29:00.466176+08
--------------------------------------------------------------
七
1、ADD_MONTHS函数
ADD_MONTHS(date,integer) 是Oracle的一个时间运算函数。返回参数date日期的integer个月后的日期。是Oracle日期运算的一个很基本的函数。PostgreSQL里面没有这个函数。可以使用Interval数据类型的加减来模拟。返回的是一个timestamp的值。也可以自己创建函数来实现它。
Oracle ADD_MONTHS
SQL> select sysdate from dual;
SYSDATE
--------------
24-8月 -16
SQL> select add_months(sysdate, 2) from dual;
ADD_MONTHS(SYS
--------------
24-10月-16
SQL> select add_months(sysdate, -2) from dual;
ADD_MONTHS(SYS
--------------
24-6月 -16
SQL> select add_months(to_date('2016/08/01', 'YYYY/MM/DD'), -1) from dual;
ADD_MONTHS(TO_
--------------
01-7月 -16
PostgreSQL ADD_MONTHS
postgres=# select now();
now
------------------------------
2016-08-24 09:30:10.87367+08
postgres=# select add_months(now(),2);
错误: 函数 add_months(timestamp with time zone, integer) 不存在
第1行select add_months(now(),2);
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select now() + '2 month';
?column?
-------------------------------
2016-10-24 09:30:35.284043+08
postgres=# select now() + '-2 month';
?column?
------------------------------
2016-06-24 09:30:53.23845+08
postgres=# select to_timestamp('2016-08-01','YYYY-MM-DD') + '-1 month';
?column?
------------------------
2016-07-01 00:00:00+08
postgres=# CREATE FUNCTION add_months(date, int)
postgres-# RETURNS date AS
postgres-# 'SELECT ($1 + ( $2::text || ''months'')::interval)::date;'
postgres-# LANGUAGE 'sql';
CREATE FUNCTION
postgres=# select add_months(current_date, 2);
add_months
------------
2016-10-24
postgres=# select add_months(current_date, -2);
add_months
------------
2016-06-24
postgres=# select add_months('2016/08/01', -1);
add_months
------------
2016-07-01
2、LAST_DAY 函数
LAST_DAY(date)函数是Oracle的一个取日期的月末日的函数。PostgreSQL中没有对应的日期函数。可以自己使用Interval来实现。也可以自己创建函数来实现。
Oracle LAST_DAY
SQL> select sysdate from dual;
SYSDATE
--------------
24-8月 -16
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDA
--------------
31-8月 -16
SQL> select last_day(to_date('2016/09/16', 'YYYY/MM/DD')) from dual;
LAST_DAY(TO_DA
--------------
30-9月 -16
PostgreSQL LAST_DAY
postgres=# CREATE FUNCTION LAST_DAY(DATE)
postgres-# RETURNS DATE AS
postgres-# 'SELECT date(substr(text($1::timestamp +
postgres'# ''1 month''),1,7)||''-01'')-1'
postgres-# LANGUAGE 'sql';
CREATE FUNCTION
postgres=# select current_date;
date
------------
2016-08-24
postgres=# select last_day(current_date);
last_day
------------
2016-08-31
postgres=# select last_day('2016/09/16'::date);
last_day
------------
2016-09-30
3、MONTHS _BETWEEN函数
MONTHS _BETWEEN函数是Oracle的一个判断两个日期之间有几个月的间隔的函数。PostgreSQL中也没有对应的函数，只能自己通过函数实现。不过由于对于相差月份的算法不一样，结果可能有些差异。下面给出一个例子。
Oracle MONTHS _BETWEEN
SQL> SELECT MONTHS_BETWEEN(TO_DATE('02-02-1995','MM-DD-YYYY'),
2 TO_DATE('01-01-1995','MM-DD-YYYY')) MONTHS from dual;
MONTHS
----------
1.03225806
SQL> SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('01-01-2014','MM-DD-YYYY')) MONTHS from dual;
MONTHS
----------
31.7571192
PostgreSQL MONTHS _BETWEEN
postgres=# CREATE FUNCTION MONTHS_BETWEEN(DATE,DATE)
postgres-# RETURNS NUMERIC AS
postgres-# 'SELECT to_number(text(date($1)-
postgres'# date($2)),''999999999'')/31'
postgres-# LANGUAGE 'sql';
CREATE FUNCTION
postgres=# SELECT MONTHS_BETWEEN('1995-02-02'::date,'1995-01-01'::date);
months_between
--------------------
1.0322580645161290
postgres=# SELECT MONTHS_BETWEEN(CURRENT_DATE, '2014-01-01'::DATE) MONTHS;
months
---------------------
31.1612903225806452
4、EXTRACT函数
EXTRACT是Oracle用于从一个date、timestamp或者interval类型中截取到特定的部分所用的函数。Oracle只支持截取年、月、日、时、分、秒，时区时，时区分、时区等。PostgreSQL也支持该函数，截取的东西比Oracle的多一些，但是不支持时区时，时区分和时区的截取（文档中说是支持的，但是我的实际测试中不支持）。
Oracle EXTRACT
SQL> select extract(year from date'2011-05-17') year from dual;
YEAR
----------
2011
SQL> select extract(month from date'2011-05-17') from dual;
EXTRACT(MONTHFROMDATE'2011-05-17')
----------------------------------
5
SQL> select extract(day from date'2011-05-17') from dual;
EXTRACT(DAYFROMDATE'2011-05-17')
--------------------------------
17
SQL> select extract(hour from timestamp'2011-05-17 12:56:20.1234') AAA from dual;
AAA
----------
12
SQL> select extract(minute from timestamp'2011-05-17 12:56:20.1234') part from dual;
PART
----------
56
SQL> select extract(second from timestamp'2011-05-17 12:56:20.1234') part from dual;
PART
----------
20.1234
SQL> select extract(timezone_region from systimestamp) part from dual;
PART
----------------------------------------------------------------
UNKNOWN
SQL> select extract(timezone_abbr from systimestamp) part from dual;
PART
----------
UNK
SQL> select extract(timezone_hour from systimestamp) part from dual;
PART
----------
8
SQL> select extract(timezone_minute from systimestamp) part from dual;
PART
----------
0
PostgreSQL EXTRACT
postgres=# select extract(year from date'2011-05-17') part;
part
------
2011
postgres=# select extract(month from date'2011-05-17') part;
part
------
5
postgres=# select extract(day from date'2011-05-17') part;
part
------
17
postgres=# select extract(week from date'2011-05-17') part;
part
------
20
postgres=# select extract(century from date'2011-05-17') part;
part
------
21
postgres=# select extract(hour from '2011-05-17 12:56:20.1234'::timestamp) AAA;
aaa
-----
12
postgres=# select extract(minute from '2011-05-17 12:56:20.1234'::timestamp) AAA;
aaa
-----
56
postgres=# select extract(second from '2011-05-17 12:56:20.1234'::timestamp) AAA;
aaa
---------
20.1234
postgres=# select extract(microsecond from '2011-05-17 12:56:20.1234'::timestamp) AAA;
aaa
----------
20123400
postgres=# select extract(isodow from '2011-05-17 12:56:20.1234'::timestamp) AAA;
aaa
-----
2
postgres=# select extract(quarter from '2011-05-17 12:56:20.1234'::timestamp) AAA;
aaa
-----
2
postgres=# select extract(timezone from '2011-05-17 12:56:20.1234'::timestamp) AAA;
错误: 不支持时间戳单位 "timezone"
postgres=# select extract(timezone_hour from '2011-05-17 12:56:20.1234'::timestamp) AAA;
错误: 不支持时间戳单位 "timezone_hour"
一、二、三、四、五、六、七
本文总结息《卸甲笔记》，感谢原作者。
--------------------------------------------------------------
一
PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先是迁移数据，然后就是SQL、存储过程、序列等程序中不同的数据库中数据的使用方式的转换。下面根据自己的理解和测试，写了一些SQL以及数据库对象转换方面的文章，不足之处，尚请多多指教。
1、NULL判断函数
Oracle的NULL判断函数是 nvl(A, B) 和 coalesce 两个函数。nvl(A, B) 是判断如果A不为NULL，则返回A，否则返回B。参数需要是相同类型的，或者可以自动转换成相同类型的, 否则需要显式转换。而 coalese 参数可以有多个，返回第一个不为NULL的参数。而参数必须为相同类型的 ，不会自动转换。
PostgreSQL中没有nvl函数。但是有coalesce函数。用法和Oracle的一样。可以使用coalesce来转换Oracle的nvl和coalesce。参数需要使用相同类型，或者可以转换成相同类型的。否则需要手动转换。
Oracle NULL判断函数
SQL> select * from o_test;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
111111 05-8月 -16
1 31-7月 -16
2 222222
SQL> select nvl(value1, 'Hello') value1 from o_test;
select nvl(value1, 'Hello') value1 from o_test
第 1 行出现错误:
ORA-01722: 无效数字
SQL> select nvl(value1, '10000') value1 from o_test;
VALUE1
----------
10000
1
2
SQL> select nvl(value2, 'Hello') value2 from o_test;
VALUE2
----------
111111
Hello
222222
SQL> select nvl(value3, '2010-1-1') value3 from o_test;
select nvl(value3, '2010-1-1') value3 from o_test
第 1 行出现错误:
ORA-01861: 文字与格式字符串不匹配
SQL> select nvl(value3, to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test;
VALUE3
--------------
05-8月 -16
31-7月 -16
01-1月 -10
SQL> select coalesce(value1, '10000') value1 from o_test;
select coalesce(value1, '10000') value1 from o_test
第 1 行出现错误:
ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 CHAR
SQL> select coalesce(value1, 10000) value1 from o_test;
VALUE1
----------
10000
1
2
SQL> select coalesce(value2, '', 'Hello John') value2 from o_test;
VALUE2
----------
111111
Hello John
222222
SQL> select coalesce(value3,'', to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test;
select coalesce(value3,'', to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test
第 1 行出现错误:
ORA-00932: 数据类型不一致: 应为 DATE, 但却获得 CHAR
SQL> select coalesce(value3, null, to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test;
VALUE3
--------------
05-8月 -16
31-7月 -16
01-1月 -10
PostgreSQL NULL判断函数
postgres=# select * from p_test;
value1 | value2 | value3
--------+--------+---------------------
| 11111 | 2010-01-01 00:00:00
1 | | 2010-01-01 00:00:00
2 | 22222 |
postgres=# select coalesce(value1, 'Hello') value1 from p_test;
错误: 无效的整数类型输入语法: "Hello"
第1行select coalesce(value1, 'Hello') value1 from p_test;
postgres=# select coalesce(value1, '10000') value1 from p_test;
value1
--------
10000
1
2
postgres=# select coalesce(value2, null, 'Hello world') value2 from p_test;
value2
-------------
11111
Hello world
22222
postgres=# select coalesce(value3, null, '2012-10-10') value3 from p_test;
value3
---------------------
2010-01-01 00:00:00
2016-08-05 10:01:32
2012-10-10 00:00:00
postgres=# select coalesce(value3, null, '2012-10-A') value3 from p_test;
错误: 无效的类型 timestamp 输入语法: "2012-10-A"
第1行select coalesce(value3, null, '2012-10-A') value3 from p_te...
2、字符串连接
2.1、字符串连接符( || )
Oracle的字符串连接符(||) 和 PostgreSQL的字符串连接符(||)的用法基本相同，不同的地方是
1、当连接的参数有null的时候，Oracle中，null 的连接效果类似于空字符串('')，而PostgreSQL中， 连接的参数中有null的， 连接结果统一都是null。
2、当几个参数都是数字的时候，Oracle会自动把数字转换为字符串。这个和Oracle内部的自动类型转换有关系。而PostgreSQL中，几个参数中至少有一个应该为字符串，否则会报错。
数据迁移的时候，对于Oracle的 A || B 可以使用PostgreSQL的coalesce( A, '') || coalesce( B, '')形式来转换。
Oracle 字符串连接符( || )
SQL> select 'abc' || 'def' value from dual;
VALUE
------
abcdef
SQL> select 123 || 456 value from dual;
VALUE
------
123456
SQL> select null || 456 value from dual;
VAL
---
456
SQL> select null || 'abcdef' value from dual;
VALUE
------
abcdef
SQL> select length(null || null) value from dual;
VALUE
----------
PostgreSQL 字符串连接符( || )
postgres=# select 'abc' || 'def' as value;
value
--------
abcdef
postgres=# select 123 || 456 as value;
错误: 操作符不存在: integer || integer
第1行select 123 || 456 as value;
提示: 没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
postgres=# select 123||'456' as value;
value
--------
123456
postgres=# select null || 456 as value ;
value
-------
postgres=# select null || 'abcdef' as value;
value
-------
postgres=# select length(null || null) as value ;
value
-------
2.2、字符串连接函数concat
Oracle的concat函数类似于字符串连接符（||），但只能够连接两个参数。参数需要是字符串类型，或者可以自动转换成字符串类型。PostgreSQL中也内置了这个方法。
需要注意的是，Oracle的concat，如果两个参数都是null, 则结果是null。而PostgreSQL中，如果两个参数都是null，则结果是空字符串('')。因为PostgreSQL的concat方法内部对于参数做了coalesce(null, '')处理。
Oracle concat
SQL> select concat('abc','def') from dual;
CONCAT
------
abcdef
SQL> select concat(123, 456) from dual;
CONCAT
------
123456
SQL> select concat(null, 456) value from dual;
VAL
---
456
SQL> select concat(null, 'abc') value from dual;
VAL
---
abc
SQL> select concat(null, null) value from dual;
V
-
SQL> select length(concat(null, null)) value from dual;
VALUE
----------
SQL> select * from o_test;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
111111 05-8月 -16
1 31-7月 -16
2 222222
SQL> select concat(value3, value2) from o_test;
CONCAT(VALUE3,VALUE2)
------------------------
05-8月 -16111111
31-7月 -16
222222
PostgreSQL 字符串连接函数
postgres=# select concat('abc','def');
concat
--------
abcdef
postgres=# select concat(123, 456);
concat
--------
123456
postgres=# select concat(null, 456) as value;
value
-------
456
postgres=# select concat(null, 'abc') as value;
value
-------
abc
postgres=# select concat(null, null) as value;
value
-------
postgres=# select length(concat(null, null)) as value;
value
-------
0
postgres=# select * from p_test;
value1 | value2 | value3
--------+--------+---------------------
| 11111 | 2010-01-01 00:00:00
1 | | 2016-08-05 10:01:32
2 | 22222 |
postgres=# select concat(value3, value2) as value from p_test;
value
--------------------------
2010-01-01 00:00:0011111
2016-08-05 10:01:32
22222
--------------------------------------------------------------
二
1、to_number函数
to_number用于将字符类型转换成数字。主要使用在显示格式的控制以及排序等地方。特别是排序的时候，因为按照字符排序和按照数字排序，结果是不同的。
Oracle的to_number函数接受两个参数。第一个参数是需要转换的字符串，第二个参数是要转换的格式。实际使用的时候，除非采用特殊的格式显示，否则一个参数就已经足够了。
PostgreSQL的to_number函数也接受两个参数。使用的时候，不能只使用一个参数。排序的时候，格式字符串中需要设置转换的位数为该字段的最大位数。否则，只使用格式提供的转换位数来排序。比如格式提供了两位，那么就转换最前面的两位字符为数字，然后使用它来排序。
Oracle to_number函数
SQL> desc o_test;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(10)
AGE VARCHAR2(10)
SQL> select * from o_test;
ID NAME AGE
---------- ---------- ----------
赵大 20
钱二 9
孙三 30
李四 110
SQL> select * from o_test order by age;
ID NAME AGE
---------- ---------- ----------
李四 110
赵大 20
孙三 30
钱二 9
SQL> select * from o_test order by to_number(age);
ID NAME AGE
---------- ---------- ----------
钱二 9
赵大 20
孙三 30
李四 110
SQL> select id, name, to_number(age) age from o_test order by to_number(age);
ID NAME AGE
---------- ---------- ----------
钱二 9
赵大 20
孙三 30
李四 110
PostgreSQL to_number函数
postgres=# \d p_test;
数据表 "public.p_test"
栏位 | 类型 | 修饰词
------+-----------------------+----------------------------------------------
id | integer | 非空 默认 nextval('p_test_id_seq'::regclass)
name | character varying(10) |
age | character varying(10) |
postgres=# select * from p_test;
id | name | age
----+------+-----
1 | 赵大 | 20
2 | 钱二 | 9
3 | 孙三 | 30
4 | 李四 | 110
postgres=# select * from p_test order by age;
id | name | age
----+------+-----
4 | 李四 | 110
1 | 赵大 | 20
3 | 孙三 | 30
2 | 钱二 | 9
postgres=# select * from p_test order by to_number(age);
错误: 函数 to_number(character varying) 不存在
第1行select * from p_test order by to_number(age);
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select * from p_test order by to_number(age,'9999999999');
id | name | age
----+------+-----
2 | 钱二 | 9
1 | 赵大 | 20
3 | 孙三 | 30
4 | 李四 | 110
postgres=# select * from p_test order by to_number(age,'99');
id | name | age
----+------+-----
2 | 钱二 | 9
4 | 李四 | 110
1 | 赵大 | 20
3 | 孙三 | 30
postgres=# select id,name, to_number(age,'9999999999') from p_test order by to_number(age,'9999999999');
id | name | to_number
----+------+-----------
2 | 钱二 | 9
1 | 赵大 | 20
3 | 孙三 | 30
4 | 李四 | 110
2、decode函数
decode是Oracle固有的一个函数，用于条件判断。其格式为
decode(条件, 值1, 返回值1, 值2, 返回值2,... 值n, 返回值n, 缺省值)。当条件等于值1的时候返回返回值1，······等于值n的时候返回返回值n。都不等于的时候返回缺省值。
PostgreSQL中，decode函数使用来解码的，和encode函数相对。对于Oracle的decode函数，可以把它转换成case......when....的SQL语句，得到一样的效果。
Oracle也支持case....when。用法和PostgreSQL中类似。
Oracle
SQL> select * from o_test;
ID NAME AGE
---------- ---------- ----------
赵大 20
钱二 9
孙三 30
李四 110
SQL> select decode(age, '20', '赵大', '9', '钱二', '张三') testname from o_test;
TEST
----
赵大
钱二
张三
张三
SQL> select case age when '20' then '赵大' when '9' then '钱二' else '张三' end testname from o_test;
TEST
----
赵大
钱二
张三
张三
SQL> select case when age = '20' then '赵大' when age= '9' then '钱二' else '张三' end testname from o_test;
TEST
----
赵大
钱二
张三
张三
PostgreSQL
postgres=# select * from p_test;
id | name | age
----+------+-----
1 | 赵大 | 20
2 | 钱二 | 9
3 | 孙三 | 30
4 | 李四 | 110
postgres=# select case age when '20' then '赵大' when '9' then '钱二' else '张三' end testname from p_test;
testname
----------
赵大
钱二
张三
张三
postgres=# select case when age='20' then '赵大' when age= '9' then '钱二' else '张三' end testname from p_test;
testname
----------
赵大
钱二
张三
张三
postgres=# select encode('abcdefghijklmn', 'base64');
encode
----------------------
YWJjZGVmZ2hpamtsbW4=
postgres=# select decode('YWJjZGVmZ2hpamtsbW4=', 'base64');
decode
--------------------------------
\x6162636465666768696a6b6c6d6e
3、instr函数
Oracle的instr函数是查找一个字符串中，另一个字符串所在的位置。如果找不到则返回0。instr一共有四个参数。前两个分别表示源字符串和查找字符串，第三个表示开始位置(<0的时候表示从右望左找)。第四个表示第几次出现的值。PostgreSQL中，可以使用position(substring in string) 函数来对应它。position函数没有Oracle的那么复杂，有些复杂的功能只能使用自定义函数来实现它。
Oracle instr
SQL> select instr('helloworld', 'l') from dual;
INSTR('HELLOWORLD','L')
-----------------------
3
SQL> select instr('helloworld', 'l', 5) from dual;
INSTR('HELLOWORLD','L',5)
-------------------------
9
SQL> select instr('helloworld', 'l', -5) from dual;
INSTR('HELLOWORLD','L',-5)
--------------------------
4
SQL> select instr('helloworld', 'l', 4, 2) from dual;
INSTR('HELLOWORLD','L',4,2)
---------------------------
9
PostgreSQL position
postgres=# select position('l' in 'helloworld');
position
----------
3
postgres=# select length(substring('helloworld', 1, 4)) + position('l' in substring('helloworld',5));
?column?
----------
9
postgres=# select instr('helloworld', 'l', -5, 1) ;
错误: 函数 instr(unknown, unknown, integer, integer) 不存在
第1行select instr('helloworld', 'l', -5, 1) ;
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# CREATE FUNCTION instr(string varchar, string_to_search varchar,
postgres(# beg_index integer, occur_index integer)
postgres-# RETURNS integer AS
$$
postgres$# DECLARE
postgres$# pos integer NOT NULL DEFAULT 0;
postgres$# occur_number integer NOT NULL DEFAULT 0;
postgres$# temp_str varchar;
postgres$# beg integer;
postgres$# i integer;
postgres$# length integer;
postgres$# ss_length integer;
postgres$# BEGIN
postgres$# IF beg_index > 0 THEN
postgres$# beg := beg_index;
postgres$# temp_str := substring(string FROM beg_index);
postgres$# FOR i IN 1..occur_index LOOP
postgres$# pos := position(string_to_search IN temp_str);
postgres$# IF i = 1 THEN
postgres$# beg := beg + pos - 1;
postgres$# ELSE
postgres$# beg := beg + pos;
postgres$# END IF;
postgres$# temp_str := substring(string FROM beg + 1);
postgres$# END LOOP;
postgres$# IF pos = 0 THEN
postgres$# RETURN 0;
postgres$# ELSE
postgres$# RETURN beg;
postgres$# END IF;
postgres$# ELSE
postgres$# ss_length := char_length(string_to_search);
postgres$# length := char_length(string);
postgres$# beg := length + beg_index - ss_length + 2;
postgres$# WHILE beg > 0 LOOP
postgres$# temp_str := substring(string FROM beg FOR ss_length);
postgres$# pos := position(string_to_search IN temp_str);
postgres$# IF pos > 0 THEN
postgres$# occur_number := occur_number + 1;
postgres$# IF occur_number = occur_index THEN
postgres$# RETURN beg;
postgres$# END IF;
postgres$# END IF;
postgres$# beg := beg - 1;
postgres$# END LOOP;
postgres$# RETURN 0;
postgres$# END IF;
postgres$#
postgres$# END;
postgres$#
$$
LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# select instr('helloworld', 'l', -5, 1) ;
instr
-------
4
postgres=# select instr('helloworld', 'l', 4, 2);
instr
-------
9
--------------------------------------------------------------
三
1、substr
substr函数是截取一个字符串的一部分，格式是substr(字符串，开始位置，长度)。Oracle和PostgreSQL都支持这个函数。但是具体使用的时候，有些不一样的地方。Oracle中，substr是一系列函数。还包括substrb, substrc,substr2,substr4等多个。分别是按照字符(substr)截取、按字节(substrb)截取、按Unicode码(substrc)截取、按照UCS2编码(substr2)截取和按照UCS4码(substr4)截取。这几个函数的用法是一致的。
PostgreSQL中，substr方法只支持字符方式。不支持其余的方式。参数和Oracle是一样的。而且还有一个叫substring方法，和substr方法是一样的。
Oracle和PostgreSQL中的用法，区别就是当开始位置=0的时候，Oracle表示的含义和1是一样的，从第一个字符开始截取。而PostgreSQL相当于第一个字符再往前一个空字符。当第二个参数<0的时候，Oracle代表开始位置从右侧开始算。而PostgreSQL则表示则是从左侧望再前追加空字符。另外PostgreSQL参数还支持使用关键字from 和 for的写法，分别表示开始位置和长度。
Oracle substr
SQL> select substr('bbb姑苏城外寒山寺aaa',4) from dual;
SUBSTR('BBB姑苏城
-----------------
姑苏城外寒山寺aaa
SQL> select substr('bbb姑苏城外寒山寺aaa',5, 5) from dual;
SUBSTR('BB
----------
苏城外寒山
SQL> select substrb('bbb姑苏城外寒山寺aaa',5, 5) from dual;
SUBST
-----
苏城
SQL> select substrc('bbb姑苏城外寒山寺aaa',5, 5) from dual;
SUBSTRC('B
----------
苏城外寒山
SQL> select substr2('bbb姑苏城外寒山寺aaa',5, 5) from dual;
SUBSTR2('B
----------
苏城外寒山
SQL> select substr4('bbb姑苏城外寒山寺aaa',5, 5) from dual;
SUBSTR4('B
----------
苏城外寒山
SQL> select substr('bbb姑苏城外寒山寺aaa',-8, 5) from dual;
SUBSTR('BB
----------
城外寒山寺
SQL> select substr('bbb姑苏城外寒山寺aaa',0, 5) from dual;
SUBSTR(
-------
bbb姑苏
PostgreSQL substr
postgres=# select substr('bbb姑苏城外寒山寺aaa',4);
substr
-------------------
姑苏城外寒山寺aaa
postgres=# select substr('bbb姑苏城外寒山寺aaa',5, 5);
substr
------------
苏城外寒山
postgres=# select substr('bbb姑苏城外寒山寺aaa',0, 10);
substr
-----------------
bbb姑苏城外寒山
postgres=# select substr('bbb姑苏城外寒山寺aaa',-5, 10);
substr
--------
bbb姑
postgres=# select substring('bbb姑苏城外寒山寺aaa' from 4);
substring
-------------------
姑苏城外寒山寺aaa
postgres=# select substring('bbb姑苏城外寒山寺aaa' from 5 for 5);
substring
------------
苏城外寒山
postgres=# select substring('bbb姑苏城外寒山寺aaa' from 0 for 10);
substring
-----------------
bbb姑苏城外寒山
postgres=# select substring('bbb姑苏城外寒山寺aaa' from -5 for 10);
substring
-----------
bbb姑
2、length
length(字符串)函数是求得字符串的长度。Oracle和PostgreSQL都支持这个函数。
Oracle中，length是一系列函数。还包括lengthb, lengthc, length2, length4等多个。分别是按照字符(length)取长度、按字节(lengthb)取长度、按Unicode码(lengthc)取长度、按照UCS2编码(length2)取长度和按照UCS4码(length4)取长度。这几个函数的用法是一致的。
PostgreSQL中，length方法只支持字符方式， 不支持其余的方式。参数和Oracle是一样的。迁移的时候，按照字符以外的方式取长度，PostgreSQL还不支持。
Oracle length
SQL> select length('bbb姑苏城外寒山寺aaa') from dual;
LENGTH('BBB姑苏城外寒山寺AAA')
------------------------------
13
SQL> select lengthb('bbb姑苏城外寒山寺aaa') from dual;
LENGTHB('BBB姑苏城外寒山寺AAA')
-------------------------------
20
PostgreSQL length
postgres=# select length('bbb姑苏城外寒山寺aaa');
length
--------
13
postgres=# select lengthb('bbb姑苏城外寒山寺aaa');
错误: 函数 lengthb(unknown) 不存在
第1行select lengthb('bbb姑苏城外寒山寺aaa');
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
3、trim/ltrim/rtrim函数
trim函数用来除去字符串开头和结尾的指定字符（默认是空格）。ltrim可以除去左侧开头的指定字符，rtrim除去右侧开头的指定字符。
Oracle的trim只能除去一个特定字符。两个以上不支持。改变字符的时候，使用关键字from。比如trim('a' from 'aabbccaa')这种写法。不支持trim('aabbcc', 'a')的这种写法。但是ltrim和rtrim支持多个字符。并且支持ltrim('aabbcc', 'a')和rtrim('aabbcc', 'c')这种写法，而不支持from关键字的写法。另外，trim的from关键字的写法中还支持Both/ leading/ trailing三个关键字，分别代表除去 开头和结尾 / 开头 / 结尾的指定字符。
PostgreSQL中，都支持除去开始结尾的多个字符。并且PostgreSQL中， trim的两种写法都是支持的，而ltrim和rtrim和oracle一样, 支持ltrim('aabbcc', 'a')和rtrim('aabbcc', 'c')这种写法，而不支持from关键字的写法。PostgreSQL的trim的from关键字的写法也支持Both/ leading/ trailing三个关键字，分别代表除去 开头和结尾 / 开头 / 结尾的指定字符。
迁移的时候，可以直接迁移。
Oracle trim
SQL> select trim(' aa ' ) from dual;
TR
--
aa
SQL> select ltrim(' aa ') from dual;
LTR
---
aa
SQL> select rtrim(' aa ') from dual;
RTR
---
aa
SQL> select trim('aabbccaa', 'a') from dual;
select trim('aabbccaa', 'a') from dual
第 1 行出现错误:
ORA-00907: 缺失右括号
SQL> select trim('a' from 'aabbccaa') from dual;
TRIM
----
bbcc
SQL> select trim('ab' from 'aabbccaa') from dual;
select trim('ab' from 'aabbccaa') from dual
第 1 行出现错误:
ORA-30001: 截取集仅能有一个字符
SQL> select ltrim('ab' from 'aabbccaa') from dual;
select ltrim('ab' from 'aabbccaa') from dual
第 1 行出现错误:
ORA-00907: 缺失右括号
SQL> select ltrim('aabbccaa','ab') from dual;
LTRI
----
ccaa
SQL> select rtrim('aabbccaa','ab') from dual;
RTRIM(
------
aabbcc
SQL> select trim(both 'a' from 'abcdefa') from dual;
TRIM(
-----
bcdef
SQL> select trim(leading 'a' from 'abcdefa') from dual;
TRIM(L
------
bcdefa
SQL> select trim(trailing 'a' from 'abcdefa') from dual;
TRIM(T
------
abcdef
PostgreSQL trim
postgres=# select trim(' aa ') aa ;
aa
----
aa
postgres=# select ltrim(' aa ') aa ;
aa
-----
aa
postgres=# select rtrim(' aa ') aa ;
aa
-----
aa
postgres=# select trim('aabbccaa', 'a') aa;
aa
------
bbcc
postgres=# select trim('ab' from 'aabbccaa') aa;
aa
----
cc
postgres=# select ltrim('ab' from 'aabbccaa') aa;
错误: 语法错误 在 "from" 或附近的
第1行select ltrim('ab' from 'aabbccaa') aa;
postgres=# select ltrim('aabbccaa','ab') ;
ltrim
-------
ccaa
postgres=# select rtrim('aabbccaa','ab') ;
rtrim
--------
aabbcc
postgres=# select trim(both 'a' from 'abcdefa') ;
btrim
-------
bcdef
postgres=# select trim(leading 'a' from 'abcdefa') ;
ltrim
--------
bcdefa
postgres=# select trim(trailing 'a' from 'abcdefa') ;
rtrim
--------
abcdef
--------------------------------------------------------------
四
1、NLSSORT
Oracle的NLSSORT可以改变取得字段的排序方式。默认的情况下，西文的排序是按照二进制编码的顺序排序。对于汉字按照拼音来排序。可以使用NLSSORT来改变字段的默认排序方式，从而取得不同的顺序。
以中文为例，排序可以有：
按照部首（第一顺序）、笔划（第二顺序）排序（SCHINESE_RADICAL_M）
按照笔划（第一顺序）、部首（第二顺序）排序（SCHINESE_STROKE_M）
按照拼音排序（SCHINESE_PINYIN_M）
等三种。默认是按照拼音来排序。
不使用参数的时候，自动采用Session内的默认值。
PostgreSQL中，对于西文按照二进制，对于汉字只是按照拼音来排序。同一种编码，其余的排序方式尚不支持。
Oracle NLSSORT
SQL> select * from test;
NAME
---------------
横看成岭侧成峰
远近高低各不同
不识庐山真面目
只缘身在此山中
SQL> select * from test order by name;
NAME
---------------
不识庐山真面目
横看成岭侧成峰
远近高低各不同
只缘身在此山中
SQL> select * from test order by nlssort(name);
NAME
---------------
不识庐山真面目
横看成岭侧成峰
远近高低各不同
只缘身在此山中
SQL> select * from test order by nlssort(name, 'NLS_SORT=SCHINESE_STROKE_M');
NAME
---------------
不识庐山真面目
只缘身在此山中
远近高低各不同
横看成岭侧成峰
SQL> select * from test order by nlssort(name, 'NLS_SORT=SCHINESE_PINYIN_M');
NAME
---------------
不识庐山真面目
横看成岭侧成峰
远近高低各不同
只缘身在此山中
SQL> select * from test order by nlssort(name, 'NLS_SORT=SCHINESE_RADICAL_M');
NAME
---------------
不识庐山真面目
只缘身在此山中
横看成岭侧成峰
远近高低各不同
PostgreSQL NLSSORT
postgres=# select * from test;
name
----------------
横看成岭侧成峰
远近高低各不同
不识庐山真面目
只缘身在此山中
postgres=# select * from test order by name;
name
----------------
不识庐山真面目
横看成岭侧成峰
远近高低各不同
只缘身在此山中
2、NLS_INITCAP/ NLS_LOWER/ NLS_UPPER
Oracle的NLS_INITCAP(x[,'NLS_SORT=y']) 、NLS_LOWER(x[,'NLS_SORT=y'])、NLS_UPPER(x[,'NLS_SORT=y']) 三个函数都有两个参数，可以根据y参数的不同，提供不同于系统默认值的结果。如果不提供这个参数，那么结果和initcap、lower和upper三个函数是一样的。
另外，Oracle的官方文档（http://docs.oracle.com/database/121/SQLRF/functions122.htm#SQLRF00676 ）上，NLS_INITCAP的例子：
SELECT NLS_INITCAP('ijsland') "InitCap" FROM DUAL;
InitCap
-------
Ijsland
SELECT NLS_INITCAP('ijsland', 'NLS_SORT = XDutch') "InitCap" FROM DUAL;
InitCap
-------
IJsland
我这边做不出相同的效果。网上也没搜到。怀疑是老版的Oracle有这种做法，新版已经取消。PostgreSQL中，对于同一种编码，没有提供多种方式排序。只有简单的Initcap, lower和upper函数。另外，在实际使用中，这几个带NLS_的方法都几乎用不到。
Oracle NLS_INITCAP/ NLS_LOWER/ NLS_UPPER
SQL> select nls_initcap('ijslang') "test" from dual;
test
-------
Ijslang
SQL> select nls_initcap('ijslang', 'NLS_SORT=XDutch') "test" from dual;
test
-------
Ijslang
SQL> select initcap('ijslang') "test" from dual;
test
-------
Ijslang
SQL> SELECT NLS_LOWER('NOKTASINDA', 'NLS_SORT = XTurkish') "Lowercase" from dual;
Lowercase
----------
noktasinda
SQL> SELECT NLS_LOWER('NOKTASINDA') "Lowercase" from dual;
Lowercase
----------
noktasinda
SQL> SELECT LOWER('NOKTASINDA') "Lowercase" from dual;
Lowercase
----------
noktasinda
SQL> SELECT NLS_UPPER('große') "Uppercase" from dual;
Upper
-----
GRO?E
SQL> SELECT NLS_UPPER('große','NLS_SORT=XGerman') "Uppercase" from dual;
Upper
-----
GRO?E
SQL> SELECT UPPER('große') "Uppercase" from dual;
Upper
-----
GRO?E
PostgreSQL INITCAP/ LOWER/ UPPER
postgres=# select initcap('ijslang');
initcap
---------
Ijslang
postgres=# select LOWER('NOKTASINDA');
lower
------------
noktasinda
postgres=# select upper('große');
upper
-------
GRO?E
PostgreSQL 9.4.4 中文手册：字符串函数
http://www.postgres.cn/docs/9.4/functions-string.html
Database SQL Language Reference：
http://docs.oracle.com/database/121/SQLRF/functions122.htm#SQLRF00676
http://docs.oracle.com/database/121/SQLRF/functions123.htm#SQLRF00677
http://docs.oracle.com/database/121/SQLRF/functions125.htm#SQLRF00678
http://docs.oracle.com/database/121/SQLRF/functions124.htm#SQLRF00679
--------------------------------------------------------------
五
1、regexp_replace
regexp_replace是使用正则表达式进行替换的函数。源字符串里面符合正则表达式的，替换成目标字符串。Oracle和PostgreSQL都支持regexp_replace，但是参数有些不同。
Oracle的regexp_replace共有六个参数。分别表示源字符串，正则表达式，目标字符串，开始位置(默认为1)，替换第几个匹配（默认全部）和一个flg值（'i'：大小写不敏感 ‘c’：大小写敏感 'n'：点号，不匹配换行符号 'm‘：多行模式 'x'：扩展模式，忽略正则表达式中的空白字符）。
PostgreSQL中的regexp_replace只有四个参数，分别表示源字符串，正则表达式，目标字符串，替换规则(默认只替换第一个。当替换规则为'g'的时候代表全部替换)。功能上没有Oracle的regexp_replace的功能强。有些比较复杂的替换，只能自己通过函数实现。
Oracle regexp_replace
SQL> select regexp_replace('abc123xyz888', '[0-9]+') from dual;
REGEXP
------
abcxyz
SQL> select regexp_replace('abc123xyz888', '[0-9]+', 'ZZZ') from dual;
REGEXP_REPLA
------------
abcZZZxyzZZZ
SQL> select regexp_replace('abc123xyz888', '[0-9]+', 'ZZZ', 8) from dual;
REGEXP_REPLA
------------
abc123xyzZZZ
SQL> select regexp_replace('abc123xyz888', '[0-9]+', 'ZZZ', 1, 2) from dual;
REGEXP_REPLA
------------
abc123xyzZZZ
SQL> select regexp_replace('abc123xyz888', '[a-c]+', 'ZZZ', 1, 1, 'i') from dual;
REGEXP_REPLA
------------
ZZZ123xyz888
SQL> select regexp_replace('abc123xyz888', '[A-C]+', 'ZZZ', 1, 1, 'i') from dual;
REGEXP_REPLA
------------
ZZZ123xyz888
SQL> select regexp_replace('abc123xyz888', '[A-C]+', 'ZZZ', 1, 1, 'c') from dual;
REGEXP_REPLA
------------
abc123xyz888
PostgreSQL regexp_replace
postgres=# select regexp_replace('abc123xyz888', '[0-9]+') ;
错误: 函数 regexp_replace(unknown, unknown) 不存在
第1行select regexp_replace('abc123xyz888', '[0-9]+') ;
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select regexp_replace('abc123xyz888', '[0-9]+', 'ZZZ');
regexp_replace
----------------
abcZZZxyz888
postgres=# select regexp_replace('abc123xyz888', '[0-9]+', 'ZZZ', 'g');
regexp_replace
----------------
abcZZZxyzZZZ
2、regexp_substr
regexp_substr是Oracle的使用正则表达式进行字符串截取的函数。PostgreSQL中的substring函数中可以实现它的简单功能，但是没有Oracle的功能强。如果需要实现它的复杂功能，需要自己使用函数实现。
Oracle的regexp_substr函数有五个参数。分别表示源字符串，正则表达式，开始位置(默认为1)，截取第几个匹配（默认第一个）和一个flg值（'i'：大小写不敏感 ‘c’：大小写敏感 'n'：点号，不匹配换行符号 'm‘：多行模式 'x'：扩展模式，忽略正则表达式中的空白字符）。
PostgreSQL中的regexp_substr只有三个参数，分别表示源字符串，正则表达式，逃匿符。
Oracle regexp_substr
SQL> select regexp_substr('abc123abc888', '[a-c]+') from dual;
REG
---
abc
SQL> select regexp_substr('abc123abc888', '[a-c]+', 2) from dual;
RE
--
bc
SQL> select regexp_substr('abc123abc888', '[a-c]+', 5) from dual;
REG
---
abc
SQL> select regexp_substr('abc123abc888', '[a-c]+', 1, 2) from dual;
REG
---
abc
SQL> select regexp_substr('abc123abc888', '[A-C]+', 1, 1) from dual;
R
-
SQL> select regexp_substr('abc123abc888', '[A-C]+', 1, 1, 'i') from dual;
REG
---
abc
SQL> select regexp_substr('abc123abc888', '[A-C]+', 1, 1, 'c') from dual;
R
-
PostgreSQL substring
postgres=# select substring('abc123abc888', '[a-c]+') ;
substring
-----------
abc
postgres=# select substring('abc123abc888' from '[a-c]+') ;
substring
-----------
abc
postgres=# select substring('Thomas' , '%#"o_a#"%' ,'#');
substring
-----------
oma
postgres=# select substring('Thomas' from '%#"o_a#"%' for '#');
substring
-----------
oma
3、regexp_instr
regexp_instr是Oracle的一个搜索字符串中符合表达式的字串位置的一个函数。在PostgreSQL中没有对应的函数，需要自己写函数实现它。最简单的功能，可以使用position函数和substring函数来模拟实现。
Oracle regexp_instr
SQL> select regexp_instr('abc123abc156', '[1-2]+') from dual;
REGEXP_INSTR('ABC123ABC156','[1-2]+')
-------------------------------------
4
SQL> select regexp_instr('abc123abc156', '[1-2]+', 7) from dual;
REGEXP_INSTR('ABC123ABC156','[1-2]+',7)
---------------------------------------
10
SQL> select regexp_instr('abc123abc156', '[1-2]+', 1, 2) from dual;
REGEXP_INSTR('ABC123ABC156','[1-2]+',1,2)
-----------------------------------------
10
SQL> select regexp_instr('abc123abc156', '[1-2]+', 1, 1, 0) from dual;
REGEXP_INSTR('ABC123ABC156','[1-2]+',1,1,0)
-------------------------------------------
4
SQL> select regexp_instr('abc123abc156', '[1-2]+', 1, 1, 1) from dual;
REGEXP_INSTR('ABC123ABC156','[1-2]+',1,1,1)
-------------------------------------------
6
SQL> select regexp_instr('abc123abc156', '[A-C]+', 1, 2, 0, 'i') from dual;
REGEXP_INSTR('ABC123ABC156','[A-C]+',1,2,0,'I')
-----------------------------------------------
7
SQL> select regexp_instr('abc123abc156', '[A-C]+', 1, 2, 0, 'c') from dual;
REGEXP_INSTR('ABC123ABC156','[A-C]+',1,2,0,'C')
-----------------------------------------------
0
PostgreSQL position substring
postgres=# select position(substring('abc123abc156' from '[1-2]+') in 'abc123abc156');
position
----------
4
postgres=# select 7 - 1 + position(substring(substring('abc123AC158',7) from '[1-2]+') in 'abc123AC158');
?column?
----------
10
4、regexp_like
regexp_like是Oracle的一个检索正则表达式条件的一个函数，只能用在条件判断部分。在PostgreSQL中没有对应的函数，需要自己写函数实现它。最简单的功能，可以简单的使用substring和length函数来模拟。
Oracle regexp_like
SQL> select * from o_test;
ID NAME AGE
---------- ---------- ----------
1 James1234 20
2 De Haan 20
2 Greenberg 20
2 TomLiu 20
SQL> select * from o_test where regexp_like(name, '([aeiou])\1');
ID NAME AGE
---------- ---------- ----------
2 De Haan 20
2 Greenberg 20
SQL> select * from o_test where regexp_like(name, '([AEIOU])\1');
未选定行
SQL> select * from o_test where regexp_like(name, '([AEIOU])\1', 'i');
ID NAME AGE
---------- ---------- ----------
2 De Haan 20
2 Greenberg 20
SQL> select * from o_test where regexp_like(name, '([AEIOU])\1', 'c');
未选定行
PostgreSQL length substring
postgres=# select * from p_test where length(substring(name, '([aeiou])\1')) > 0;
id | name | age
----+-----------+-----
2 | De Haan | 20
3 | Greenberg | 20
postgres=# select * from p_test where length(substring(name, '([AEIOU])\1')) > 0;
id | name | age
----+------+-----
postgres=# select * from p_test where length(substring(name, '([AEIOUaeiou])\1')) > 0;
id | name | age
----+-----------+-----
2 | De Haan | 20
3 | Greenberg | 20
参考文档：
PostgreSQL 9.4.4 中文手册：
http://www.postgres.cn/docs/9.4/functions-string.html (字符串函数和操作符）
Database SQL Language Reference：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions148.htm#SQLRF06300
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions149.htm#SQLRF06302
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions150.htm#SQLRF06303
http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions007.htm#SQLRF00501
--------------------------------------------------------------
六
1、NVL2
NVL2(expr1, expr2, expr3) 是Oracle的一个NULL判断函数。当expr1 不为 NULL时，返回expr2；为NULL时，返回 expr3。
PostgreSQL中没有对应的函数。 可以使用case when实现相同的意思。
Oracle NVL2
SQL> select * from o_test;
ID NAME AGE
---------- ---------- ----------
1
2
2 zhangsan 18
SQL> select nvl2(name, '有人', '无人') FLG from o_test;
FLG
----
无人
无人
有人
SQL> select case when name is not null then '有人' else '无人' end FLG from o_test;
FLG
----
无人
无人
有人
PostgreSQL CASE
postgres=# select * from p_test;
id | name | age
----+----------+-----
1 | |
2 | |
2 | zhangsan | 18
postgres=# select case when name is not null then '有人' else '无人' end FLG from p_test;
flg
------
无人
无人
有人
2、LNNVL
LNNVL(condition) 是Oracle中进行条件判断的一个函数。当条件返回true时，返回false。当条件为unknow或者返回false的时候返回true。类似于 CASE XXX WHEN is null or is not true THEN true ELSE false END。PostgreSQL中，没有对应的函数，只能使用CASE WHEN函数进行模拟。
Oracle LNNVL
SQL> select * from o_test;
ID NAME AGE
---------- ---------- ----------
1
2
2 zhangsan 18
SQL> select case when lnnvl(name = 'zhangsan') then '无人' else '有人' end flg from o_test;
FLG
----
无人
无人
有人
SQL> select case when name = 'zhangsan' then '有人' else '无人' end FLG from o_test;
FLG
----
无人
无人
有人
PostgreSQL CASE WHEN
postgres=# select * from p_test;
id | name | age
----+----------+-----
1 | |
2 | |
2 | zhangsan | 18
postgres=# select case when name ='zhangsan' then '有人' else '无人' end FLG from p_test;
flg
------
无人
无人
有人
3、BITAND
BITAND(expr1, expr2)函数是Oracle里面的一个对于数字按位进行 AND 运算的函数。PostgreSQL中没有对应的函数，可以直接使用 a&b的方式进行模拟。
Oracle BITAND
SQL> SELECT BITAND(6,3) FROM dual;
BITAND(6,3)
-----------
2
SQL> SELECT BITAND(6, 5) FROM dual;
BITAND(6,5)
-----------
4
PostgreSQL 数字按位与运算
postgres=# SELECT BITAND(6,3) ;
错误: 函数 bitand(integer, integer) 不存在
第1行SELECT BITAND(6,3) ;
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select 6&3;
?column?
----------
2
postgres=# select 6&5;
?column?
----------
4
4、REMAINDER
REMAINDER(n2, n1) 是 Oracle的一个计算余数的函数。它返回 n2/n1 的余数。 和MOD的不同的地方在于： REMAINDER相当于n2-n1 ROUND(n2/n1)。而MOD相当于n2-n1 FLOOR(n2/n1)。PostgreSQL中没有这个 函数。可以使用它的元计算式n2-n1* ROUND(n2/n1)的 形式模拟它。
Oracle REMAINDER
SQL> Select remainder(3.5,2) from dual;
REMAINDER(3.5,2)
----------------
-.5
SQL> select 3.5 - 2 * ROUND(3.5/2) from dual;
3.5-2*ROUND(3.5/2)
------------------
-.5
SQL> select mod(3.5, 2) from dual;
MOD(3.5,2)
----------
1.5
PostgreSQL 数字按位与运算
postgres=# select remainder(3.5, 2);
错误: 函数 remainder(numeric, integer) 不存在
第1行select remainder(3.5, 2);
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select 3.5 - 2 * ROUND(3.5/2) ;
?column?
----------
-0.5
postgres=# select mod(3.5, 2) ;
mod
-----
1.5
5、SYSDATE、CURRENT_TIMESTAMP
Oracle的SYSDATE是去当前的日期的一个函数。返回值只有年月日。取得当前时间戳的函数是CURRENT_TIMESTAMP，返回当前的时间戳。PostgreSQL中，可以使用CURRENT_DATE来对应SYSDATE。至于CURRENT_TIMESTAMP，PostgreSQL也支持。也可以使用now()来对应它。
Oracle SYSDATE、CURRENT_TIMESTAMP
SQL> select sysdate from dual;
SYSDATE
--------------
22-8月 -16
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
22-8月 -16 10.28.25.223000 上午 +08:00
PostgreSQL CURRENT_DATE、CURRENT_TIMESTAMP、NOW()
postgres=# select current_date;
date
------------
2016-08-22
postgres=# select current_timestamp;
now
-------------------------------
2016-08-22 10:28:56.689154+08
postgres=# select now();
now
-------------------------------
2016-08-22 10:29:00.466176+08
--------------------------------------------------------------
七
1、ADD_MONTHS函数
ADD_MONTHS(date,integer) 是Oracle的一个时间运算函数。返回参数date日期的integer个月后的日期。是Oracle日期运算的一个很基本的函数。PostgreSQL里面没有这个函数。可以使用Interval数据类型的加减来模拟。返回的是一个timestamp的值。也可以自己创建函数来实现它。
Oracle ADD_MONTHS
SQL> select sysdate from dual;
SYSDATE
--------------
24-8月 -16
SQL> select add_months(sysdate, 2) from dual;
ADD_MONTHS(SYS
--------------
24-10月-16
SQL> select add_months(sysdate, -2) from dual;
ADD_MONTHS(SYS
--------------
24-6月 -16
SQL> select add_months(to_date('2016/08/01', 'YYYY/MM/DD'), -1) from dual;
ADD_MONTHS(TO_
--------------
01-7月 -16
PostgreSQL ADD_MONTHS
postgres=# select now();
now
------------------------------
2016-08-24 09:30:10.87367+08
postgres=# select add_months(now(),2);
错误: 函数 add_months(timestamp with time zone, integer) 不存在
第1行select add_months(now(),2);
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select now() + '2 month';
?column?
-------------------------------
2016-10-24 09:30:35.284043+08
postgres=# select now() + '-2 month';
?column?
------------------------------
2016-06-24 09:30:53.23845+08
postgres=# select to_timestamp('2016-08-01','YYYY-MM-DD') + '-1 month';
?column?
------------------------
2016-07-01 00:00:00+08
postgres=# CREATE FUNCTION add_months(date, int)
postgres-# RETURNS date AS
postgres-# 'SELECT ($1 + ( $2::text || ''months'')::interval)::date;'
postgres-# LANGUAGE 'sql';
CREATE FUNCTION
postgres=# select add_months(current_date, 2);
add_months
------------
2016-10-24
postgres=# select add_months(current_date, -2);
add_months
------------
2016-06-24
postgres=# select add_months('2016/08/01', -1);
add_months
------------
2016-07-01
2、LAST_DAY 函数
LAST_DAY(date)函数是Oracle的一个取日期的月末日的函数。PostgreSQL中没有对应的日期函数。可以自己使用Interval来实现。也可以自己创建函数来实现。
Oracle LAST_DAY
SQL> select sysdate from dual;
SYSDATE
--------------
24-8月 -16
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDA
--------------
31-8月 -16
SQL> select last_day(to_date('2016/09/16', 'YYYY/MM/DD')) from dual;
LAST_DAY(TO_DA
--------------
30-9月 -16
PostgreSQL LAST_DAY
postgres=# CREATE FUNCTION LAST_DAY(DATE)
postgres-# RETURNS DATE AS
postgres-# 'SELECT date(substr(text($1::timestamp +
postgres'# ''1 month''),1,7)||''-01'')-1'
postgres-# LANGUAGE 'sql';
CREATE FUNCTION
postgres=# select current_date;
date
------------
2016-08-24
postgres=# select last_day(current_date);
last_day
------------
2016-08-31
postgres=# select last_day('2016/09/16'::date);
last_day
------------
2016-09-30
3、MONTHS _BETWEEN函数
MONTHS _BETWEEN函数是Oracle的一个判断两个日期之间有几个月的间隔的函数。PostgreSQL中也没有对应的函数，只能自己通过函数实现。不过由于对于相差月份的算法不一样，结果可能有些差异。下面给出一个例子。
Oracle MONTHS _BETWEEN
SQL> SELECT MONTHS_BETWEEN(TO_DATE('02-02-1995','MM-DD-YYYY'),
2 TO_DATE('01-01-1995','MM-DD-YYYY')) MONTHS from dual;
MONTHS
----------
1.03225806
SQL> SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('01-01-2014','MM-DD-YYYY')) MONTHS from dual;
MONTHS
----------
31.7571192
PostgreSQL MONTHS _BETWEEN
postgres=# CREATE FUNCTION MONTHS_BETWEEN(DATE,DATE)
postgres-# RETURNS NUMERIC AS
postgres-# 'SELECT to_number(text(date($1)-
postgres'# date($2)),''999999999'')/31'
postgres-# LANGUAGE 'sql';
CREATE FUNCTION
postgres=# SELECT MONTHS_BETWEEN('1995-02-02'::date,'1995-01-01'::date);
months_between
--------------------
1.0322580645161290
postgres=# SELECT MONTHS_BETWEEN(CURRENT_DATE, '2014-01-01'::DATE) MONTHS;
months
---------------------
31.1612903225806452
4、EXTRACT函数
EXTRACT是Oracle用于从一个date、timestamp或者interval类型中截取到特定的部分所用的函数。Oracle只支持截取年、月、日、时、分、秒，时区时，时区分、时区等。PostgreSQL也支持该函数，截取的东西比Oracle的多一些，但是不支持时区时，时区分和时区的截取（文档中说是支持的，但是我的实际测试中不支持）。
Oracle EXTRACT
SQL> select extract(year from date'2011-05-17') year from dual;
YEAR
----------
2011
SQL> select extract(month from date'2011-05-17') from dual;
EXTRACT(MONTHFROMDATE'2011-05-17')
----------------------------------
5
SQL> select extract(day from date'2011-05-17') from dual;
EXTRACT(DAYFROMDATE'2011-05-17')
--------------------------------
17
SQL> select extract(hour from timestamp'2011-05-17 12:56:20.1234') AAA from dual;
AAA
----------
12
SQL> select extract(minute from timestamp'2011-05-17 12:56:20.1234') part from dual;
PART
----------
56
SQL> select extract(second from timestamp'2011-05-17 12:56:20.1234') part from dual;
PART
----------
20.1234
SQL> select extract(timezone_region from systimestamp) part from dual;
PART
----------------------------------------------------------------
UNKNOWN
SQL> select extract(timezone_abbr from systimestamp) part from dual;
PART
----------
UNK
SQL> select extract(timezone_hour from systimestamp) part from dual;
PART
----------
8
SQL> select extract(timezone_minute from systimestamp) part from dual;
PART
----------
0
PostgreSQL EXTRACT
postgres=# select extract(year from date'2011-05-17') part;
part
------
2011
postgres=# select extract(month from date'2011-05-17') part;
part
------
5
postgres=# select extract(day from date'2011-05-17') part;
part
------
17
postgres=# select extract(week from date'2011-05-17') part;
part
------
20
postgres=# select extract(century from date'2011-05-17') part;
part
------
21
postgres=# select extract(hour from '2011-05-17 12:56:20.1234'::timestamp) AAA;
aaa
-----
12
postgres=# select extract(minute from '2011-05-17 12:56:20.1234'::timestamp) AAA;
aaa
-----
56
postgres=# select extract(second from '2011-05-17 12:56:20.1234'::timestamp) AAA;
aaa
---------
20.1234
postgres=# select extract(microsecond from '2011-05-17 12:56:20.1234'::timestamp) AAA;
aaa
----------
20123400
postgres=# select extract(isodow from '2011-05-17 12:56:20.1234'::timestamp) AAA;
aaa
-----
2
postgres=# select extract(quarter from '2011-05-17 12:56:20.1234'::timestamp) AAA;
aaa
-----
2
postgres=# select extract(timezone from '2011-05-17 12:56:20.1234'::timestamp) AAA;
错误: 不支持时间戳单位 "timezone"
postgres=# select extract(timezone_hour from '2011-05-17 12:56:20.1234'::timestamp) AAA;
错误: 不支持时间戳单位 "timezone_hour"