Re: [GENERAL] INSERTing rows from external file
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERTing rows from external file
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@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.94988','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 ' 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERTing rows from external file
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.94988','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_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERTing rows from external file
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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.94988','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 ' and getting the above error. Thanks, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERTing rows from external file
>> -Original Message- >> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@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 >> (column_names) VALUES ;' that I thought I could read using psql from the command line. However, the syntax, 'psql < 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERTing rows from external file
On 08/16/2011 05:34 PM, Rich Shepard wrote: I have a file with 5500 rows formated as 'INSERT INTO (column_names) VALUES ;' that I thought I could read using psql from the command line. However, the syntax, 'psql < 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 USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERTing rows from external file
On Tue, Aug 16, 2011 at 2:34 PM, Rich Shepard wrote: > I have a file with 5500 rows formated as 'INSERT INTO > (column_names) VALUES ;' that I thought I could read using psql from > the command line. However, the syntax, 'psql < 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] INSERTing rows from external file
I have a file with 5500 rows formated as 'INSERT INTO (column_names) VALUES ;' that I thought I could read using psql from the command line. However, the syntax, 'psql < 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
