6

Suppose there is a table

version col_A   col_B   col_C
1       A1      B1     (null)
2       A2      B3     (null)
3       A3      B2     (null)
4       A5     (null)   C1
5       A1     (null)  (null)

What I need is to fetch the last non-null value for each column in Postgres i.e. For the above table, I expect the result as (A1, B2, C1).

Here 'last' means the last non-null value in each column when the table is ordered by column version.

The version column always contains non-null values only.

This table is not going to be huge as in this will be a couple of thousand rows only. So, not worried very much about performance.

1

5 Answers 5

6

I believe based on your updates that you want to sort the rows by the version column to get the last (non-NULL) value. You can achieve your end goal a few different ways by using window functions, but I found the FIRST_VALUE() window function with a CASE statement the simplest, with the following query (dbfiddle for demonstration):

SELECT DISTINCT
    FIRST_VALUE(col_A) OVER (ORDER BY CASE WHEN col_A IS NULL THEN 0 ELSE version END DESC) AS LastA,
    FIRST_VALUE(col_B) OVER (ORDER BY CASE WHEN col_B IS NULL THEN 0 ELSE version END DESC) AS LastB,
    FIRST_VALUE(col_C) OVER (ORDER BY CASE WHEN col_C IS NULL THEN 0 ELSE version END DESC) AS LastC
FROM tab

Using your updated example of:

version col_A   col_B   col_C
1       A1      B1     (null)
2       A2      B3     (null)
3       A3      B2     (null)
4       A5     (null)   C1
5       A1     (null)  (null)

This produces the result:

LastA   LastB   LastC
A1      B2      C1

The key to this solution is the CASE statement which is saying order the values by the version column except if the value is NULL which will be put that at end of the ordering (which is the IS NULL THEN 0 part of the CASE statement). This results in the value with the last version that isn't a NULL value to be returned (when using the FIRST_VALUE() function, and because the ORDER BY clause is DESC).

Note: This solution assumes your versions start at 1 (specifically there will never be a version = 0). If that is possible, then replace the 0 value in the CASE clause with a negative number that will never be used such as -1 or -9999.

0
6

You can do the following (all of the code below is available on the fiddle here):

CREATE TABLE tab
(
  version INT PRIMARY KEY,
  col_A TEXT,
  col_B TEXT,
  col_C TEXT
);

Data:

INSERT INTO tab (version, col_A, col_B, col_C)
VALUES
(1, 'A1', 'B1', null),
(2, 'A2', 'B3', null),
(3, 'A3', 'B2', null),
(4, 'A4', null, 'C1'),
(5, 'A5', null, null);

Then run this query:

select
    (select col_a from tab where col_a is not null order by version desc limit 1) as a,
    (select col_b from tab where col_b is not null order by version desc limit 1) as b,
    (select col_c from tab where col_c is not null order by version desc limit 1) as c ;

Result:

 a   b   c
A5  B2  C1

You might like to consider putting in something like the COALESCE function in case one (or more?) of the columns only contains NULL values. Finally, in future, when asking questions of this sort, could you please provide a fiddle with your tables and data? Help us to help you - p.s. welcome to dba.se!

Performance Analysis:

As of 2021-11-26 07:40:00 UTC, I have done a performance analysis which is available here.

With 1k rows (and a unique index on (version)):

  1. Quassnoi option 1 ~ 0.060 ms
  2. Gerard H. Pille ~ 0.176 ms
  3. Quassnoi option 2 ~ 0.183 ms
  4. J.D. ~ 2.906 ms

With 20k rows (and 19k of them all nulls in columns A, B, C):

  1. Quassnoi option 1 ~ 8 ms
  2. Quassnoi option 2 ~ 15 ms
  3. Gerard H. Pille ~ 37 ms
  4. J.D. ~ 49 ms

With the same 20k rows as above after adding 3 partial indexes on (version) WHERE (col_a IS NOT NULL), etc.:

  1. Quassnoi option 1 ~ 0.8 ms
  2. Quassnoi option 2 ~ 14.0 ms
  3. Gerard H. Pille ~ 36.9 ms
  4. J.D. ~ 48.5 ms

With the same 20k rows as above after adding 3 partial indexes on (version) INCLUDE (col_a) WHERE (col_a IS NOT NULL), etc. Quassnoi-1 query kills it here, outperforming all the others by a big magnitude:

  1. Quassnoi option 1 ~ 0.12 ms
  2. Quassnoi option 2 ~ 11.9 ms
  3. Gerard H. Pille ~ 36.9 ms
  4. J.D. ~ 48.9 ms

Last test is with 20k rows but there are non-null values dispersed. Here both Gerars's and Quassnoi-2 are quite fast, not as good as Quassnoi-1 but the difference is tiny:

  1. Quassnoi option 1 ~ 0.050 ms
  2. Quassnoi option 2 ~ 0.112 ms
  3. Gerard H. Pille ~ 0.168 ms
  4. J.D. ~ 49.1 ms

