Re: INSERTing rows from external file

>> -----Original Message-----
>> From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Rich Shepard
>> Sent: Tuesday, August 16, 2011 5:34 PM
>> To: pgsql-general(at)postgresql(dot)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.