Обсуждение: 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. 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
On Tue, Aug 16, 2011 at 2:34 PM, Rich Shepard <rshepard@appl-ecosys.com> 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
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
>> -----Original Message----- >> From: pgsql-general-owner@postgresql.org [mailto: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.
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
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
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
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto: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.
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