Обсуждение: Extracting data into .csv format
Using PostgreSQL 9.3 on Linux I'm trying to extract data into a comma delimited format and not having much success...seems to act totally different fromOracle. Does anyone have any suggestions? Thank you in advance. Denise
Вложения
Please read this: http://www.postgresql.org/docs/9.4/static/sql-copy.html -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Ferrell, Denise D CTR NSWCDD,H11 Sent: Wednesday, February 17, 2016 10:19 PM To: pgsql-admin@postgresql.org Subject: [ADMIN] Extracting data into .csv format Using PostgreSQL 9.3 on Linux I'm trying to extract data into a comma delimited format and not having much success...seems to act totally different fromOracle. Does anyone have any suggestions? Thank you in advance. Denise
The "Copy" statement (and \copy for local access COPY ( select * from (......... ) TO ' || '''' || filname || '''' || ' with csv header' STeve -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Ferrell, Denise D CTR NSWCDD,H11 Sent: Wednesday, February 17, 2016 11:49 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Extracting data into .csv format Using PostgreSQL 9.3 on Linux I'm trying to extract data into a comma delimited format and not having much success...seems to act totally different fromOracle. Does anyone have any suggestions? Thank you in advance. Denise THIS MESSAGE (AND ALL ATTACHMENTS) IS INTENDED FOR THE USE OF THE PERSON OR ENTITY TO WHOM IT IS ADDRESSED AND MAY CONTAININFORMATION THAT IS PRIVILEGED, CONFIDENTIAL AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW. If you are not the intendedrecipient, your use of this message for any purpose is strictly prohibited. If you have received this communicationin error, please delete the message without making any copies and notify the sender so that we may correct ourrecords. Thank you.
On Wed, Feb 17, 2016 at 9:49 AM, Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil> wrote:
Using PostgreSQL 9.3 on Linux
I'm trying to extract data into a comma delimited format and not having much success...seems to act totally different from Oracle. Does anyone have any suggestions?
In the documentation lookup the psql "\copy" meta-command along with the SQL COPY command.
David J.
On 2016-02-17 11:49 AM, Ferrell, Denise D CTR NSWCDD, H11 wrote:
Have you tried something like i.e.:Using PostgreSQL 9.3 on Linux I'm trying to extract data into a comma delimited format and not having much success...seems to act totally different from Oracle. Does anyone have any suggestions? Thank you in advance. Denise
psql -c "COPY ( QUERY ) TO STDOUT WITH CSV HEADER " > CSV_FILE.csv
Should do the trick (assuming it's basic output you're after).
Regards,
Ziggy
On Wed, Feb 17, 2016 at 10:19 PM, Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil> wrote:
Using PostgreSQL 9.3 on Linux
I'm trying to extract data into a comma delimited format and not having much success...seems to act totally different from Oracle. Does anyone have any suggestions?
Thank you in advance.
Denise
Hi Denise,
Please read below official documentation of copy command:
Or check below tutorial
Hope this will solve your problem.
Hi, Le 17/02/2016 17:49, Ferrell, Denise D CTR NSWCDD, H11 a écrit : > Using PostgreSQL 9.3 on Linux > > I'm trying to extract data into a comma delimited format and not having >much success...seems to act totally different from Oracle. Does anyone >have any suggestions? Just in case it helps you, I wrote a pair of very simple scripts, but they have eased my life a lot: * sql2csv: https://github.com/pierrechtux/geolllibre/blob/master/sql2csv sql2csv takes an SQL query as an argument, and spits out the result in CSV. Just redirecting its output (>) makes a .csv file. It is extremely trivial: just this is relevant: echo "COPY ($1) TO stdout WITH CSV HEADER FORCE QUOTE *" | psql -X -h $GLL_BD_HOST $GLL_BD_NAME * csv2sql: https://github.com/pierrechtux/geolllibre/blob/master/csv2sql csv2sql works the other way: you give him a .csv file, and it dumps it into your database, in a 'tmp_imports' schema (makes it easy for cleaning...). These utility scripts are very rough: all fields are considered as strings (varchar), to be as generic as possible. I use these 2 utilities in production daily, for a number of purposes, including heavy diff'ing. So far, no worries, they proved to be very reliable, There are a couple of environment variables that should be just adapted for a generic use: $GLL_BD_HOST and $GLL_BD_NAME. My little 0.02€... À+ Pierre -- ____________________________________________________________________________ Pierre Chevalier PChGEI: Pierre Chevalier Géologue Et Informaticien Partenaire DALIBO Mesté Duran 32100 Condom Tél+fax : 09 75 27 45 62 06 37 80 33 64 Émail : pierrechevaliergeolCHEZfree.fr icq# : 10432285 jabber: pierre.chevalier1967@jabber.fr http://pierremariechevalier.free.fr/pierre_chevalier_geologue ____________________________________________________________________________