public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Edwin UY <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Re: Escaping special characters - \r when doing COPY CSV
Date: Thu, 04 Sep 2025 09:29:35 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+wokJ9_q+81PFDH2NriJUZg8BjtBk=Z+ov4LRybmKAq70NBnA@mail.gmail.com>
References: <CA+wokJ9_q+81PFDH2NriJUZg8BjtBk=Z+ov4LRybmKAq70NBnA@mail.gmail.com>

On Thu, 2025-09-04 at 18:01 +1200, Edwin UY wrote:
> I don't have access to run copy as this is a RDS database so I can only use \copy.
> I need to send the output to csv.
> One of the fields has a special character = \r. I can see it visually. 
> 
> Running the \copy below gives me extra row/s because of the \r
> \copy ( select *  from blah  where blah_ref = '666'   order by date_created desc  limit 5; ) to 'abc.csv' WITH CSV DELIMITER ',' HEADER
> 
> I have tried this
> \copy ( select *  from blah  where blah_ref = '666'   order by date_created desc  limit 5; ) to 'abc.csv' WITH (FORMAT CSV, HEADER, ESCAPE '\r');
> 
> But it gives ERROR:  COPY escape must be a single one-byte character.
> 
> Besides using a view for the SQL, as suggested when I do a web search, any chance someone here knows how to do it without using a view?

There is no problem with having the carriage return characters in the CSV output,
but if you'd rather do without them, you could trim them:

  \copy (SELECT trim(textcol, E'\u000d'), othercol, ... FROM tab ...) TO ...

In order to include escape sequences into a string literal, you have to prepend
the opening single quote with an "E" for "extended":

  E'\r'
  E'\x0D'
  E'\u000D'
  E'\U0000000D'

Yours,
Laurenz Albe

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Escaping special characters - \r when doing COPY CSV
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox