Importing a TSV File into Postgres

When it comes to loading up large amounts of data into a database all at once, the CSV file is one of the most common ways used. CSV files use commas to separate between values listed and because of this can be problematic if your data has commas in it. Enter TSV files, which are similar to CSV files but use tabs for separation, or ‘\t’. There are other types of CSV files that use other separation symbols but TSV files are one of the preferred types when running tasks with databases due to speed advantages.

Let’s say you want to import millions of rows of data into a table on Postgres. These rows are generated into a TSV file and is ready to be copied into your database. Here’s how we do it.

Create a table first. Here’s an example schema for a table of listings. Not required but as a good practice to follow, set a column as a primary key.

CREATE TABLE LISTINGS(
   ID             INT     NOT NULL,
   TITLE          TEXT    NOT NULL,
   DESCRIPTION    TEXT    NOT NULL,
   PHOTOS         TEXT,   
CONSTRAINT listings_pkey PRIMARY KEY (id));

Next create an index, that may help improve query times. An implicit index is created when you set a column as to NOT NULL and PRIMARY KEY but you can create one explicitly as follows.

CREATE INDEX idx_id
ON listings (id);

Finally, you can use the COPY command to import the TSV into the database. It’s important to make sure that the order of the columns in the TSV file matches that of the database. Also, the TSV file’s first row should contain the headers when using this command as follows.

COPY listings 
FROM '/home/ec2-user/list.tsv'
DELIMITER E'\t'
CSV HEADER;

You may have noticed the E before the ‘\t’ in the DELIMITER command. This is necessary as it tells Postgres that there may be escaped characters. This is not usually necessary for importing comma separated values.

When the command is executed, Postgres will import the rows. The more rows there are, the longer it will take so go grab a snack! You will see the following to indicate that the import has been completed.

COPY 10000000

No go ahead and run queries on your database! Thanks for reading!