Suggestion to reduce COPY command output to csv file

From: postggen2020 s <postggen2020(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Subject: Suggestion to reduce COPY command output to csv file
Date: 2020-03-04 18:38:55
Message-ID: CAJ=omAhsJ89HqBtLBhE33RjvQ6ykbZjtM18igf=T-42m-nveBQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

Hi Team,
Thanks a lot all of you, for providing support peoples like me.
Could you please provide a suggestion on COPY command.

Environment:
DB Version:9.5.15
postgis:
Table contain GIS data.

I have a table with GIS data, its around 300MB and 2Lacks+ records. I want
to export all the records to the CSV file. I can able to export the table
data into a CSV file, but the exported CSV file size around 162MB.
While I am trying to opening the excel it is taking a long time and also
while applying vlookup excel is getting hang.

I am using below command :
\copy cities TO '/var/lib/pgsql/cities.csv' (DELIMITER ',', FORMAT 'csv',
HEADER)

I am trying to achieve the following :
1. The exported CSV file should have within 10MB.
2. The excel file should open quickly and able to perform vlookups without
any problems(like hang).

Could you please provide suggestions on below queries:
1. what I am trying to achieve, is the right assumption?.
2. Can we use this command to use for the above use case?.
3. Is there any tool/extension available for the above use case.?

Your inputs are highly appreciated.

Regards,
Postgadm.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Adrian Klaver 2020-03-04 18:46:45 Re: Suggestion to reduce COPY command output to csv file
Previous Message Ishan Joshi 2020-03-04 11:31:53 RE: Postgres server crash and not starting

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2020-03-04 18:46:45 Re: Suggestion to reduce COPY command output to csv file
Previous Message Klaus Darilion 2020-03-04 17:33:50 Re: Logical replication lag in seconds