Re: 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 <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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us