Shell Programming and Scripting

hi all,
a have problem to load *.txt to postgres

my database:

Code:

id_list_ip (nextval)
list_ip (varchar)

txt file (list_ip.txt)

Code:

hasilping_10.8.248.1
hasilping_119.110.112.226
hasilping_119.110.125.33
hasilping_125.22.1.25
hasilping_192.168.2.1
hasilping_202.73.96.70

script (list_ip.sh)

Code:

#!/bin/sh

cd /usr/lib/postgresql/8.4/bin
./psql -U postgres -d NMS << EOF
COPY "list_ip" ("id_list_ip","list_ip") FROM '/opt/Tampung1/list_ip.txt';

how to insert the txt file to postgres with id_list_ip auto-increment?

thanks in advance

I think you need a staging table, because you are inserting one column and autoincrementing the other, which is not copy-friendly. Copy to the staging table, and then insert to the list_ip column, and the other column will autoincrement. Remember to clear your staging table on each run.

OK thank DGPickett ...I'll try it..

With RDBMS, the big bottleneck is getting the data in; moving it internally is relatively fast and simple. Some RDBMS have ways to map a text file as an external table, which is very fast, or to stream data into the insert statement using a SP and UDFs (the sp is like a table, and inside, it has udf calls to get the right field or substring of the current record for each virtual column, and a where clause udf call does the read and eof test). I like the streaming, using pipe.