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

```

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

相關文章