does "\copy" command handle commit and rollback in postgres?

Question

I am trying execute .sql file with psql. I run .sql file inside i have written following query \copy table name from .dumb. So if the command fails, will it handle commit/rollback by default. or we need to take care of that.

please edit your Q. are you trying to run sql file or `\copy table from file`?.. — Vao Tsun, Dec 20 '16 at 09:17

Vao Tsun · Accepted Answer · 2016-12-20T09:37:09.473

1

If \copy fails transaction will be aborted, here is example:

t=# \! cat s07
create table trans(i int);
copy s07 from '/no such file';
t=# begin;
BEGIN
t=# \i s07
CREATE TABLE
psql:s07:2: ERROR:  could not open file "/no such file" for reading: No such file or directory
t=# select * from trans;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
t=# end;
ROLLBACK

edited Dec 20 '16 at 09:37

answered Dec 20 '16 at 09:16

Vao Tsun

47,234
13
100
132

So whats behavior for commit. – Shashi Dec 20 '16 at 09:32
i thought to use conn.BeginTransaction(),,, in finally() block committing the changes to DB. – Shashi Dec 20 '16 at 09:34
or can i add "commit;" after "\copy TableName From D:.dump" in my .sql File? – Shashi Dec 20 '16 at 09:36

score 1 · Answer 2 · answered Dec 20 '16 at 09:43

1

All depends on version of PG you are running. Because default for psql is autocommit on. And in newer versions you cannot set it off. So every successful manually issued COPY or \copy command is immediately commited.

answered Dec 20 '16 at 09:43

JosMac

2,164
1
17
23

does "\copy" command handle commit and rollback in postgres?

2 Answers2

Linked