Does psql -f quit insertion after an error in a statement has been detected?

Wang, Mary Y wrote:

Hi,

I'm using a psql -f command to reload the data from a dump file. I noticed that some tables are not populated with any rows (I mean 0 rows), yet, if I manually insert a row (actually just copy an INSERT statement from that input file) in the interactive terminal, that row was added with no problem. So my question "does psql -f quits inserting rows for a table when it detects there is an error in a statement?". The impression that I got is that even though other rows might not have any errors, but psql -f seems just quits after it detects an error in a row.

Am I missing something here?

if the inserts are in a transaction (eg, after a BEGIN), any error will
cause the entire transaction to rollback and the rest of the transaction
to be 'flushed' until a ROLLBACK or COMMIT statement ends the
transaction block.

On Sun, Jun 6, 2010 at 5:29 PM, Wang, Mary Y <mary.y.wang@boeing.com> wrote:

I'm using a psql -f command to reload the data from a dump file.  I noticed that some tables are not populated with any rows (I mean 0 rows), yet, if I manually insert a row (actually just copy an INSERT statement from that input file) in the interactive terminal, that row was added with no problem.  So my question "does psql -f quits inserting rows for a table when it detects there is an error in a statement?".  The impression that I got is that even though other rows might not have any errors, but psql -f seems just quits after it detects an error in a row.

See the psql man page, and search for "ON_ERROR_STOP". This controls
this behavior.

That's good to know. I ended up manually copy/paste INSERT statements for each table to another file and rerun the psql -f again. It was painful!

Mary
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Vick Khera
Sent: Monday, June 07, 2010 11:21 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Does psql -f quit insertion after an error in a statement has been detected?

On Sun, Jun 6, 2010 at 5:29 PM, Wang, Mary Y <mary.y.wang@boeing.com> wrote:

I'm using a psql -f command to reload the data from a dump file.  I noticed that some tables are not populated with any rows (I mean 0 rows), yet, if I manually insert a row (actually just copy an INSERT statement from that input file) in the interactive terminal, that row was added with no problem.  So my question "does psql -f quits inserting rows for a table when it detects there is an error in a statement?".  The impression that I got is that even though other rows might not have any errors, but psql -f seems just quits after it detects an error in a row.

See the psql man page, and search for "ON_ERROR_STOP". This controls this behavior.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 06/07/2010 02:10 PM, Wang, Mary Y wrote:

That's good to know. I ended up manually copy/paste INSERT statements for each table to another file and rerun the psql -f again. It was painful!

Mary

For future reference you could do something like:

pg_dump -a -t table_name -f table_name_data.sql

This will dump the data only from the table 'table_name' in the original
database to a file you can use to populate the table in the new database.

See here for the gory details:
http://www.postgresql.org/docs/8.4/interactive/app-pgdump.html

--
Adrian Klaver
adrian.klaver@gmail.com

Wang, Mary Y wrote:

Hi,

I'm using a psql -f command to reload the data from a dump file. I
noticed that some tables are not populated with any rows (I mean 0
rows), yet, if I manually insert a row (actually just copy an INSERT
statement from that input file) in the interactive terminal, that row
was added with no problem. So my question "does psql -f quits inserting
rows for a table when it detects there is an error in a statement?".
The impression that I got is that even though other rows might not have
any errors, but psql -f seems just quits after it detects an error in
a row.

Unless you set ON_ERROR_STOP in psql, psql will continue reading the
file and executing commands:

<term><varname>ON_ERROR_STOP</varname></term>
<listitem>
<para>
By default, if non-interactive scripts encounter an error, such
as a malformed <acronym>SQL</acronym> command or internal
meta-command, processing continues. This has been the
traditional behavior of <application>psql</application> but it
is sometimes not desirable. If this variable is set, script
processing will immediately terminate. If the script was called
from another script it will terminate in the same fashion. If
the outermost script was not called from an interactive
<application>psql</application> session but rather using the
<option>-f</option> option, <application>psql</application> will
return error code 3, to distinguish this case from fatal error
conditions (error code 1).

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +