I have a file with 5500 rows formated as 'INSERT INTO <table>
(column_names) VALUES <values>;' that I thought I could read using psql from
the command line. However, the syntax, 'psql <database_name> < filename.sql'
throws an error at the beginning of the first INSERT statement.

In the INSERT manual page I see no example or other insight on adding a
large number of rows to a table from an external .sql file. Please point me
to the reference on how to do this.

Rich

Search Discussions

  • Chris Travers at Aug 16, 2011 at 9:49 pm

    On Tue, Aug 16, 2011 at 2:34 PM, Rich Shepard wrote:
    I have a file with 5500 rows formated as 'INSERT INTO <table>
    (column_names) VALUES <values>;' that I thought I could read using psql from
    the command line. However, the syntax, 'psql <database_name> < filename.sql'
    throws an error at the beginning of the first INSERT statement.
    What kind of error?
    In the INSERT manual page I see no example or other insight on adding a
    large number of rows to a table from an external .sql file. Please point me
    to the reference on how to do this.
    Do you get the same error by running psql interactively and typing \i
    filename.sql?

    Best Wishes,
    Chris Travers
  • Rich Shepard at Aug 16, 2011 at 10:14 pm

    On Tue, 16 Aug 2011, Chris Travers wrote:

    What kind of error?
    Chris,

    Here's the full statement for the last row:

    psql:chem_too.sql:5517: ERROR: invalid input syntax for type boolean: ""
    LINE 1: ...NS','1996-11-21','Potassium','0.949999988','mg/L','','','','...
    ^
    The column is NULLable and if there's no value a NULL should be entered.
    Do you get the same error by running psql interactively and typing \i
    filename.sql?
    Er, thanks for the pointer. I didn't read the psql man page first. Now I'm
    using 'psql -f <filename> <database_name>' and getting the above error.

    Thanks,

    Rich
  • Scott Ribe at Aug 16, 2011 at 10:23 pm

    On Aug 16, 2011, at 4:13 PM, Rich Shepard wrote:

    Here's the full statement for the last row:

    psql:chem_too.sql:5517: ERROR: invalid input syntax for type boolean: ""
    LINE 1: ...NS','1996-11-21','Potassium','0.949999988','mg/L','','','','...
    ^
    The column is NULLable and if there's no value a NULL should be entered.
    An empty string is not null.

    --
    Scott Ribe
    scott_ribe@elevated-dev.com
    http://www.elevated-dev.com/
    (303) 722-0567 voice
  • David Johnston at Aug 16, 2011 at 10:28 pm
    -----Original Message-----
    From: pgsql-general-owner@postgresql.org
    On Behalf Of Rich Shepard
    Sent: Tuesday, August 16, 2011 6:14 PM
    To: pgsql-general@postgresql.org
    Subject: Re: [GENERAL] INSERTing rows from external file
    On Tue, 16 Aug 2011, Chris Travers wrote:

    What kind of error?
    Chris,

    Here's the full statement for the last row:

    psql:chem_too.sql:5517: ERROR: invalid input syntax for type boolean: ""
    LINE 1: ...NS','1996-11-21','Potassium','0.949999988','mg/L','','','','...
    ^
    The column is NULLable and if there's no value a NULL should be entered.
    Do you get the same error by running psql interactively and typing \i
    filename.sql?
    Er, thanks for the pointer. I didn't read the psql man page first. Now
    I'm using 'psql -f <filename> <database_name>' and getting the above error.

    Thanks,

    ----------------------------------------------------------------------------
    ---------

    Since the error references a specific row of data you should provide that as
    well.

    Your INSERT statement is syntactically incorrect; the error has nothing to
    do with PSQL other than the fact that PSQL is reporting the error to you.

    Odds are you are wrapping your Boolean input with single quotes and the
    empty string is not valid input for a Boolean. Because of the quotes the
    system will not use NULL since the input data is not missing but instead it
    has the empty-string as a value.

    David J.
  • Rich Shepard at Aug 16, 2011 at 10:48 pm

    On Tue, 16 Aug 2011, David Johnston wrote:

    Your INSERT statement is syntactically incorrect; the error has nothing to
    do with PSQL other than the fact that PSQL is reporting the error to you.
    David,

    I see that now.
    Odds are you are wrapping your Boolean input with single quotes and the
    empty string is not valid input for a Boolean. Because of the quotes the
    system will not use NULL since the input data is not missing but instead
    it has the empty-string as a value.
    Yep. Gotta' correct them all so the proper columns are listed and those
    with no values are not.

    Thanks for pointing out the obvious.

    Rich
  • Greg Smith at Aug 16, 2011 at 9:52 pm

    On 08/16/2011 05:34 PM, Rich Shepard wrote:
    I have a file with 5500 rows formated as 'INSERT INTO <table>
    (column_names) VALUES <values>;' that I thought I could read using
    psql from
    the command line. However, the syntax, 'psql <database_name> <
    filename.sql'
    throws an error at the beginning of the first INSERT statement.
    Sounds like a problem with your file. Messing up CR/LF characters when
    moving things between Windows and UNIX systems is a popular one. Proof
    it works:

    $ psql -c "create table t(i integer)"
    CREATE TABLE
    $ cat test.sql
    INSERT INTO t(i) VALUES (1);
    INSERT INTO t(i) VALUES (2);
    INSERT INTO t(i) VALUES (3);
    INSERT INTO t(i) VALUES (4);
    $ psql < test.sql
    INSERT 0 1
    INSERT 0 1
    INSERT 0 1
    INSERT 0 1

    You might also try this:

    psql -ef filename.sql

    Which will show you the command that's being executed interleaved with
    the output; that can be helpful for spotting what's wrong with your
    input file.

    P.S. The fast way to get lots of data into PostgreSQL is to use COPY,
    not a series of INSERT statements. You may want to turn off
    synchronous_commit to get good performance when doing lots of INSERTs.

    --
    Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
    PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
  • Rich Shepard at Aug 16, 2011 at 10:18 pm

    On Tue, 16 Aug 2011, Greg Smith wrote:

    Sounds like a problem with your file. Messing up CR/LF characters when
    moving things between Windows and UNIX systems is a popular one. Proof it
    works:
    Greg,

    Excel file imported into LibreOffice and converted to .ods. Columns marked
    and saved as .csv. Emacs does not display the 'DOS' indicator of CR/LF
    instead of the UNIX \n because all work was done with linux applications.

    Using the psql '-f' option worked ... up to a point when psql pointed out
    to me that I had neglected to put a terminating semicolon on the end of each
    line. Mea culpa!

    Now I get an error on a boolean column. See my response to Chris with
    details.

    Thanks,

    Rich
  • David Johnston at Aug 16, 2011 at 9:53 pm

    -----Original Message-----
    From: pgsql-general-owner@postgresql.org
    On Behalf Of Rich Shepard
    Sent: Tuesday, August 16, 2011 5:34 PM
    To: pgsql-general@postgresql.org
    Subject: [GENERAL] INSERTing rows from external file

    I have a file with 5500 rows formated as 'INSERT INTO <table>
    (column_names) VALUES <values>;' that I thought I could read using psql
    from the command line. However, the syntax, 'psql <database_name> <
    filename.sql'

    throws an error at the beginning of the first INSERT statement.
    Prove It...

    I do not use psql in this manner but what you are trying to do should work.
    One thing that it may behoove you to do is clean up the file so that the

    INSERT INTO table (columns) VALUES

    Part only appears once. Then, for each "VALUES (....);" line you should
    replace the semi-colon with a comma (except the final one).

    Thus:
    INSERT INTO table (col) VALUES (1);
    INSERT INTO table (col) VALUES (2);
    INSERT INTO table (col) VALUES (3);

    Becomes:
    INSERT INTO table (col) VALUES (1),
    (2),
    (3);

    This is considerably faster to execute. 5500 rows should be OK to do in a
    single statement but anything beyond should probably result in a separate
    INSERT being added (and maybe a COMMIT).

    David J.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedAug 16, '11 at 9:34p
activeAug 16, '11 at 10:48p
posts9
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase