Flogged Horse Flogged Horse - 1 year ago 80
SQL Question

Copying postgres data base with links / ref ids

Trying to extract a csv of a db table

COPY sale_order TO '/tmp/salesorder.csv' DELIMITER ',' CSV HEADER;

I get..

  • id - partner_id

  • 1 - 45

  • 2 - 55

"partner_id" is references another table "contacts" as id, where I want "name"

Can I ref the partner_id to output

  • id - name

  • 1 -Fred Blogs

  • 2 -John Smith

Answer Source

It is possible to copy from a query:

copy (
    select so.id, p.name
        sale_order so
        inner join
        partner p on p.id = so.partner_id
) to '/tmp/salesorder.csv' delimiter ',' csv header;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download