Insert `tsv` files into postgresql db

Question

I have several files which are saved as tsv. I want to insert them into a postgresql db, to analyze them with sql.

However, my problem is how to INSERT this tsv files into postgresql 9.2 under windows 7?

I appreciate your reply!

PS.: I have created the table with the right values like:

CREATE TABLE ratings (distribution VARCHAR, votes VARCHAR, rank FLOAT, title VARCHAR);

the file is in the directory:

C:/Users/testUser/Desktop/TSV/ratings.list.tsv

tsv as in text search vectors, or did you mean csv as in comma separated values? If the latter, use COPY. — Denis de Bernardy, Dec 08 '13 at 16:28
@Denis Thx for your answer. By tsv I mean as tab seperated values. Could you show an example for how to use COPY in an sql statement. — Carol.Kar, Dec 08 '13 at 16:33

score 37 · Answer 1 · answered Jun 28 '16 at 20:49

37

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';

answered Jun 28 '16 at 20:49

Ryan Atallah

2,977
26
34

if you add CSV to the end, you can also use the HEADER flag – kdazzle Nov 10 '16 at 20:31
What if the .tsv file specifies a value for a UNIQUE column which already exists in the table? – Gershom Maes Mar 17 '21 at 03:39

score 25 · Accepted Answer · answered Dec 08 '13 at 17:03

25

For tab separated values, you can use COPY:

http://www.postgresql.org/docs/current/static/sql-copy.html

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'

answered Dec 08 '13 at 17:03

Denis de Bernardy

75,850
13
131
154

9

Thx for your answer. I immediately tried it, but got: `ERROR: DELIMITER for COPY must be a single one-byte character` – Carol.Kar Dec 08 '13 at 17:06
5

ok solved it I had to type a tab instead using `\tab`. Thx for your help! – Carol.Kar Dec 08 '13 at 17:11
2

\t - tab is default, so in this case, you don't need use DELIMITER – Pavel Stehule Dec 08 '13 at 18:27
try double-quotes instead of single – 111 Jul 18 '15 at 17:48
How do you skip the first line since it's a comment? – Superdooperhero Mar 07 '20 at 07:13
3

I've tried everything mentioned above, but what worked for me was adding `(FORMAT CSV, DELIMITER E'\t', HEADER true)` after the 'FROM' clause. Hope this helps anyone. – a-sie Jun 24 '20 at 22:56
What if the .tsv file specifies a value for a `UNIQUE` column which already exists in the table? – Gershom Maes Mar 17 '21 at 03:39

score 2 · Answer 3 · answered Jul 03 '16 at 21:19

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

score 0 · Answer 4 · answered Jul 01 '15 at 20:34

0

Sad to say, but the easiest way is to convert the TSV to a CSV. Most of the built in Postgres import utilities to do things like converting empty strings to nulls, skipping headers, etc are Only for CSV.

See this simple 6 line Python answer on SO. I use it and then CSV loaded like normal without a problem in Postgres after trying for and hour to load a TSV.

answered Jul 01 '15 at 20:34

Joseph Lust

19,340
7
85
83

1

Thanks for the download vote. For various use cases the upvoted answer won't work, hence the above suggestion. ;) – Joseph Lust Dec 04 '15 at 04:25
4

I beg to differ; the default postgresql COPY command defaults to TSV, and I see no difference in support for CSV and TSV. Specifically, both of the commands you mention are present in the configuration for TSV file reading, per this page: https://www.postgresql.org/docs/9.6/static/sql-copy.html – John Clements Mar 16 '18 at 22:10

Insert `tsv` files into postgresql db

4 Answers4