Import CSV file to PostgreSQL

In this session, We will give an example how to import csv file into postgresql table, first, create a new table called nyc_census_blocks_test

CREATE TABLE IF NOT EXISTS public.nyc_census_blocks_test
(
    gid integer NOT NULL DEFAULT nextval('nyc_census_blocks_gid_seq'::regclass),
    blkid character varying(15) COLLATE pg_catalog."default",
    popn_total double precision,
    popn_white double precision,
    popn_black double precision,
    popn_nativ double precision,
    popn_asian double precision,
    popn_other double precision,
    boroname character varying(32) COLLATE pg_catalog."default",
    geom geometry(MultiPolygon,26918),
    CONSTRAINT nyc_census_blocks_pkey PRIMARY KEY (gid)
);

Second, prepare the csv file, my path of csv file is : /var/lib/pgsql/nyc_census_data.csv

To import CSV file into the nyc_census_blocks table, you use COPY statement as follows:

COPY nyc_census_blocks_test 
FROM '/var/lib/pgsql/nyc_census_data.csv' 
DELIMITER ',' 
CSV HEADER;

PostgreSQL gives back the following message:

COPY 38794

Now let’s check nyc_census_blocks_test

SELECT count(*) FROM nyc_census_blocks_test;

It will give the same number as above which is 38794.

Let’s dive into the COPY statement in more detail.

First, you specify the table with column names after the COPY keyword. The order of the columns must be the same as the ones in the CSV file. In case the CSV file contains all columns of the table, you don’t need to specify them explicitly, for example:

COPY table_name
FROM 'C:\some_path\sample_data.csv' 
DELIMITER ',' 
CSV HEADER;

Second, you put the CSV file path after the FROM keyword. Because CSV file format is used, you need to specify DELIMITER as well as CSV clauses.

Third, specify the HEADER keyword to indicate that the CSV file contains a header. When the COPY command imports data, it ignores the header of the file.

Notice that the file must be read directly by the PostgreSQL server, not by the client application. Therefore, it must be accessible by the PostgreSQL server machine. Also, you need to have superuser access in order to execute the COPY statement successfully.