包含null值的唯一索引

drop table t1;

create table t1(

id serial not null primary key,

card_no character varying(10),

name character varying(10),

age int

);

 

create unique index on t1(card_no,COALESCE(name,'000'));

 

insert into t1(card_no,name) values('no001','one');

insert into t1(card_no,name) values('no002','one');

insert into t1(card_no) values('no003');

insert into t1(card_no) values('no003');

insert into t1(card_no,name) values('no004','four');

insert into t1(card_no,name) values('no004','four');

 

postgres=# select * from t1;

id | card_no | name | age

----+---------+------+-----

1 | no001 | one |

2 | no002 | one |

3 | no003 | |

5 | no004 | four |

(4 rows)