Loading data from files

Having already blogged about loading data from flat files to MySQL, it's time to post a similar case for PostgreSQL, as the manual seems to lack a real life example ...

First of all the table to be loaded

CREATE TABLE target
(
code character(3) NOT NULL,
"name" character varying(50) NOT NULL,
amount numeric,
CONSTRAINT pk_1 PRIMARY KEY (code)
)
WITH (OIDS=FALSE);

as you can see one of the column names is a reserved word! Bad practice, but it's here to add some spice.

Now the data

code;name;amount
12A;Pippo;12.5
13B;Topolino;45
23D;Pluto;NULL

as you can see it's delimited by ";", has an header row and contains NULLs, which we want to preserve in our target table.
This file is named 2beloaded.csv and it's placed in c:\

After logging in to PostgreSQL and creating the table with the statement provided above it's time to load it

copy
--target table with columns listed
 target(code, "name", amount)
from
--source file
 'C:/2beloaded.csv'
with
--list of options
      csv
      --switches on csv mode
      header
      --ignores first line as an header line
 delimiter ';'
      --sets the delimiter
 null as 'NULL'
      --preserves nulls by telling the database what represents a NULL

Quite nice, isn't it?
Hope this helps.