PostGIS空间表administracao与classeativecon列多值存储问询

Hey there! Let's fix the issue where your ge.edf_edificacao_pavimento_a table can only store single values in administracao and classeativecon. I'll walk you through two reliable approaches, each with tradeoffs depending on your use case.

方案1：使用PostgreSQL数组类型（快速实现，适合简单场景） #

This approach lets you store multiple values directly in the existing columns by switching to array types, without adding new tables.

步骤1：删除原有单值约束和外键 #

First, we need to remove the existing constraints that enforce single values:

-- 清理administracao相关的约束
ALTER TABLE ge.edf_edificacao_pavimento_a
DROP CONSTRAINT edf_edificacao_pavimento_a_administracao_fk,
DROP CONSTRAINT edf_edificacao_a_administracao_check;

-- 清理classeativecon相关的约束
ALTER TABLE ge.edf_edificacao_pavimento_a
DROP CONSTRAINT edf_edificacao_pavimento_a_classeativecon_fk,
DROP CONSTRAINT edf_edificacao_a_classeativecon_check;

步骤2：修改列类型为数组 #

Now convert the columns to smallint[] (array of small integers):

-- 更新administracao为数组类型
ALTER TABLE ge.edf_edificacao_pavimento_a
ALTER COLUMN administracao TYPE smallint[];

-- 更新classeativecon为数组类型
ALTER TABLE ge.edf_edificacao_pavimento_a
ALTER COLUMN classeativecon TYPE smallint[];

步骤3：添加数组检查约束 #

We'll add new constraints to ensure all values in the array are within your original allowed range:

-- 限制administracao数组的有效值
ALTER TABLE ge.edf_edificacao_pavimento_a
ADD CONSTRAINT edf_edificacao_a_administracao_array_check
CHECK (
  array_length(administracao, 1) > 0 AND -- 确保至少有一个值
  EVERY(elem = ANY(ARRAY[2,3,4,5,6,95,97]::smallint[])) IN administracao
);

-- 限制classeativecon数组的有效值
ALTER TABLE ge.edf_edificacao_pavimento_a
ADD CONSTRAINT edf_edificacao_a_classeativecon_array_check
CHECK (
  array_length(classeativecon, 1) > 0 AND
  EVERY(elem = ANY(ARRAY[10,11,12,13,14,15,16,17,18,19,2,20,21,22,23,24,25,26,27,28,29,3,30,31,32,33,34,35,36,4,5,6,7,8,9,95,98,99]::smallint[])) IN classeativecon
);

步骤4（可选）：用触发器维护外键关联 #

PostgreSQL doesn't support foreign keys directly for arrays, so we can use a trigger to validate that all array elements exist in your reference tables:

-- 验证administracao数组元素的触发器函数
CREATE OR REPLACE FUNCTION validate_administracao_array()
RETURNS TRIGGER AS $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM unnest(NEW.administracao) AS arr_elem
    JOIN dominios.administracao d ON d.code = arr_elem
    GROUP BY arr_elem
    HAVING COUNT(*) = 1
  ) THEN
    RAISE EXCEPTION 'One or more values in administracao are not valid (must exist in dominios.administracao)';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到主表
CREATE TRIGGER trigger_validate_administracao_array
BEFORE INSERT OR UPDATE ON ge.edf_edificacao_pavimento_a
FOR EACH ROW EXECUTE FUNCTION validate_administracao_array();

-- 同理，创建classeativecon的验证触发器
CREATE OR REPLACE FUNCTION validate_classeativecon_array()
RETURNS TRIGGER AS $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM unnest(NEW.classeativecon) AS arr_elem
    JOIN dominios.classe_ativ_econ d ON d.code = arr_elem
    GROUP BY arr_elem
    HAVING COUNT(*) = 1
  ) THEN
    RAISE EXCEPTION 'One or more values in classeativecon are not valid (must exist in dominios.classe_ativ_econ)';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_validate_classeativecon_array
BEFORE INSERT OR UPDATE ON ge.edf_edificacao_pavimento_a
FOR EACH ROW EXECUTE FUNCTION validate_classeativecon_array();

方案2：使用多对多关联表（规范设计，适合长期扩展性） #

If you want a database design that follows 3rd Normal Form and supports complex queries, this is the way to go. We'll create separate tables to link your edificacao records to multiple administracao and classeativecon values.

步骤1：创建关联表 #

-- 关联edificacao和administracao的多对多表
CREATE TABLE ge.edf_edificacao_administracao (
  id_edificacao integer NOT NULL,
  code_administracao smallint NOT NULL,
  CONSTRAINT edf_edificacao_administracao_pk PRIMARY KEY (id_edificacao, code_administracao),
  CONSTRAINT fk_edificacao_adm FOREIGN KEY (id_edificacao) REFERENCES ge.edf_edificacao_pavimento_a(id) ON DELETE CASCADE,
  CONSTRAINT fk_administracao FOREIGN KEY (code_administracao) REFERENCES dominios.administracao(code)
);

-- 关联edificacao和classeativecon的多对多表
CREATE TABLE ge.edf_edificacao_classe_ativ_econ (
  id_edificacao integer NOT NULL,
  code_classe_ativ_econ smallint NOT NULL,
  CONSTRAINT edf_edificacao_classe_ativ_econ_pk PRIMARY KEY (id_edificacao, code_classe_ativ_econ),
  CONSTRAINT fk_edificacao_classe FOREIGN KEY (id_edificacao) REFERENCES ge.edf_edificacao_pavimento_a(id) ON DELETE CASCADE,
  CONSTRAINT fk_classe_ativ_econ FOREIGN KEY (code_classe_ativ_econ) REFERENCES dominios.classe_ativ_econ(code)
);

步骤2：移除主表中的旧列和约束 #

-- 删除主表的administracao列及相关约束
ALTER TABLE ge.edf_edificacao_pavimento_a
DROP COLUMN administracao,
DROP CONSTRAINT edf_edificacao_pavimento_a_administracao_fk,
DROP CONSTRAINT edf_edificacao_a_administracao_check;

-- 删除主表的classeativecon列及相关约束
ALTER TABLE ge.edf_edificacao_pavimento_a
DROP COLUMN classeativecon,
DROP CONSTRAINT edf_edificacao_pavimento_a_classeativecon_fk,
DROP CONSTRAINT edf_edificacao_a_classeativecon_check;

两种方案的对比 #

选择建议 #

• 用数组类型：如果只需要存储多个值，查询需求简单，想快速上线。
• 用多对多表：如果这是长期项目，需要频繁查询关联数据，或者未来可能添加更多关联属性。

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