Subject: Escaping special characters - \r when doing COPY CSV

From: Edwin UY <[email-masked]>
Date: Thu, 4 Sep 2025 18:01:45 +1200

Hi,

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?

Please advise. Thanks in advance.

Regards,
Ed

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 04 Sep 2025 09:29:35 +0200

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.
> \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');
> 
> 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

---

From: Edwin UY <[email-masked]>
Date: Thu, 4 Sep 2025 21:39:46 +1200

Thanks Laurenz, any trick up your sleeves that will work for a select *
instead of having to do it for individual columns

On Thu, Sep 4, 2025 at 7:29 PM Laurenz Albe <[email-masked]>
wrote:

> > \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');
> >
> > 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?
>

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 04 Sep 2025 17:15:30 +0200

On Thu, 2025-09-04 at 21:39 +1200, Edwin UY wrote:
> Thanks Laurenz, any trick up your sleeves that will work for a select * instead of having to do it for individual columns 

I would have told you if I had.

Yours,
Laurenz Albe