CODEWITHSUNDEEP
postgresqlcsvinsert
rshar
rshar

Reputation: 1477

Insert data from a tsv file from web in postgres table

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.

Upvotes: 1

Views: 909

Answers (2)

Jim Jones
Jim Jones

Reputation: 19623

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

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246523

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

Upvotes: 0

Related Questions