CODEWITHSUNDEEP
javapostgresqlbatch-filejdbc
heisenberg
heisenberg

Reputation: 73

Batch Inserts in PostgreSQL using JDBC

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?

Upvotes: 2

Views: 1994

Answers (2)

jjanes
jjanes

Reputation: 44137

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.

Upvotes: 1

BevynQ
BevynQ

Reputation: 8259

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);

Upvotes: 1

Related Questions