importing a text file using pgAdmin

Question

I have just downloaded pgAdmin 1.14.3 in an effort to import, query, and manage large textfiles. These textfiles are either quote comma quote delimited or tab delimited (they come as quote comma quote and I edited many for use with another software). While version 1.16 allows an import function, it has not been released yet and I am wondering how to import data into a newly created table using pgAdmin.

The text files range from 12MB to 2GB, so I'm looking for a comprehensive solution that would not involve importing row by row. I tried this with phppgadmin, but ran into file size limitations embedded in the php.ini file (separate post) and am trying this as a possible workaround. I'm a little new to SQL, so not really sure of all the commands possible at my fingertips. Any helps is appreciated - thanks!

Answer 1

You can issue a COPY statement, like this:

COPY table_name (column_name)
FROM 'd:\test.sql';

Query returned successfully: 6 rows affected, 31 ms execution time.

See the documentation here: http://www.postgresql.org/docs/9.1/static/sql-copy.html

Note that I did not test this in PgAdmin for large files, but using psql I have never seen a case where the file had been too big for COPY.