MIME-Version: 1.0
References: 
 <CA+wokJ9_q+81PFDH2NriJUZg8BjtBk=Z+ov4LRybmKAq70NBnA@mail.gmail.com>
 <8ec53dc7d4508410124a5a6d1442840aa90a8f0c.camel@cybertec.at>
In-Reply-To: <8ec53dc7d4508410124a5a6d1442840aa90a8f0c.camel@cybertec.at>
From: Edwin UY <edwin.uy@gmail.com>
Date: Thu, 4 Sep 2025 21:39:46 +1200
Message-ID: 
 <CA+wokJ9vnqAg=baD2FzFptim4Ne-t0Tx3m7Mt8U4J-YzcjycgA@mail.gmail.com>
Subject: Re: Escaping special characters - \r when doing COPY CSV
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c2de14063df6826c"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BwokJ9vnqAg%3DbaD2FzFptim4Ne-t0Tx3m7Mt8U4J-YzcjycgA%40mail.gmail.com>
Precedence: bulk

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

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=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec.=
at>
wrote:

> 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 =3D \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 =3D '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 =3D '666'   order by
> date_created desc  limit 5; ) to 'abc.csv' WITH (FORMAT CSV, HEADER, ESCA=
PE
> '\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
>

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

<div dir=3D"ltr">Thanks Laurenz, any trick up your sleeves that will work f=
or a select * instead of having to do it for individual columns=C2=A0</div>=
<br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=
=3D"gmail_attr">On Thu, Sep 4, 2025 at 7:29=E2=80=AFPM Laurenz Albe &lt;<a =
href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cybertec.at</a>&gt; w=
rote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0p=
x 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On Thu, 20=
25-09-04 at 18:01 +1200, Edwin UY wrote:<br>
&gt; I don&#39;t have access to run copy as this is a RDS database so I can=
 only use \copy.<br>
&gt; I need to send the output to csv.<br>
&gt; One of the fields has a special character =3D \r. I can see it visuall=
y. <br>
&gt; <br>
&gt; Running the \copy below gives me extra row/s because of the \r<br>
&gt; \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<br>
&gt; <br>
&gt; I have tried this<br>
&gt; \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>
&gt; <br>
&gt; But it gives ERROR: =C2=A0COPY escape must be a single one-byte charac=
ter.<br>
&gt; <br>
&gt; 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?<br>
<br>
There is no problem with having the carriage return characters in the CSV o=
utput,<br>
but if you&#39;d rather do without them, you could trim them:<br>
<br>
=C2=A0 \copy (SELECT trim(textcol, E&#39;\u000d&#39;), othercol, ... FROM t=
ab ...) TO ...<br>
<br>
In order to include escape sequences into a string literal, you have to pre=
pend<br>
the opening single quote with an &quot;E&quot; for &quot;extended&quot;:<br=
>
<br>
=C2=A0 E&#39;\r&#39;<br>
=C2=A0 E&#39;\x0D&#39;<br>
=C2=A0 E&#39;\u000D&#39;<br>
=C2=A0 E&#39;\U0000000D&#39;<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--000000000000c2de14063df6826c--