CODEWITHSUNDEEP
postgresql
jcm
jcm

Reputation: 5659

Loading data into Postgres from file

I have the following two files, the first is a data file, where the first column is the unique identifier of the row and the second which represents a dictionary of the fields following the id field.

> cat data
1,NULL,37,M,NULL
2,NULL,25,F,NULL
3,NULL,42,F,NULL
4,NULL,18,M,NULL

> cat .dictionary
0,balance|double
1,age|int
2,gender|string
3,state|string

The size of the data file could be very large.

How can I get this data into Postgres? Should I write my own custom data loader program which parses the files and creates a .sql file which can then be used? Or connect directly to the database via JDBC and load it in that way?

Upvotes: 0

Views: 886

Answers (2)

khampson
khampson

Reputation: 15356

As a variant of @RADAR's answer, after creating the table, the \copy command in psql can be used. It wraps COPY, but offers some benefits in terms of bringing the required permissions to the local file system (i.e. your machine) instead of the server's, where you may not necessarily have the required permissions.

It can potentially be slower than a server-side COPY, since it's not pure server-side, but it's the method I generally use when I need to do this sort of thing, and I find the performance to generally be good.

As a related aside, I find the biggest cause of performance issues when copying data like this are indexes and constraints on the table. If you're copying a table that has them already, and it's a lot of data, I would recommend dropping them, doing the copy, and then re-applying them afterward. For a new table, wait until after the copy to create any indexes or constraints. The difference can be orders of magnitude.

For more details about \copy, see the psql doc.

Upvotes: 0

radar
radar

Reputation: 13425

Create the table to hold this data and then use COPY

create table Table1
(
 balance double precision,
 age     int,
 gender  varchar(10),
 state   varchar(10)
);

COPY Table1 FROM '/home/abc/data.txt' DELIMITER ',' CSV;

Upvotes: 1

Related Questions