Adding boolean column to check whether entry is most recent of their attributes in Postgres SQL table?

user_4107349 0
Viewed 71

Simplified Postgres table looks like this:

date        attribute
2002-04-14  C
2001-12-07  A
1990-12-19  A
1990-12-20  B
1991-01-29  A
1991-07-01  C
1990-07-25  B
1999-05-13  B
1990-09-25  A

We need to add a boolean column checking whether the date is the most recent of entries of attribute. The desired_col in the table:

date        attribute   desired_col
2002-04-14  C           TRUE
2001-12-07  A           TRUE
1990-12-19  A           FALSE
1990-12-20  B           FALSE
1991-01-29  A           FALSE
1991-07-01  C           FALSE
1990-07-25  B           FALSE
1999-05-13  B           TRUE
1990-09-25  A           FALSE

This is the closest thing I can find, but makes a new table, but I only want a column.

Someone knowledgeable has suggested creating a trigger, but I don't understand these or their relevance.

If it helps, we could do this in pandas with df['desired_col'] = (df.groupby('attribute')['date'].transform('max') == df['date']). How can we do this in PostgreSQL?

pandas postgresql sql
1 Answers

You may achieve this in a postgresql query using window functions eg

SELECT
    df.date,
    df.attribute,
    df.date = MAX(df.date) OVER (PARTITION BY df.attribute) as desired_col
FROM
    df;
date attribute desired_col
1990-09-25 A false
2001-12-07 A true
1990-12-19 A false
1991-01-29 A false
1990-07-25 B false
1999-05-13 B true
1990-12-20 B false
2002-04-14 C true
1991-07-01 C false

View on DB Fiddle

Let me know if this works for you.

Edit 1

If you would like to modify the existing table, then you may add the desired_column as a boolean column with a default value of false and running an update as shown below:

Query #1

ALTER TABLE df
ADD desired_column bool DEFAULT FALSE;

There are no results to be displayed.

Query #2

SELECT * FROM df;
date attribute desired_column
2002-04-14T00:00:00.000Z C false
2001-12-07T00:00:00.000Z A false
1990-12-19T00:00:00.000Z A false
1990-12-20T00:00:00.000Z B false
1991-01-29T00:00:00.000Z A false
1991-07-01T00:00:00.000Z C false
1990-07-25T00:00:00.000Z B false
1999-05-13T00:00:00.000Z B false
1990-09-25T00:00:00.000Z A false

Query #3

UPDATE df 
SET
    desired_column = true
FROM (
    SELECT df2.attribute, MAX(df2.date) as max_date
    FROM df as df2
    GROUP BY df2.attribute
) df3 
WHERE  df.attribute = df3.attribute AND
       df.date = df3.max_date;

There are no results to be displayed.

Query #4

SELECT * FROM df;
date attribute desired_column
1990-12-19T00:00:00.000Z A false
1990-12-20T00:00:00.000Z B false
1991-01-29T00:00:00.000Z A false
1991-07-01T00:00:00.000Z C false
1990-07-25T00:00:00.000Z B false
1990-09-25T00:00:00.000Z A false
2002-04-14T00:00:00.000Z C true
2001-12-07T00:00:00.000Z A true
1999-05-13T00:00:00.000Z B true

View on DB Fiddle

It should be noted that maintaining this column performance wise may be expensive as it could mean that adding a new record would trigger and update (which can be implemented) but would not be ideal if you do need the performance of a materialized view. It may be more efficient to have your table and when results for this column is desired then you run a query or you query a view that references the table eg.

CREATE OR REPLACE VIEW vw_df AS 
SELECT
    df.date::text,
    df.attribute,
    df.date = MAX(df.date) OVER (PARTITION BY df.attribute) as desired_col
FROM
    df
ORDER BY 
    df.date DESC,
    df.attribute;

View working demo db fiddle

Let me know if this works for you.

user_43071360
Related