public inbox for [email protected]help / color / mirror / Atom feed
Escaping 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]> 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 07:29 Laurenz Albe <[email protected]> parent: 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 09:39 Edwin UY <[email protected]> parent: 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 15:15 Laurenz Albe <[email protected]> parent: Edwin UY <[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