Insert data from a tsv file from web in postgres table

Question

This question has been has asked but I am unable to find proper solution to that. I would like to COPY data from the columns present in a tsv file available at a web link and insert it into an already created table under a schema.

I have used the query below but I am unable to get any output.

COPY schema.table FROM PROGRAM 'curl "ftp://ftp.ebi.ac.uk/pub/databases/intact/current/various/mutations.tsv"'

COPY schema.table FROM PROGRAM 'wget -q -O - "$@" "ftp://ftp.ebi.ac.uk/pub/databases/intact/current/various/mutations.tsv HEADER TSV DELIMITER '\t'

Any suggestions here would be really helpful.

"unable to get any output." What output were you expecting to get? Is it just because you didn't end the line with a ';'? — jjanes
– jjanes, Commented Jan 28, 2020 at 16:56

Jim Jones · Accepted Answer · 2020-01-28 19:35:06Z

1

I consider a bad practice to download files into the database server - not to mention executing external software to do so. In other words, don't let your database server have access to the internet unless it's really necessary.

That being said, I'd recommend you to download this file of yours in your client and upload it using psql. It will work no matter where you download the file and you won't have to deal with possible security breaches or read/write permissions in the server.

Downloading and importing a TSV file in two steps:

$ wget -O mutations.tsv "ftp://ftp.ebi.ac.uk/pub/databases/intact/current/various/mutations.tsv"
$ cat mutations.tsv | psql -d your_db -c "COPY your_table FROM STDIN DELIMITER E'\t'"

edited Jan 28, 2020 at 19:35

answered Jan 28, 2020 at 14:24

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurenz Albe Over a year ago

Interesting. Why is it worse to use curl on the server than on the client? If you don't trust root, you are lost anyway.

Jim Jones Over a year ago

I'm just not very comfortable letting my server access the internet to download a file. Imho, database systems should be used just for storage and not be able to do such a thing

Laurenz Albe · Accepted Answer · 2020-01-28 14:25:44Z

0

You should use the CSV format and the tab character as a separator:

COPY atable
FROM PROGRAM 'curl "ftp://ftp.ebi.ac.uk/pub/databases/intact/current/various/mutations.tsv"'
   (FORMAT 'csv', HEADER, DELIMITER E'\t');

answered Jan 28, 2020 at 14:25

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

2 Comments

rshar Over a year ago

Above query runs for sometime and then exit with this error:

[Code: 0, SQL State: 38000]  ERROR: program "curl "ftp://ftp.ebi.ac.uk/pub/databases/intact/current/various/mutations.tsv"" failed   Detail: child process exited with exit code 7

Laurenz Albe Over a year ago

According to the documentation of curl, that is "Failed to connect to host." Different problem.

Collectives™ on Stack Overflow

Insert data from a tsv file from web in postgres table

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related