Prevent Duplicate Donations with Postgres Constraints

Updated 14 November 2026 · Standard formula, shown below

This guide shows you how to stop duplicate donations from being recorded in your PostgreSQL database, even when a donor accidentally clicks “submit” twice or your API retries a failed request. You’ll implement a unique constraint on an idempotency key column, then use that key in your insert logic to safely reject or ignore repeats. You’ll need a working PostgreSQL database (9.5+ for ON CONFLICT) and basic familiarity with SQL and your application’s donation flow.

What You’ll Need

A PostgreSQL database (version 9.5 or later) with a table that stores donations.
Access to run ALTER TABLE and CREATE UNIQUE INDEX statements.
Knowledge of how your application generates unique identifiers (e.g., UUIDs) or how you’ll generate idempotency keys.
Understanding of your current donation insertion code (SQL or an ORM).

Step-by-Step

Add an idempotency key column to your donations table. If it doesn’t exist, run:
```
ALTER TABLE donations ADD COLUMN idempotency_key UUID;
```
(Or use TEXT if your keys aren’t UUIDs.) This column will hold a unique identifier for each logical donation attempt.
Create a unique index on that column. This is the core constraint that prevents duplicates:
```
CREATE UNIQUE INDEX donations_idempotency_key_idx ON donations (idempotency_key);
```
This ensures no two rows can share the same key.
Generate a new idempotency key for each donation attempt. In your application, before inserting, create a UUID (e.g., uuid4() in Python, UUID.randomUUID() in Java, or gen_random_uuid() in Postgres). The key should be the same for retries of the same logical attempt, but different for distinct attempts.
Modify your insert statement to use ON CONFLICT. Replace your plain INSERT with something like:
```
INSERT INTO donations (amount, donor_email, idempotency_key)
VALUES ($1, $2, $3)
ON CONFLICT (idempotency_key) DO NOTHING;
```
If the key already exists, the insert is skipped, and no duplicate row is created.
Check the result of the insert. After executing the statement, check how many rows were affected. If it’s 0, the donation was a duplicate—you can return a “already processed” response to the client without charging them again.
Handle the case where you need the existing row. If you must return the original donation details, use ON CONFLICT (idempotency_key) DO UPDATE SET idempotency_key = EXCLUDED.idempotency_key RETURNING * (this is a trick to force a return, but it’s safer to just query by the key after a conflict).
Test with a duplicate key. Insert a row with a known key, then try to insert the same key again. Confirm the second insert affects 0 rows.

Common Mistakes

Not using a unique index. A regular index won’t prevent duplicates—only a unique index or constraint will.
Allowing NULL idempotency keys. In PostgreSQL, NULLs are considered distinct, so multiple NULLs won’t violate the unique constraint. If you want to enforce uniqueness on all rows, make the column NOT NULL or use a partial unique index like WHERE idempotency_key IS NOT NULL.
Generating a new key on retry. The whole point is that a retry uses the same key. If your client generates a new key each time, duplicates will still occur.
Forgetting to handle the conflict in code. Even with ON CONFLICT DO NOTHING, your application must check the affected row count to know whether the insert succeeded.
Using ON CONFLICT without specifying the conflict target. You must name the column or constraint: ON CONFLICT (idempotency_key), not just ON CONFLICT DO NOTHING (though the latter works, it’s less explicit and can be ambiguous).

Tips

Use UUIDs for idempotency keys. They are globally unique and easy to generate in any language.
Store the idempotency key in a separate column, not as part of a composite key. This keeps your primary key clean and makes the uniqueness check straightforward.
Consider a partial unique index if you only care about non-null keys. For example, if you only set the key for online donations, use CREATE UNIQUE INDEX ... ON donations (idempotency_key) WHERE idempotency_key IS NOT NULL; This allows multiple NULLs for offline donations.
Wrap your insert in a transaction. If you also need to update a user’s balance or send a receipt, do it in the same transaction as the insert to keep everything atomic.
Log conflicts. When a duplicate is detected, log it with the key and timestamp. This helps you monitor for unexpected retries or bugs.
Use gen_random_uuid() directly in SQL if you’re inserting from a database function—it’s built into PostgreSQL 13+ (or via the pgcrypto extension earlier).

Troubleshooting

I get an error like “duplicate key value violates unique constraint” even with ON CONFLICT. This usually means you didn’t specify the correct conflict target. Check that the column name in ON CONFLICT (idempotency_key) matches exactly the column in the unique index. Also, if you have a primary key that conflicts, you need to handle that separately.
The unique index isn’t being used. If you see a full table scan in EXPLAIN, it might be because the column has a different type (e.g., TEXT vs VARCHAR) or the index is not unique. Verify with \d donations that the index exists and is unique.
Duplicate rows still appear. Check if any rows have NULL idempotency keys. If so, the unique index won’t block them. Add NOT NULL or a partial index as described above.
My insert returns 0 rows affected, but I expected the existing row. That’s normal with DO NOTHING. To get the existing row, you can do a separate SELECT after detecting a conflict, or use ON CONFLICT ... DO UPDATE with a dummy update to force a RETURNING.
I’m using an ORM and it doesn’t support ON CONFLICT. You can still execute raw SQL for the insert, or use the ORM’s upsert method if available (e.g., Django’s insert(...).on_conflict_do_nothing()).

Frequently Asked Questions

What exactly is an idempotency key?

It’s a unique identifier that a client sends with a request to indicate that the request is a retry of a previous one. The server uses it to recognize and ignore duplicates.

Can I use the same idempotency key for different donations?

No. Each logical donation attempt must have its own unique key. If you reuse a key, the second donation will be blocked.

What if my donation table already has duplicates?

Before adding the unique index, you must clean up existing duplicates. Run a query to find duplicates and decide which one to keep, then delete the others.

Does this work with PostgreSQL 9.5?

Yes, ON CONFLICT was introduced in 9.5. For gen_random_uuid(), you’ll need version 13 or the pgcrypto extension.

Should I use a UUID or a string for the key?

UUID is recommended because it’s compact, globally unique, and has built-in generation in most languages. Strings are fine if you have a specific format.

What if my application crashes after inserting but before sending a response?

The insert is already committed, so when the client retries with the same key, the conflict will be detected and the retry will be ignored. That’s exactly what you want.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.