Batch Inserts in PostgreSQL using JDBC

Question

I want to insert a file into postgresql using JDBC. I know the below command to use but the file downloaded is not parsed with a delimiter("|" or ",")

 Filereader fr = new FileReader("mesowest.out.txt"); cm.copyIn("COPY tablename FROM STDIN WITH DELIMITER '|'", fr);

My file looks like this :

ABAUT 20131011/0300 8.00 37.84 -109.46 3453.00 21.47 8.33 241.90 ALTU1 20131011/0300 8.00 37.44 -112.48 2146.00 -9999.00 -9999.00 -9999.00 BDGER 20131011/0300 8.00 39.34 -108.94 1529.00 43.40 0.34 271.30 BULLF 20131011/0300 8.00 37.52 -110.73 1128.00 56.43 8.07 197.50 CAIUT 20131011/0300 8.00 38.35 -110.95 1381.00 54.88 8.24 250.00 CCD 20131011/0300 8.00 40.69 -111.59 2743.00 27.94 8.68 285.40

So my question is .. is it necessary to append delimiters to this file to push it into the database using jdbc?

BevynQ · Accepted Answer · 2013-10-14 00:29:32Z

from postgres documentation

delimiter : The single character that separates columns within each row (line) of the file. The default is a tab character in text mode, a comma in CSV mode.

It looks like your data is tab delimeted. So using the default should work.

Filereader fr = new FileReader("mesowest.out.txt"); cm.copyIn("COPY tablename FROM STDIN", fr);

jjanes · Accepted Answer · 2013-10-14 16:12:52Z

You need to transform your file in some way, yes.

It looks it is currently either delimited by a variable number of spaces, or it has fixed width fields. The difference being, what would happen if 2146.00 were changed to 312146.00? Would it run into the previous field like "-112.48312146.00", like fixed width would, or would you add a space anyway even though that would break the column alignment?

I don't believe either of those are directly supported by COPY, so some transformation is necessary. Also, -9999.00 looks like a magic value that should probably be converted to NULL.

Collectives™ on Stack Overflow

Batch Inserts in PostgreSQL using JDBC

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related