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@xxxxxxxxxxx> 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
