How extract data from pg_dump'ed files to tsv files?

From: Peng Yu <pengyu(dot)ut(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: How extract data from pg_dump'ed files to tsv files?
Date: 2017-10-03 17:05:24
Message-ID: CABrM6wnGob5mm=5GUP5z6vTm+zsuYLp3aFHZw6u_xhwsNT2Rng@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,

I'd like to extract the tables from the following dump to tsv files.
Does anybody know what command I should use to extract the tables into
tsv files? Thanks.

http://dgidb.org/data/data.sql

$ grep -- '^-- ' data.sql
-- PostgreSQL database dump
-- Dumped from database version 9.6.1
-- Dumped by pg_dump version 9.6.3
-- Data for Name: ar_internal_metadata; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: chembl_molecule_synonyms; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Name: chembl_molecule_synonyms_id_seq; Type: SEQUENCE SET; Schema:
public; Owner: ssiebert
-- Data for Name: chembl_molecules; Type: TABLE DATA; Schema: public;
Owner: ssiebert
-- Name: chembl_molecules_id_seq; Type: SEQUENCE SET; Schema: public;
Owner: ssiebert
-- Data for Name: delayed_jobs; Type: TABLE DATA; Schema: public;
Owner: ssiebert
-- Name: delayed_jobs_id_seq; Type: SEQUENCE SET; Schema: public;
Owner: ssiebert
-- Data for Name: drug_alias_blacklists; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Name: drug_alias_blacklists_id_seq; Type: SEQUENCE SET; Schema:
public; Owner: ssiebert
-- Data for Name: drugs; Type: TABLE DATA; Schema: public; Owner: ssiebert
-- Data for Name: drug_aliases; Type: TABLE DATA; Schema: public;
Owner: ssiebert
-- Data for Name: source_trust_levels; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: source_types; Type: TABLE DATA; Schema: public;
Owner: ssiebert
-- Data for Name: sources; Type: TABLE DATA; Schema: public; Owner: ssiebert
-- Data for Name: drug_aliases_sources; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: drug_attributes; Type: TABLE DATA; Schema: public;
Owner: ssiebert
-- Data for Name: drug_attributes_sources; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: drug_claims; Type: TABLE DATA; Schema: public; Owner: ssiebert
-- Data for Name: drug_claim_aliases; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: drug_claim_attributes; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: drug_claim_types; Type: TABLE DATA; Schema: public;
Owner: ssiebert
-- Data for Name: drug_claim_types_drug_claims; Type: TABLE DATA;
Schema: public; Owner: ssiebert
-- Data for Name: genes; Type: TABLE DATA; Schema: public; Owner: ssiebert
-- Data for Name: gene_aliases; Type: TABLE DATA; Schema: public;
Owner: ssiebert
-- Data for Name: gene_aliases_sources; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: gene_attributes; Type: TABLE DATA; Schema: public;
Owner: ssiebert
-- Data for Name: gene_attributes_sources; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: gene_claim_categories; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: gene_categories_genes; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: gene_claims; Type: TABLE DATA; Schema: public; Owner: ssiebert
-- Data for Name: gene_claim_aliases; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: gene_claim_attributes; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: gene_claim_categories_gene_claims; Type: TABLE DATA;
Schema: public; Owner: ssiebert
-- Data for Name: gene_gene_interaction_claims; Type: TABLE DATA;
Schema: public; Owner: ssiebert
-- Data for Name: gene_gene_interaction_claim_attributes; Type: TABLE
DATA; Schema: public; Owner: ssiebert
-- Data for Name: interactions; Type: TABLE DATA; Schema: public;
Owner: ssiebert
-- Data for Name: interaction_attributes; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: interaction_attributes_sources; Type: TABLE DATA;
Schema: public; Owner: ssiebert
-- Data for Name: interaction_claims; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: interaction_claim_attributes; Type: TABLE DATA;
Schema: public; Owner: ssiebert
-- Data for Name: interaction_claim_types; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: interaction_claim_types_interaction_claims; Type:
TABLE DATA; Schema: public; Owner: ssiebert
-- Data for Name: publications; Type: TABLE DATA; Schema: public;
Owner: ssiebert
-- Data for Name: interaction_claims_publications; Type: TABLE DATA;
Schema: public; Owner: ssiebert
-- Data for Name: interaction_types_interactions; Type: TABLE DATA;
Schema: public; Owner: ssiebert
-- Data for Name: interactions_publications; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- Data for Name: interactions_sources; Type: TABLE DATA; Schema:
public; Owner: ssiebert
-- PostgreSQL database dump complete

--
Regards,
Peng

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message David G. Johnston 2017-10-03 17:09:50 Re: How extract data from pg_dump'ed files to tsv files?
Previous Message Andreas Kretschmer 2017-10-01 19:02:49 Re: Creating database problems