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
