帶有特定子句postgresql的upsert

我需要一些關于 postgres 中 upsert 的幫助

例如我有一個表結構，如：

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
 )

我想做的是如果用戶資料庫中不存在 users_id 則插入新用戶，然后如果 users_transaction.transaction_date < users_recency.first_transaction 則更新 first_transaction。

我試著用

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;

此查詢更新所有資料。有什么辦法可以實作嗎？

這是一個可以嘗試的 db fiddle

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

uj5u.com熱心網友回復：

您的查詢缺少一個WHERE條件，該條件將限制 first_transaction 的修改，以防它小于 transaction_date 值。

更新查詢：

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鏈接以便更好地理解：

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

一般Insert語法：

[ 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 ]

有關Insert查看以下官方檔案鏈接的更好資訊：

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

uj5u.com熱心網友回復：

通過觸發器。 演示

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 ();

標籤：sql PostgreSQL

上一篇：插入沒有空值

下一篇：如果非負數字值有加號，則無法轉換為jsonb