Postgres check if row exists before inserting

Question

I have the following table

id	customerNumber	productId	dateTime	action
2	12	NA	2018-11-04 14:55:16.000000	active
1	12	NA	2018-11-03 13:51:08.000000	active

The problem I have is that every time an action is registered, a new insert with a new row is created which I don't need. Over time this table fills up.

INSERT INTO db.table("customerNumber", "productId", "dateTime", "action") 
VALUES (:customerNumber, :productId, (now() at time zone ('utc')), :action);

So what I want to instead is to write a new query that follows the following criteria:

1. If the row does not exist, insert a new one.
2. If a single row exists with the customer number, update the values.
3. If multiple rows exists with the same customerNumber, then only the one with the latest dateTime should be updated.

In the table above for example, the first row with id == 2 should be updated only.

I have done some searching and testing. Many seem to refer to using the conflict operation, but this will not work since customerNumber is not unique here. How can I make this happen within a single query?

Answer 1

With inspiration of @Daqs answer and chatGpt, I got this working;

WITH updatedvalues_row AS (
    UPDATE db.table
    SET productId = :productId, dateTime = (now() at time zone 'utc'), action = :action
    WHERE (customerNumber, dateTime) = (
        SELECT customerNumber, MAX(dateTime) as dateTime
        FROM db.table
        WHERE customerNumber = :customerNumber
        GROUP BY customerNumber
    )
    RETURNING *
)
INSERT INTO db.table("customerNumber", "productId", "dateTime", "action")
SELECT :customerNumber, :productId, (now() at time zone 'utc'), :action
WHERE NOT EXISTS (SELECT 1 FROM updatedvalues_row);