I have a C# program calling a PostgreSQL upsert, using ON CONFLICT DO UPDATE
, which returns some data about the data being insert/updated using RETURNING
. Below is a simplified version of the SQL. I want to be able to take different actions in my C# code based on whether there was a conflict or not, such as logging a message that the record already existed. Is there a way to return whether there was a conflict? I'd rather not have to do a separate query to first determine if the order exists.
INSERT INTO order(order_key, order_id, order_status, product_id, cust_id, cust_name, order_qty)
VALUES (@OrderKey, @OrderId, @OrderStatus, @ProductId, @CustId, @CustName, @OrderQty)
ON CONFLICT ON CONSTRAINT cust_id_order_id_unique
DO
UPDATE SET
order_status = EXCLUDED.order_status,
cust_id = EXCLUDED.cust_id,
cust_name = EXCLUDED.cust_name,
product_id = EXCLUDED.product_id,
order_qty = EXCLUDED.order_qty
RETURNING "order".order_key as OrderKey;
Can this be done? If so, how?