Wednesday, 24 June 2009

Insert Large Amount Of Rows Into PostgreSQL

Inserting large amount of data into postgres database may take a lot of time, if you use INSERT command. Consider better COPY command, though it is not so flexible.
COPY my_table_name(int_column, string_column, bytea_column) from stdin;
1    THISisSTRING    \\001\\017\\044
2    THISisSTRING2   \\001\\017\\062
3    THISisSTRING3   \\001\\017\\102

The default delimiter is tab (\t).

To acquire the right format in 1 particular case, you can fill database with some values using INSERT command and dump database with pg_dump. The resulting file will contain the right COPY syntax.