PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres check if row exists before inserting

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?

Tags:

2

Answers


  1. 0

    Here’s how you can structure your query:

    • Check if the row exists
    • Update if a single row exists
    • Update the row with the latest dateTime
        INSERT INTO db.table ("customerNumber", "productId", "dateTime", "action") 
    VALUES (:customerNumber, :productId, (now() at time zone 'utc'), :action)
    ON CONFLICT ("customerNumber") DO 
    UPDATE 
    SET "productId" = :productId, 
        "dateTime" = (now() at time zone 'utc'), 
        "action" = :action
    WHERE db.table.id = (
        SELECT id FROM db.table 
        WHERE "customerNumber" = :customerNumber 
        ORDER BY "dateTime" DESC 
        LIMIT 1
    );

    To perform this operation within a single query, you can use the SQL MERGE statement.

    Login or Signup to reply.
  2. 0

    You can use the following query to solve the issue that you have:You can use CTE to solve the following query and the query is as follows:

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

    The above query using the CTE expression will first attempt to update the first row with the matching customerNumber and the latest dateTime.

    If a row is updated, it returns the updated row’s data.

    If no row matches the criteria, it returns an empty result.

    Lastly insert a new row only if the updatedvalues_row CTE returned no rows

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search