Oracle迁移PostgreSQL：INDEX BY等价实现及索引类型添加方法问询

核心差异说明 #

Oracle中TYPE X IS TABLE OF Y INDEX BY BINARY_INTEGER/VARCHAR2定义的是关联数组（Associative Array），本质是键值对集合，支持整数或字符串作为自定义键（索引），键无需连续。而PostgreSQL的原生数组是有序、连续下标（默认从1开始）的线性集合，下标只能是整数，无法自定义索引类型。

因此你提到的CREATE DOMAIN X AS Y[]仅能创建Y类型的数组域，无法指定索引类型——PostgreSQL数组的索引规则是固定的。

替代实现方案 #

若需模拟Oracle关联数组的功能，可根据实际场景选择以下方案：

1. 使用hstore类型（适合字符串键+单一值类型） #

hstore是PostgreSQL的键值对扩展类型，键和值均为字符串，适配INDEX BY VARCHAR2的场景：

-- 启用hstore扩展
CREATE EXTENSION IF NOT EXISTS hstore;

-- 创建基于hstore的域（按需）
CREATE DOMAIN x AS hstore;

-- 使用示例
DECLARE
  my_hstore x := 'key1=>''value1'', key2=>''value2'''::hstore;
BEGIN
  -- 获取值
  RAISE NOTICE '%', my_hstore->'key1';
  -- 添加新键值
  my_hstore := my_hstore || 'key3=>''value3'''::hstore;
END;

2. 使用jsonb类型（支持复杂值类型） #

如果值类型不单一或需要更灵活的结构，jsonb支持字符串、整数等多种键类型，适配性更强：

-- 创建基于jsonb的域
CREATE DOMAIN x AS jsonb;

-- 使用示例
DECLARE
  my_jsonb x := '{"key1": 123, "key2": "abc"}'::jsonb;
BEGIN
  -- 获取值
  RAISE NOTICE '%', my_jsonb->>'key1';
  -- 添加新键值
  my_jsonb := my_jsonb || '{"key3": true}'::jsonb;
END;

3. 自定义复合类型+表（适合持久化场景） #

若需将这类结构持久化到表中，可定义复合类型结合表存储：

-- 定义键值对复合类型
CREATE TYPE key_value_pair AS (
  key VARCHAR(255), -- 若对应Oracle的BINARY_INTEGER，可改为INT
  value Y -- 替换为你的实际类型Y
);

-- 创建存储关联数组的表
CREATE TABLE my_associative_array (
  id SERIAL PRIMARY KEY,
  array_data key_value_pair[]
);

-- 插入示例数据
INSERT INTO my_associative_array (array_data)
VALUES (ARRAY[('key1', 'val1'), ('key2', 'val2')]::key_value_pair[]);

4. PL/pgSQL内存模拟（仅存储过程内使用） #

纯PL/pgSQL中无直接的关联数组，但可通过两个并行数组模拟（一个存键，一个存值）：

DECLARE
  keys VARCHAR[] := '{}';
  values Y[] := '{}'; -- Y替换为你的目标类型
BEGIN
  -- 添加元素
  keys := array_append(keys, 'key1');
  values := array_append(values, 'val1');
  
  -- 查找元素
  FOR i IN 1..array_length(keys, 1) LOOP
    IF keys[i] = 'key1' THEN
      RAISE NOTICE '%', values[i];
      EXIT;
    END IF;
  END LOOP;
END;

总结 #

PostgreSQL原生数组无法自定义索引类型，若要实现Oracle关联数组的功能，需根据使用场景选择hstore、jsonb、自定义复合类型或PL/pgSQL模拟方案。

内容的提问来源于stack exchange，提问作者NaiveDev