Usual caveats concerning performing tests on a server over which one has no control or knowledge apply!

I'll keep it revised as the competition hots up!

1
5

The solution proposed by J.D. will work, but it will have to sort the whole table three times and do quite an expensive DISTINCT.

As was alluded to in the comments, efficiently solving this task would require a window function similar to FIRST_VALUE with a filter, which, unfortunately, PostgreSQL does not support.

In theory, this could be emulated by using an aggregate function on a record type like MAX(ROW(CASE WHEN col_a IS NOT NULL THEN VERSION END, col_a)), but for some reason PostgreSQL does not support MAX and MIN on record types either, even though it does support comparison and ordering on record types.

If you have a unique index on the column version, you could use one of these queries.

Option 1

SELECT  (
        SELECT  col_a
        FROM    aang
        WHERE   col_a IS NOT NULL
        ORDER BY
                version DESC
        LIMIT 1
        ),
        (
        SELECT  col_b
        FROM    aang
        WHERE   col_b IS NOT NULL
        ORDER BY
                version DESC
        LIMIT 1
        ),
        (
        SELECT  col_c
        FROM    aang
        WHERE   col_c IS NOT NULL
        ORDER BY
                version DESC
        LIMIT 1
        )

This query just scans the table from the bottom up three times, stopping at the first value of each column. Since your version is uniquely indexed, this scan will be very likely to use the index. It is more efficient if your last values in all three columns are close enough to the bottom.

Option 2

SELECT  a.col_a, b.col_b, c.col_c
FROM    (
        SELECT  last_a, last_b, last_c
        FROM    (
                SELECT  *,
                        MAX(version) FILTER (WHERE col_a IS NOT NULL) OVER w AS last_a,
                        MAX(version) FILTER (WHERE col_b IS NOT NULL) OVER w AS last_b,
                        MAX(version) FILTER (WHERE col_c IS NOT NULL) OVER w AS last_c
                FROM    aang
                WINDOW  w AS (ORDER BY version DESC)
                ) q
        WHERE   (last_a, last_b, last_c) IS NOT NULL
        ORDER BY
                version DESC
        LIMIT 1
        ) q
JOIN    aang a
ON      a.version = q.last_a
JOIN    aang b
ON      b.version = q.last_b
JOIN    aang c
ON      c.version = q.last_c

This query scans the table from the bottom up, the same as the previous one, but does it just once.

It then uses the MAX window function to remember the maximum value of the field version for the records which don't have nulls in the value columns (a, b, and c).

It stops scanning when all the three maxes are not null and returns a single record with the three values of the field version, which correspond to the version holding the last non-null value of the respective column.

Finally, it joins back to the table three times, retrieving the value of the column for each version.

It scans just once, but does an index seek three times (to retrieve the values). If your non-null values are relatively far from the bottom of the table, the scan will have to be more lengthy, so the cost of doing the scan just once outweighs the extra cost of doing the seeks.

0
3

first() aggregate function

This table is not going to be huge as in this will be a couple of thousand rows only. So, not worried very much about performance.

I suppose the custom aggregate function first() will be the simplest solution, then.

Install the custom aggregate function first() as instructed in the Postgres Wiki. (I updated the Wiki page while being at it.)

CREATE OR REPLACE FUNCTION public.first_agg (anyelement, anyelement)
  RETURNS anyelement
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT $1';

CREATE AGGREGATE public.first(anyelement) (
  SFUNC    = public.first_agg
, STYPE    = anyelement
, PARALLEL = SAFE
);

Then this can be your query:

SELECT first(col_a) AS col_a
     , first(col_b) AS col_b
     , first(col_c) AS col_c
FROM  (SELECT col_a, col_b, col_c FROM tab ORDER BY version DESC) sub;

Fast and simple for small tables. But does not scale well, so can't keep up with some of the optimized solutions for bigger tables.

PL/pgSQL function

CREATE OR REPLACE FUNCTION func(OUT _col_a text, OUT _col_b text, OUT _col_c text)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _row record;
BEGIN
   FOR _row IN
      SELECT col_a, col_b, col_c FROM tbl ORDER BY version DESC
   LOOP
      IF _col_a IS NULL AND _row.col_a IS NOT NULL THEN
         _col_a := _row.col_a;
      END IF;

      IF _col_b IS NULL AND _row.col_b IS NOT NULL THEN
         _col_b := _row.col_b;
      END IF;

      IF _col_c IS NULL AND _row.col_c IS NOT NULL THEN
         _col_c := _row.col_c;
      END IF;

      EXIT WHEN (_col_a, _col_b, _col_c) IS NOT NULL;
   END LOOP;
END
$func$;

Call:

