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?
Asked
Active
Viewed 841 times
0
-
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 – a_horse_with_no_name 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 Answers
1
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
- 43,040
- 110
- 45
- 72
Mohit Sharma
- 11
- 1