Insert `tsv` files into postgresql db

database postgresql database-design insert tsv

You want something like this:

COPY ratings FROM 'C:/Users/testUser/Desktop/TSV/ratings.list.tsv' DELIMITER E'\t';

The E'\t' is required, otherwise you'll get an error like this:

ERROR: DELIMITER for COPY must be a single one-byte character

If the columns in your TSV don't line up perfectly with your table, you can also define the mapping by doing the following:

COPY ratings (column_1, column_2, ... column_n)  FROM 'C:/Users/testUser/Desktop/TSV/ratings.list.tsv'  DELIMITER E'\t';

database postgresql database-design insert tsv

For tab separated values, you can use COPY:

Depending on the exact format of your file, it could be something like:

COPY ratings FROM 'C:/Users/testUser/Desktop/TSV/ratings.list.tsv' DELIMITER '\t'

database postgresql database-design insert tsv

I'm able to do this with csvsql from csvkit.

To read a TSV (or CSV) and create/insert it to a table, my command line script looks like this:

csvsql --insert input.tsv  --table table_t --tabs --no-constraints --db postgresql://user:passwd@localhost/mydb

CodeHunter