PostgreSQL插入数据引用其他表值，除子查询外还有哪些实现方式？

可行的实现方案如下 #

方案1：分两次数据库请求执行（先查科目ID再插入） #

逻辑直观，先单独查询对应科目名称的ID，再将ID作为参数执行学生插入操作，如果需要保证两次操作的原子性，加上事务即可：

public boolean insertStudent(String student_name, float score, String subjectName) {
    boolean res = false;
    // 开启事务，关闭自动提交
    try {
        c.setAutoCommit(false);
        // 第一步：查科目ID
        Integer subjectId = null;
        try(PreparedStatement queryStmt = c.prepareStatement("SELECT id FROM subject WHERE name = ?")) {
            queryStmt.setString(1, subjectName);
            ResultSet rs = queryStmt.executeQuery();
            if(rs.next()) {
                subjectId = rs.getInt("id");
            } else {
                // 科目不存在的自定义处理逻辑，比如抛出异常
                throw new IllegalArgumentException("科目:" + subjectName + "不存在");
            }
        }
        // 第二步：插入学生数据
        try(PreparedStatement insertStmt = c.prepareStatement("INSERT INTO student(student_name,score,subject_id) VALUES(?,?,?)")) {
            insertStmt.setString(1, student_name);
            insertStmt.setFloat(2, score);
            insertStmt.setInt(3, subjectId);
            res = insertStmt.executeUpdate() > 0;
        }
        // 提交事务
        c.commit();
    } catch (SQLException e) {
        // 异常回滚
        try {
            c.rollback();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        e.printStackTrace();
        throw new RuntimeException(e);
    } finally {
        try {
            c.setAutoCommit(true);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return res;
}

优点：逻辑可读性高，方便在Java层做科目不存在的自定义业务处理。

方案2：使用INSERT ... SELECT语法替换嵌套子查询 #

写法比嵌套子查询更简洁，执行效率和你现有的子查询方案基本一致，属于SQL层面的优化写法：

private static final String INSERT_STU = "INSERT INTO student(student_name,score,subject_id) SELECT ?, ?, id FROM subject WHERE name = ?";

Java代码不需要做任何修改，直接沿用你现有逻辑即可。 #

方案3：封装为PostgreSQL存储函数 #

如果这类插入逻辑使用频率很高，可以把逻辑封装到数据库层面的自定义函数，Java端直接调用函数即可：
首先在PostgreSQL中创建函数：

CREATE OR REPLACE FUNCTION insert_student(
    p_student_name VARCHAR,
    p_score FLOAT,
    p_subject_name VARCHAR
) RETURNS BOOLEAN AS $$
DECLARE
    v_subject_id INT;
BEGIN
    SELECT id INTO v_subject_id FROM subject WHERE name = p_subject_name;
    IF v_subject_id IS NULL THEN
        RAISE EXCEPTION '科目%不存在', p_subject_name;
    END IF;
    INSERT INTO student(student_name, score, subject_id) VALUES (p_student_name, p_score, v_subject_id);
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

Java端调用方式：

public boolean insertStudent(String student_name, float score, String subjectName) {
    try(CallableStatement cs = c.prepareCall("{? = call insert_student(?, ?, ?)}")) {
        cs.registerOutParameter(1, Types.BOOLEAN);
        cs.setString(2, student_name);
        cs.setFloat(3, score);
        cs.setString(4, subjectName);
        cs.execute();
        return cs.getBoolean(1);
    } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException(e);
    }
}

方案4：支持科目不存在时自动新增科目（可选） #

如果你的业务允许传入不存在的科目名称时自动在subject表创建对应科目，可以用CTE+UPSERT的写法实现：

private static final String INSERT_STU = """
    WITH s AS (
        INSERT INTO subject(name) VALUES(?) 
        ON CONFLICT(name) DO UPDATE SET name = EXCLUDED.name
        RETURNING id
    )
    INSERT INTO student(student_name, score, subject_id) 
    SELECT ?, ?, s.id FROM s
""";

这种写法可以实现插入学生时自动补全不存在的科目，不需要额外的判断逻辑。

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