MIME-Version: 1.0
From: Edwin UY <edwin.uy@gmail.com>
Date: Thu, 4 Sep 2025 18:01:45 +1200
Message-ID: 
 <CA+wokJ9_q+81PFDH2NriJUZg8BjtBk=Z+ov4LRybmKAq70NBnA@mail.gmail.com>
Subject: Escaping special characters - \r when doing COPY CSV
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ff73e6063df37615"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BwokJ9_q%2B81PFDH2NriJUZg8BjtBk%3DZ%2Bov4LRybmKAq70NBnA%40mail.gmail.com>
Precedence: bulk

--000000000000ff73e6063df37615
Content-Type: text/plain; charset="UTF-8"

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

--000000000000ff73e6063df37615
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi,</div><div><br></div><div>I don&#39;t have access =
to run copy as this is a RDS database so I can only use \copy.</div><div>I =
need to send the output to csv.</div><div>One of the fields has a special c=
haracter =3D \r. I can see it visually. </div><div><br></div><div>Running t=
he \copy below gives me extra row/s because of the \r</div><div>\copy (
select *=C2=A0 from blah=C2=A0 where blah_ref =3D &#39;666&#39;=C2=A0=C2=A0=
 order by date_created desc=C2=A0=C2=A0limit 5;

)=C2=A0to &#39;abc.csv&#39; WITH CSV DELIMITER &#39;,&#39; HEADER</div><div=
><br></div><div>I have tried this</div><div>
\copy (
select *=C2=A0 from blah=C2=A0 where blah_ref =3D &#39;666&#39;=C2=A0=C2=A0=
 order by date_created desc=C2=A0=C2=A0limit 5;

)=C2=A0to &#39;abc.csv&#39; WITH (FORMAT CSV, HEADER, ESCAPE &#39;\r&#39;);=
<br></div><div><br></div><div>But it gives ERROR: =C2=A0COPY escape must be=
 a single one-byte character.</div><div><br></div><div>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=C2=A0using a view?</div><div><br></div><div>Plea=
se advise. Thanks in advance.</div><div><br></div><div><br></div><div>Regar=
ds,</div><div>Ed</div><div><br></div></div>

--000000000000ff73e6063df37615--