public inbox for [email protected]
help / color / mirror / Atom feedEscaping special characters - \r when doing COPY CSV
4+ messages / 2 participants
[nested] [flat]
* Escaping special characters - \r when doing COPY CSV
@ 2025-09-04 06:01 Edwin UY <[email protected]>
2025-09-04 07:29 ` Re: Escaping special characters - \r when doing COPY CSV Laurenz Albe <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Edwin UY @ 2025-09-04 06:01 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Escaping special characters - \r when doing COPY CSV
2025-09-04 06:01 Escaping special characters - \r when doing COPY CSV Edwin UY <[email protected]>
@ 2025-09-04 07:29 ` Laurenz Albe <[email protected]>
2025-09-04 09:39 ` Re: Escaping special characters - \r when doing COPY CSV Edwin UY <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2025-09-04 07:29 UTC (permalink / raw)
To: Edwin UY <[email protected]>; Pgsql-admin <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Escaping special characters - \r when doing COPY CSV
2025-09-04 06:01 Escaping special characters - \r when doing COPY CSV Edwin UY <[email protected]>
2025-09-04 07:29 ` Re: Escaping special characters - \r when doing COPY CSV Laurenz Albe <[email protected]>
@ 2025-09-04 09:39 ` Edwin UY <[email protected]>
2025-09-04 15:15 ` Re: Escaping special characters - \r when doing COPY CSV Laurenz Albe <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Edwin UY @ 2025-09-04 09:39 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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 <[email protected]>
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Escaping special characters - \r when doing COPY CSV
2025-09-04 06:01 Escaping special characters - \r when doing COPY CSV Edwin UY <[email protected]>
2025-09-04 07:29 ` Re: Escaping special characters - \r when doing COPY CSV Laurenz Albe <[email protected]>
2025-09-04 09:39 ` Re: Escaping special characters - \r when doing COPY CSV Edwin UY <[email protected]>
@ 2025-09-04 15:15 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2025-09-04 15:15 UTC (permalink / raw)
To: Edwin UY <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-09-04 15:15 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-04 06:01 Escaping special characters - \r when doing COPY CSV Edwin UY <[email protected]>
2025-09-04 07:29 ` Laurenz Albe <[email protected]>
2025-09-04 09:39 ` Edwin UY <[email protected]>
2025-09-04 15:15 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox