Postgresql数据库删除索引失败-大小写

测试环境在删除索引的时候出现如下错误，在加入双引号之后可以支持删除。

db_psis_prod=> drop index IDX_t_passenger_scs_way_id_202008;          
ERROR:  index "idx_t_passenger_scs_way_id_202008" does not exist
db_psis_prod=> 
db_psis_prod=> drop index "IDX_t_passenger_scs_way_id_202008";
DROP INDEX

默认情况下postgresql数据库存储到数据库中是以小写的形式。测试验证如下

保存的格式为大写就需要在创建的时候加双引号，删除的时候也需要加双引号。

create index ind_id on tab1(tid);
create index IND_ID2 on tab1(tid);
create index "IND_ID3" on tab1(tid);
create index "ind_id4" on tab1(tid);
create index "IND_id5" on tab1(tid);

dbtest=> select * from pg_indexes where tablename='tab1';
 schemaname | tablename | indexname | tablespace |                        indexdef                         
------------+-----------+-----------+------------+---------------------------------------------------------
 public     | tab1      | ind_id    |            | CREATE INDEX ind_id ON public.tab1 USING btree (tid)
 public     | tab1      | IND_ID3   |            | CREATE INDEX "IND_ID3" ON public.tab1 USING btree (tid)
 public     | tab1      | ind_id4   |            | CREATE INDEX ind_id4 ON public.tab1 USING btree (tid)
 public     | tab1      | IND_id5   |            | CREATE INDEX "IND_id5" ON public.tab1 USING btree (tid)
 public     | tab1      | ind_id2   |            | CREATE INDEX ind_id2 ON public.tab1 USING btree (tid)
(5 rows)

drop index ind_id;
drop index IND_ID2;
drop index IND_ID3;
drop index "IND_ID3";
drop index "ind_id4";
drop index IND_id5;
drop index "IND_id5";

dbtest=> drop index ind_id;
DROP INDEX
dbtest=> drop index IND_ID2;
DROP INDEX
dbtest=> drop index IND_ID3;
ERROR:  index "ind_id3" does not exist
dbtest=> drop index "IND_ID3";
DROP INDEX
dbtest=> drop index "ind_id4";
DROP INDEX
dbtest=> 
dbtest=> drop index IND_id5;
ERROR:  index "ind_id5" does not exist
dbtest=> drop index "IND_id5";
DROP INDEX
dbtest=>