本文比较了在创建索引上面，使用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=#

```

ITPub博客

Oracle vs PostgreSQL Develop（30） - Index&Case when

最新文章