FAQ
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?
Thanks
Mary

Search Discussions

  • John R Pierce at Jun 6, 2010 at 9:59 pm

    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.
  • Vivek Khera at Jun 7, 2010 at 6:21 pm

    On Sun, Jun 6, 2010 at 5:29 PM, Wang, Mary Y 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.
  • Wang, Mary Y at Jun 7, 2010 at 9:10 pm
    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 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 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
  • Adrian Klaver at Jun 7, 2010 at 9:19 pm

    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
  • Bruce Momjian at Jul 3, 2010 at 4:31 pm

    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. +

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJun 6, '10 at 9:29p
activeJul 3, '10 at 4:31p
posts6
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase