CODEWITHSUNDEEP
postgresql
jlars62
jlars62

Reputation: 7353

If a query with multiple insert values fails, what happens to prior values

I am using postgresql, and suppose I have the following query.

insert into table_name values (1, 1), (1, 3);

And lets say inserting values (1,3) fail. Do the values (1,1) get inserted?

From the tests I have done, the first values that do not cause any errors do not get inserted, but I haven't been able to find any sources to back that up. What I have tried to google has come up with different subjects.

In general when inserting multiple values, is it an all or nothing type of thing? Or is it possible for only some of the values to get inserted?

Thanks

Upvotes: 5

Views: 1919

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125224

Do the values (1,1) get inserted?

No. Each statement in Postgresql is a transaction in itself

From the manual:

PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it.

Upvotes: 10

Related Questions