JPA 中针对 PG 表字段创建特殊索引

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16



private final JdbcTemplate jdbcTemplate;

private void initPoiTable() {
    Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap("SELECT 1 as count FROM information_schema.tables WHERE table_name = 'data_poi'");
    if (ObjectUtils.isEmpty(stringObjectMap) || stringObjectMap.get("count") == null || (int) stringObjectMap.get("count") <= 0) {
        return;
    }
    // 创建 tsvector 类型和 Geography 类型的字段
    jdbcTemplate.execute("ALTER TABLE data_poi ADD COLUMN IF NOT EXISTS key_words tsvector;");
    jdbcTemplate.execute("ALTER TABLE data_poi ADD COLUMN IF NOT EXISTS location_geography geography;");
    // 添加GIN索引
    jdbcTemplate.execute("CREATE INDEX IF NOT EXISTS idx_key_words ON data_poi USING GIN(key_words);");
    // 添加Gist索引
    jdbcTemplate.execute("CREATE INDEX IF NOT EXISTS idx_location_geography ON data_poi USING GIST(location_geography);");
}