generate CSV with Copy+Quotes - Postgres 9.2

On 09/21/2016 02:23 AM, Patrick B wrote:

Hi guys,

I'm exporting some data for testing purpose.

COPY (SELECT
name_first
name_last,
email,
company
FROM
clients
)
TO '/var/lib/pgsql/test1.csv' DELIMITER ',' csv HEADER QUOTE '"';

cat /var/lib/pgsql/test1.csv

"","",hiddenemail@hotmail.com
<mailto:hiddenemail@hotmail.com>,"",John Wayne

In your COPY you have fields and in the CSV file you have five.
Are you looking at the correct file?

See how the email and the company aren't into two "?

Have no idea what you are trying to say.

hiddenemail@hotmail.com looks like a correct email to me.

Maybe if you show what the data is in the table?

*What I need is:*

"","","hiddenemail@hotmail.com
<mailto:hiddenemail@hotmail.com>","","John Wayne"

I thought that by putting QUOTE the COPY would do that?

Is there any way I can put the " into those fields?

Cheers

Patrick

--
Adrian Klaver
adrian.klaver@aklaver.com

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

On Wed, Sep 21, 2016 at 9:33 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 09/21/2016 02:23 AM, Patrick B wrote:

Hi guys,

I'm exporting some data for testing purpose.

COPY (SELECT
name_first
name_last,
email,
company
FROM
clients
)
TO '/var/lib/pgsql/test1.csv' DELIMITER ',' csv HEADER QUOTE '"';

cat /var/lib/pgsql/test1.csv

"","",hiddenemail@hotmail.com
<mailto:hiddenemail@hotmail.com>,"",John Wayne

In your COPY you have fields and in the CSV file you have five.
Are you looking at the correct file?

See how the email and the company aren't into two "?

Have no idea what you are trying to say.

hiddenemail@hotmail.com looks like a correct email to me.

Maybe if you show what the data is in the table?

*What I need is:*

"","","hiddenemail@hotmail.com
<mailto:hiddenemail@hotmail.com>","","John Wayne"

I thought that by putting QUOTE the COPY would do that?

Is there any way I can put the " into those fields?

Cheers

Patrick

--
Adrian Klaver
adrian.klaver@aklaver.com

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

Perhaps you mean you want to quote of all strings? For that you use FORCE
QUOTE.
eg:
COPY (SELECT
name_first
name_last,
email,
company
FROM
clients
)
TO '/var/lib/pgsql/test1.csv' DELIMITER ','
csv HEADER QUOTE '"';
FORCE QUOTE name_first, name_last, email, company;

Please RTFM
*https://www.postgresql.org/docs/9.4/static/sql-copy.html
<https://www.postgresql.org/docs/9.4/static/sql-copy.html&gt;*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Perhaps you mean you want to quote of all strings? For that you use FORCE
QUOTE.
eg:
COPY (SELECT
name_first
name_last,
email,
company
FROM
clients
)
TO '/var/lib/pgsql/test1.csv' DELIMITER ','
csv HEADER QUOTE '"';
FORCE QUOTE name_first, name_last, email, company;

Please RTFM
*https://www.postgresql.org/docs/9.4/static/sql-copy.html
<https://www.postgresql.org/docs/9.4/static/sql-copy.html&gt;*
--

Thanks! FORCE QUOTE was exactly what I needed.

Patrick.