Array Aggregation in PostgreSQL: Selecting Patients with All Diseases

In this article, we will explore how to use PostgreSQL’s array handling features to select rows where all columns have values in a list. We’ll dive into the technical details of array aggregation and provide examples to illustrate its usage.

Introduction to Arrays in PostgreSQL

PostgreSQL supports arrays as a data type, allowing you to store multiple values in a single column. This feature is particularly useful when working with datasets that require storing multiple related values, such as diseases diagnosed by a patient.

Arrays are defined using the array keyword followed by the elements within curly brackets {}. For example:

CREATE TABLE patients (
    subject_id INTEGER,
    hadm_id INTEGER,
    icd9_code INTEGER[]
);

In this example, the icd9_code column is declared as an array of integers.

Array Aggregation Functions

PostgreSQL provides several aggregation functions that can be used to manipulate arrays. Two essential functions for this article are array_agg() and @> (the “greater than” operator).

array_agg()

The array_agg() function collects all elements from a table into an array. This function is used to group rows together based on specific conditions.

Example:

SELECT subject_id, icd9_code
FROM patients
GROUP BY subject_id
ORDER BY subject_id;

This query will return each patient’s subject_id and the corresponding icd9_code.

@> Operator

The @> operator checks if an array contains all elements from another expression. This is useful when you want to select rows where a column has values that are present in a specific list.

Example:

SELECT subject_id
FROM patients
GROUP BY subject_id
HAVING array_agg(distinct icd9_code) @> array[4280, 5849];

In this example, we group the patients table by subject_id and use array_agg() to collect all distinct icd9_code values. The @> operator then checks if the resulting array contains both 4280 and 5849.

Real-World Example: Selecting Patients with All Diagnoses

Suppose we have a hospital database where each patient is assigned a unique subject_id, and each admission has a corresponding hadm_id. The icd9_code column represents the disease diagnosed during that admission. We want to select patients who have been diagnosed with at least all of the following diseases: 4280, 5849, and 4019.

To achieve this, we can use the array_agg() function in combination with the @> operator:

SELECT subject_id
FROM patients
GROUP BY subject_id
HAVING array_agg(distinct icd9_code) @> array[4280, 5849, 4019];

This query will return the patient IDs (subject_id) who have been diagnosed with all three specified diseases.

Handling Missing Values

When working with arrays in PostgreSQL, it’s essential to consider how missing values are handled. By default, array_agg() will ignore null elements and only include non-null values. However, you can modify this behavior using the ALL keyword.

Example:

SELECT subject_id
FROM patients
GROUP BY subject_id
HAVING array_agg(distinct icd9_code) @> array[4280, 5849] AND array_agg(distinct icd9_code) != ARRAY[]::integer[];

In this example, we add the AND condition to ensure that the resulting array is not empty. The second part of the condition uses the != operator to check if the array is equal to an empty array (ARRAY[]::integer[]). This ensures that only patients with actual diagnoses are returned.

Limitations and Considerations

While PostgreSQL’s array handling features provide a powerful toolset for manipulating arrays, there are some limitations and considerations to keep in mind:

• Data type compatibility: When combining arrays using the @> operator, ensure that both sides of the comparison have compatible data types. For example, comparing an array of integers with an array of strings will result in an error.
• Null values: As mentioned earlier, missing values are handled differently depending on the specific aggregation function used. Always verify how null values are being treated in your queries to avoid unexpected results.
• Large datasets: When working with large datasets, consider using indexes on the array columns to improve query performance.

Conclusion

PostgreSQL’s array handling features provide a robust way to manipulate arrays and perform complex operations on them. By mastering these features, you can unlock new insights in your data analysis work. Remember to carefully consider how missing values are handled and ensure compatibility between data types when using array aggregation functions. With practice and experience, you’ll become proficient in working with PostgreSQL’s array features to solve real-world problems.

Last modified on 2025-04-22