Обсуждение: Escaping special characters - _ when doing COPY CSV
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
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 (FORMATCSV, 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 usinga 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
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 <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 = \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
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