0

I have two tables in PostgreSQL and I want to join them with where condition. After I joined them, I want to convert to CSV file using copy function. Is it possible to join and generate the CSV file using COPY function? Or is it have another method?

chemat92
  • 501
  • 2
  • 7
  • 16
  • This is possible, but you did not show us the query or table structure, so we can't help you. – Tim Biegeleisen Nov 22 '16 at 04:57
  • `copy from (select col1, col2, from t1 join t2 on ... where ...) to ...`. See the examples in the manual: https://www.postgresql.org/docs/current/static/sql-copy.html#AEN77768 –  Nov 22 '16 at 07:35
  • This has the answer that you need http://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file – Deepak Puthraya Nov 22 '16 at 14:08

1 Answers1

2

Yes, it is possible and very easy.

Let's suppose we have two tables, merchant_position and merchant_timeline. In (mp_sc_id, mp_merchant_id, mp_rank, mp_tier, mp_updated_at), all these fields are from the merchant_position table but (mt_name) is in merchant_timeline table and foreign key is mt_id and mp_merchant_id.

\copy (select mp_sc_id, mp_merchant_id, mp_rank, mp_tier, mp_updated_at, mt_name from merchant_position INNER JOIN merchant_timeline ON mt_id = mp_merchant_id) TO '/Users/Desktop/mercahnt_rank.csv' DELIMITER ',' CSV HEADER
Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Mohit Sharma
  • 21
  • 2