如何将相同prc_id对应的多个knw_id合并至一行并解决SQL子查询CardinalityViolation错误

First, let's break down why your current query is throwing that error: the subquery inside your SELECT clause returns multiple rows (all knw_ids that appear more than once across the entire table), but PostgreSQL expects a single value when using a subquery as an expression in SELECT. That's the root cause of the CardinalityViolation error.

To fix this, you need an aggregate function that collects all related knw_ids into a single array (or string) per prc_id. Here's how to do it correctly, while keeping your join with the process table:

Correct SQL Query #

SELECT 
    p.prc_id,
    p.prc_name,
    array_agg(pke.knw_id) AS knw_ids
FROM processknowledgeentry pke
INNER JOIN process p ON pke.prc_id = p.prc_id
WHERE pke.prc_id = %s -- Remove this line if you want results for all prc_ids
GROUP BY p.prc_id, p.prc_name;

Key Changes Explained: #

• array_agg(pke.knw_id): This PostgreSQL aggregate function takes all knw_id values for a grouped prc_id and wraps them into a native array (e.g., [2,4]), which exactly matches your desired output format.
• GROUP BY p.prc_id, p.prc_name: Since we're using an aggregate function, we need to group by all non-aggregated columns. Assuming prc_id is the primary key of the process table, grouping by both columns is safe (and required in most SQL dialects to avoid ambiguity).
• Removed the problematic subquery: Instead of trying to fetch knw_ids with a separate subquery, we directly aggregate them from the joined tables, which aligns with your grouping requirement.

Alternative: Comma-Separated String Instead of Array #

If you prefer a comma-separated string (like "2,4") instead of an array, use string_agg instead:

SELECT 
    p.prc_id,
    p.prc_name,
    string_agg(pke.knw_id::text, ', ') AS knw_ids
FROM processknowledgeentry pke
INNER JOIN process p ON pke.prc_id = p.prc_id
WHERE pke.prc_id = %s
GROUP BY p.prc_id, p.prc_name;

Note: We cast knw_id to text because string_agg requires string inputs.

Verifying the Logic #

Your original goals are fully addressed here:

1. Grouping by prc_id: The query groups all knw_ids under their corresponding prc_id.
2. Process table association: The join to process is retained to fetch prc_name.
3. Knowledge table compatibility: Since knw_ids are stored as original numeric IDs (either in array or string form), you can still join back to the knowledge table—for example, using WHERE knw_id = ANY(knw_ids) for array values.

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