Is it wasteful to use ARRAY_AGG to get the first non-NULL value in a column?

Question

I use PostgreSQL 14 to manage a table which stores updates to a table of medics: users can update the first name, last name, and or/ the age of the medic. A field which was not touched by an update operation has a NULL value.

Here's an example of four edits touching two separate medics. The medic with ID 3 received three updates: the first two are updating the age field, the third one touches the first_name:

SELECT * FROM medic_edits;

id	medic_id	first_name	last_name	age
1	1	Indy
2	3			59
3	3			63
4	3	Bob

I would like to merge this table such that in the resulting table there's one row per medic, giving the cumulative edits. This is my current query and the output it produces:

SELECT
  medic_id,
  (ARRAY_REMOVE(ARRAY_AGG(first_name ORDER BY id DESC), NULL))[1] AS first_name,
  (ARRAY_REMOVE(ARRAY_AGG(last_name ORDER BY id DESC), NULL))[1] AS last_name,
  (ARRAY_REMOVE(ARRAY_AGG(age ORDER BY id DESC), NULL))[1] AS last_name
FROM medic_edits
GROUP BY medic_id
;

medic_id	first_name	last_name	last_name
1	Indy
3	Bob		63

This is exactly the output I expected, but I suspect that the ARRAY_REMOVE/ARRAY_AGG logic is a bit wasteful. I wonder if there is a way to use partitions for good profit here, the FIRST_VALUE function looks very relevant.

Answer 1

Yes, it's wasteful. I expect this to be faster:

SELECT DISTINCT ON (medic_id)
       medic_id
     , first_value(first_name) OVER (PARTITION BY medic_id ORDER BY CASE WHEN first_name IS NOT NULL THEN id END) AS first_name
     , first_value(last_name)  OVER (PARTITION BY medic_id ORDER BY CASE WHEN last_name  IS NOT NULL THEN id END) AS last_name
     , first_value(age)        OVER (PARTITION BY medic_id ORDER BY CASE WHEN age        IS NOT NULL THEN id END) AS age
FROM   medic_edits;

For descending id value, use instead:

       first_value(first_name) OVER (PARTITION BY medic_id ORDER BY CASE WHEN first_name IS NOT NULL THEN id END DESC NULLS LAST) AS first_name

See:

• Sort by column ASC, but NULL values first?

But there are probably faster ways, yet. Also depends on the exact table definition, cardinalities, and data distribution.

See:

• Fetch a row that contains the set of last non-NULL values for each column

About DISTINCT ON:

• Select first row in each GROUP BY group?

Works in a single SELECT because DISTINCT or DISTINCT ON are applied after window functions. See:

• Best way to get result count before LIMIT was applied

Aside: "age" is going to bit-rot rapidly. It's typically superior to store a birthday.