更新Postgres系谱流程：为子代植物前缀添加@字符

现有系统背景 #

2012年基于Stack Overflow资源开发了一套植物育种用Postgres系谱管理流程，核心逻辑：

• 通过family/plant ID定义亲子层级关系
• 植物表ptst_plant通过id_family外键关联家族表ptst_family
• 根级父代植物ID固定为1（映射为'NA'），对应家族表中is_root='Y'的记录

当前问题 #

现有流程无法在系谱路径输出中区分实际子代，导致F2及以后世代的亲子关系辨识度不足。

核心需求 #

识别F2+非根级亲子关系，为对应子代的plant_key前缀添加@字符做标识。

实现思路 #

筛选目标关系 #

先筛选出ptst_family.is_root='N'的F2及以上世代的亲子关系记录。

标识子代逻辑 #

匹配ptst_family中雌雄亲本的plant_id，关联对应的ptst_plant.id_family找到前代家族，为该家族对应的子代plant_key添加@前缀。

可选实现方式 #

• 直接修改原有系谱生成脚本（pedigree.sql），在路径生成时实时添加标识
• 编写单独的更新脚本，批量修改系谱表的Path字段

测试资源（PostgreSQL 16.6） #

测试表结构 #

-- 家族表 ptst_family
CREATE TABLE ptst_family (
    id_family INT PRIMARY KEY,
    is_root CHAR(1) NOT NULL CHECK (is_root IN ('Y', 'N')),
    generation VARCHAR(10) -- 世代标识，如F1、F2等
);

-- 植物表 ptst_plant
CREATE TABLE ptst_plant (
    id_plant INT PRIMARY KEY,
    id_family INT NOT NULL REFERENCES ptst_family(id_family),
    plant_key VARCHAR(50) NOT NULL,
    parent_male INT REFERENCES ptst_plant(id_plant),
    parent_female INT REFERENCES ptst_plant(id_plant)
);

-- 系谱表（假设存在）
CREATE TABLE pedigree (
    id_pedigree INT PRIMARY KEY,
    id_plant INT NOT NULL REFERENCES ptst_plant(id_plant),
    path TEXT NOT NULL -- 系谱路径字段
);

测试数据示例 #

-- 根家族
INSERT INTO ptst_family VALUES (1, 'Y', 'NA');
-- F1家族
INSERT INTO ptst_family VALUES (2, 'N', 'F1');
-- F2家族
INSERT INTO ptst_family VALUES (3, 'N', 'F2');

-- 根级亲本
INSERT INTO ptst_plant VALUES (1, 1, 'NA', NULL, NULL);
-- F1植株
INSERT INTO ptst_plant VALUES (2, 2, 'F1-001', 1, 1);
-- F2子代植株
INSERT INTO ptst_plant VALUES (3, 3, 'F2-001', 2, 2);

2012年开发的pedigree.sql核心片段 #

-- 原有系谱路径生成逻辑（示例）
WITH RECURSIVE pedigree_path AS (
    SELECT 
        p.id_plant,
        p.plant_key AS path
    FROM ptst_plant p
    JOIN ptst_family f ON p.id_family = f.id_family
    WHERE f.is_root = 'Y'
    UNION ALL
    SELECT 
        c.id_plant,
        CONCAT(pp.path, ' -> ', c.plant_key) AS path
    FROM ptst_plant c
    JOIN pedigree_path pp ON c.parent_male = pp.id_plant OR c.parent_female = pp.id_plant
)
INSERT INTO pedigree (id_plant, path)
SELECT id_plant, path FROM pedigree_path;

期望输出示例 #

原有路径输出：NA -> F1-001 -> F2-001
优化后路径输出：NA -> F1-001 -> @F2-001

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