SELECT * FROM func();

db<>fiddle here

Note that the expression (_col_a, _col_b, _col_c) IS NOT NULL is only true if all columns are NOT NULL. See:

The point is to scan the table once, and only until we find values, instead of multiple times and/or the complete table. The function adds considerable overhead. This approach looks better the more columns we process at once, the sooner we find the last missing value, and the wider the underlying table row.

This is much like the Gerard earlier function, but works with a single assignment per output columns, which should make a substantial difference as assignments are comparatively expensive in PL/pgSQL.

Index for Quassnoi's option 1

If performance is the paramount requirement (which it is not for the OP, but for our little performance bet), then Quassnoi's option 1 coupled with partial indexes should perform best:

CREATE INDEX ON aang (version DESC) INCLUDE (col_a) WHERE col_a IS NOT NULL;
CREATE INDEX ON aang (version DESC) INCLUDE (col_b) WHERE col_b IS NOT NULL;
CREATE INDEX ON aang (version DESC) INCLUDE (col_c) WHERE col_c IS NOT NULL;

Reduces the query plan to three perfectly fast index-only scans. But with only very few rows to traverse, the overhead weighs in, and the functions are still faster in a quick test.

first_value() with LIMIT 1

J.D.'s query with first_value() is much faster with LIMIT 1 instead of DISTINCT. Scales much better, as the cost of DISTINCT grows with O(N²), while the cost of LIMIT 1 is constant.

SELECT *
FROM  (
   SELECT first_value(col_a) OVER (ORDER BY CASE WHEN col_a IS NULL THEN 0 ELSE version END DESC) AS col_a
        , first_value(col_b) OVER (ORDER BY CASE WHEN col_b IS NULL THEN 0 ELSE version END DESC) AS col_b
        , first_value(col_c) OVER (ORDER BY CASE WHEN col_c IS NULL THEN 0 ELSE version END DESC) AS col_c
   FROM tbl
   ) sub
LIMIT 1;

Big benchmark roundup

Based on Vérace's fiddle (kudos!), with major changes:

  • Postgres 14 instead of Postgres 12
  • add VACUUM ANALYZE
  • drop redundant UNIQUE index
  • trim a lot of noise
  • add my additional solutions
  • drop the run with partial, non-covering indexes (little added value)
  • add numbers to guide through the many tests

db<>fiddle here

Quassnoi's option 1 and the functions dominate.

At the bottom I run the functions a couple of times to level caching effects. After that, my function is only little faster than Gerard's, - as only few rows have to be processed in that test.

0
2

I wonder how this one would perform:

create or replace function test_get_lnn ()
  returns table(col_A text, col_B text, col_C text)
language plpgsql
as $$
  declare
    t_a text := null;
    t_b text := null;
    t_c text := null;
    r record;
  begin
    for r in
      select t.col_A, t.col_B, t.col_C from test t order by version desc
    loop
      if t_a is null then t_a := r.col_A; end if;
      if t_b is null then t_b := r.col_B; end if;
      if t_c is null then t_c := r.col_C; end if;
      exit when t_a is not null and t_b is not null and t_c is not null;
    end loop;
    return query select t_a, t_b, t_c;
  end;
$$
;

with

select * from test;
 version | col_a | col_b | col_c 
---------+-------+-------+-------
       1 | A1    | B1    | 
       2 | A2    | B2    | 
       3 | A3    | B3    | 
       4 | A4    |       | C1
       5 | A6    | B2    | 
       6 | A7    |       | C0
       7 | A5    |       | 

it gives

select col_A, col_B, col_C from test_get_lnn();
 col_a | col_b | col_c 
-------+-------+-------
 A5    | B2    | C0
(1 row)

EDIT.

Ypercube and Vérace's db<>fiddle.uk allowed me to run some tests of my own on my antique PC. What I did can be seen in this here fiddle, and psql's \timing gave the following:

 col_a | col_b | col_c |    current_time    |      author       
-------+-------+-------+--------------------+-------------------
 A5    | B5    | C1    | 13:57:04.180964+00 | Quassnoi Option 1
(1 row)

Time: 0.512 ms
 col_a | col_b | col_c |    current_time    |      author       
-------+-------+-------+--------------------+-------------------
 A5    | B5    | C1    | 13:57:04.181546+00 | Quassnoi Option 2
(1 row)

Time: 0.543 ms
 lasta | lastb | lastc |    current_time    | author 
-------+-------+-------+--------------------+--------
 A5    | B5    | C1    | 13:57:04.182155+00 | J.D.
(1 row)

Time: 1119.801 ms (00:01.120)
 col_a | col_b | col_c |    current_time    |     author      
-------+-------+-------+--------------------+-----------------
 A5    | B5    | C1    | 13:57:05.302037+00 | Gerard H. Pille
(1 row)

Time: 0.263 ms
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.