PostgreSQL bulk load

Introduction

Scenarios such as testing or baseline deployments require populating database tables with seed data. In PostgreSQL there are a number of approaches to load data including use of SQL scripts or database dumps, however there is a simple alternative which is to load from a CSV file. This approach is most likely to be used where non-technical people are involved in preparing functional tests.

Database Table

For the purpose of this trivial example, a table containing bids registered by traders is being populated. This table has no foreign key relationships. Create the bid table with the following DDL.

CREATE TABLE bid
(
  bid_id bigint NOT NULL,
  trader_id character varying(10) NOT NULL,
  bid numeric,
  CONSTRAINT bid_pkey PRIMARY KEY (bid_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE bid OWNER TO postgres;

CSV Data File

Create a CSV file containing the bid data with the first row containing column headers.

ID,Trader,Bid
1,AA14124,0.54615104
2,AA14125,0.594037188
3,AA14126,0.884697673
4,AA14127,0.55843941
5,AA14128,0.90518401
6,AA14129,0.977280122
7,AA14130,0.277475746
8,AA14131,0.559351948
9,AA14132,0.261013363
10,AA14133,0.236723671
etc...
etc...

Process CSV Data File

Assuming bid-data.csv exists in the /Temp directory, load the CSV into PostgreSQL by executing the following command in the pgadmin query tool:

copy bid from
'/Temp/bid-data.csv'
DELIMITERS ',' CSV HEADER

If the load is successful you will see a message similar to the following displaying the number of rows processed:

Query returned successfully: 2500 rows affected, 182 ms execution time.