本文比较了在创建索引上面，使用case when时Oracle和PG的异同.

Oracle
测试脚本:

-- Oracle
drop table t1;
create table t1(id int);
create unique index idx_t1_u on t1(case when id < 10 then id else null end);
insert into t1 values(1);
insert into t1 values(1);
insert into t1 values(10);
insert into t1 values(10);

执行结果:

SQL> -- Oracle
SQL>
SQL> drop table t1;
表已删除。
SQL> create table t1(id int);
表已创建。
SQL>
SQL> create unique index idx_t1_u on t1(case when id < 10 then id else null end);
索引已创建。
SQL> insert into t1 values(1);
已创建 1 行。
SQL> insert into t1 values(1);
insert into t1 values(1)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TEST.IDX_T1_U)
SQL> insert into t1 values(10);
已创建 1 行。
SQL> insert into t1 values(10);
已创建 1 行。
SQL>

执行完成后，t1表中有4行记录。

PostgreSQL
测试脚本(不支持case when创建index)：

drop table t1;
create table t1(id int);
create unique index idx_t1_u on t1(case when id < 10 then id else null end);

执行结果：

vastbase=# drop table t1;
DROP TABLE
vastbase=# create table t1(id int);
CREATE TABLE
vastbase=#
vastbase=# create unique index idx_t1_u on t1(case when id < 10 then id else null end);
ERROR:  syntax error at or near "case"
LINE 1: create unique index idx_t1_u on t1(case when id < 10 then id...
                                           ^
vastbase=#

可通过创建UDF或者条件索引实现
测试脚本

drop table t1;
create table t1(id int);
create or replace function sf_test(id int) 
return int
IMMUTABLE
is
  v_id int;
begin
  select (case when id < 10 then id else null end) into v_id from dual;
  return v_id;
end;
/
create unique index idx_t1_id on t1(sf_test(id));
insert into t1 values(1);
insert into t1 values(1);
insert into t1 values(10);
insert into t1 values(10);
drop table t2;
create table t2(id int);
create unique index idx_t2_u on t2(id) where id < 10;
insert into t2 values(1);
insert into t2 values(1);
insert into t2 values(10);
insert into t2 values(10);

执行效果

vastbase=# drop table t1;
DROP TABLE
vastbase=# create table t1(id int);
CREATE TABLE
vastbase=#
vastbase=# create or replace function sf_test(id int)
vastbase-# return int
vastbase-# IMMUTABLE
vastbase-# is
vastbase$#   v_id int;
vastbase$# begin
vastbase$#   select (case when id < 10 then id else null end) into v_id from dual;
vastbase$#   return v_id;
vastbase$# end;
vastbase$# /
CREATE FUNCTION
vastbase=#
vastbase=# create unique index idx_t1_id on t1(sf_test(id));
CREATE INDEX
vastbase=# insert into t1 values(1);
INSERT 0 1
vastbase=# insert into t1 values(1);
ERROR:  duplicate key value violates unique constraint "idx_t1_id"
DETAIL:  Key (sf_test(id))=(1) already exists.
vastbase=# insert into t1 values(10);
INSERT 0 1
vastbase=# insert into t1 values(10);
INSERT 0 1
vastbase=#
vastbase=# drop table t2;
DROP TABLE
vastbase=# create table t2(id int);
CREATE TABLE
vastbase=#
vastbase=# create unique index idx_t2_u on t2(id) where id < 10;
CREATE INDEX
vastbase=# insert into t2 values(1);
INSERT 0 1
vastbase=# insert into t2 values(1);
ERROR:  duplicate key value violates unique constraint "idx_t2_u"
DETAIL:  Key (id)=(1) already exists.
vastbase=# insert into t2 values(10);
INSERT 0 1
vastbase=# insert into t2 values(10);
INSERT 0 1
vastbase=#