upsert with spesific clause postgresql-CodePudding

i need some help regarding upsert in postgres

for example i have a table structure like:

CREATE TABLE users_recency(
  id INTEGER PRIMARY KEY,
  created_date TIMESTAMP WITHOUT TIME ZONE,
  first_transaction TIMESTAMP WITHOUT TIME ZONE
 )
 
 CREATE TABLE users_transaction(
   id INTEGER,
   users_id INTEGER,
   transaction_date TIMESTAMP WITHOUT TIME ZONE
 )

What i wanted to do is insert new users if users_id doesn't exists in users database and then update first_transaction if users_transaction.transaction_date < users_recency.first_transaction.

i tried to use

INSERT INTO users_recency(id, first_transaction)
SELECT users_id, MIN(transaction_date) FROM users_transaction GROUP BY users_id
ON CONFLICT (id) 
DO 
   UPDATE SET first_transaction = EXCLUDED.first_transaction;

this query updates all the data. is there any way to achieve?

here's a db fiddle to try

https://www.db-fiddle.com/f/nKk3uaucfRB46kXcGokPKB/1

CodePudding user response：

Your query is missing a WHERE condition which will restrict the modification of first_transaction in case it is less than transaction_date value.

Updated Query:

INSERT INTO users_recency(id, first_transaction)
SELECT users_id, MIN(transaction_date) FROM users_transaction GROUP BY users_id
ON CONFLICT (id)
DO
UPDATE SET first_transaction = EXCLUDED.first_transaction
WHERE EXCLUDED.first_transaction < users_recency.first_transaction;

db fiddle link for better understanding:

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=a8d6b6601424006219dbec5db47817c9

General Insert Syntax:

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

For better info on the Insert check out the below official doc link:

https://www.postgresql.org/docs/current/sql-insert.html

CodePudding user response：

via trigger. demo

CREATE OR REPLACE FUNCTION users_txn_trg_func ()
    RETURNS TRIGGER
    AS $$
DECLARE
    min_tx_date date;
BEGIN
    IF EXISTS (
        SELECT
        FROM
            users_transaction u
        WHERE
            u.users_id = NEW.users_id) THEN
    SELECT
        min(transaction_date) INTO min_tx_date
    FROM
        users_transaction
    WHERE
        users_id = NEW.users_id;
    IF NEW.transaction_date < min_tx_date THEN
        min_tx_date := NEW.transaction_date;
    END IF;
    RAISE info 'new user_id: %, min_tx_date: %', NEW.users_id, min_tx_date;
ELSE
    min_tx_date := NEW.transaction_date;
    RAISE NOTICE 'min_tx_date: %', min_tx_date;
END IF;
INSERT INTO users_recency (id, first_transaction)
    VALUES (NEW.users_id, min_tx_date)
ON CONFLICT (id)
    DO UPDATE SET
        first_transaction = EXCLUDED.first_transaction;
    RETURN new;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_name
    BEFORE INSERT ON users_transaction FOR EACH ROW
    EXECUTE PROCEDURE users_txn_trg_func ();