Subject: Re: Extracting data into .csv format
To: "Ferrell, Denise D CTR NSWCDD, H11" <denise.ferrell.ctr@navy.mil>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
References: 
 <2BC18916DE4BF141AC1EFC55CE2CAE7D1B8DAE47@NAEANRFKXM01V.nadsusea.nads.navy.mil>
From: =?UTF-8?Q?Pierre_Chevalier_G=c3=a9ologue?= <pierrechevaliergeol@free.fr>
Message-ID: <56C5A1AF.5070106@free.fr>
Date: Thu, 18 Feb 2016 11:49:19 +0100
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:38.0) Gecko/20100101
 Icedove/38.5.0
MIME-Version: 1.0
In-Reply-To: 
 <2BC18916DE4BF141AC1EFC55CE2CAE7D1B8DAE47@NAEANRFKXM01V.nadsusea.nads.navy.mil>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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
____________________________________________________________________________


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin