Postgres: test insert or update before doing it?

Question

We have a table that only ever contains one entry of a particular type, so if we get an request for an insert we delete everything and then do the insert. The insert comes from user data and there have been times where it throws an error (maybe they didn't fill something in, etc) and we end up with a bunch of deleted data. We are handling all the data processing so we don't have error like that anymore but I was wondering:

Is there a way to test an insert or update to see if it will work before actually doing it?

Sounds like you should use database transactions: rollback on errors, commit on success. — alecxe
– alecxe, Commented Jan 15, 2014 at 16:38

Erwin Brandstetter · Accepted Answer · 2014-01-15 18:50:53Z

4

Open a session and do everything in a transaction:

BEGIN;
INSERT ...

-- check if it worked
ROLLBACK;

Everything will be rolled back. Some possible side effects, though: incremented sequences are not reset. Some functions like dblink() cannot be rolled back. Plain INSERT or UPDATE are safe. Be aware that you may be locking rows as long as your transaction is open.

However, if you are operating with valuable data, you shouldn't toy with it like that. Create a copy of your database for testing.

answered Jan 15, 2014 at 18:50

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: test insert or update before doing it?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related