Message-ID: <8ec53dc7d4508410124a5a6d1442840aa90a8f0c.camel@cybertec.at>
Subject: Re: Escaping special characters - \r when doing COPY CSV
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Edwin UY <edwin.uy@gmail.com>, Pgsql-admin
	 <pgsql-admin@lists.postgresql.org>
Date: Thu, 04 Sep 2025 09:29:35 +0200
In-Reply-To: 
 <CA+wokJ9_q+81PFDH2NriJUZg8BjtBk=Z+ov4LRybmKAq70NBnA@mail.gmail.com>
References: 
	<CA+wokJ9_q+81PFDH2NriJUZg8BjtBk=Z+ov4LRybmKAq70NBnA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/8ec53dc7d4508410124a5a6d1442840aa90a8f0c.camel%40cybertec.at>
Precedence: bulk

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 u=
se \copy.
> I need to send the output to csv.
> One of the fields has a special character =3D \r. I can see it visually.=
=20
>=20
> Running the \copy below gives me extra row/s because of the \r
> \copy ( select *=C2=A0 from blah=C2=A0 where blah_ref =3D '666'=C2=A0=C2=
=A0 order by date_created desc=C2=A0=C2=A0limit 5; )=C2=A0to 'abc.csv' WITH=
 CSV DELIMITER ',' HEADER
>=20
> I have tried this
> \copy ( select *=C2=A0 from blah=C2=A0 where blah_ref =3D '666'=C2=A0=C2=
=A0 order by date_created desc=C2=A0=C2=A0limit 5; )=C2=A0to 'abc.csv' WITH=
 (FORMAT CSV, HEADER, ESCAPE '\r');
>=20
> But it gives ERROR: =C2=A0COPY escape must be a single one-byte character=
.
>=20
> Besides using a view for the SQL, as suggested when I do a web search, an=
y chance someone here knows how to do it without=C2=A0using a view?

There is no problem with having the carriage return characters in the CSV o=
utput,
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 pre=
pend
the opening single quote with an "E" for "extended":

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

Yours,
Laurenz Albe