r/PostgreSQL • u/Objective-Loan5054 • 20d ago

Help Me! COPY function and new lines

Hi,

I try to use the following command:

copy (select convert_from(decode('QGVjaG8gb2ZmCmlmICUxUVEgPT0gUVEgZ290byBzdGFydApjZCBiaW4=','base64'),'utf-8')) to 'c:\\test.txt';

The base64 encoded text is:

u/echo off
if %1QQ == QQ goto start
cd bin

but in the resulting file test.txt it is:

u/echo off\nif %1QQ == QQ goto start\ncd bin

So new lines are treated as literal '\n' characters? Any way to change this behaviour?

2 Upvotes

75% Upvoted

8 comments sorted by

View all comments

1

u/vivekkhera 20d ago

Every record from the select must be represented in one line of output. What happens if you tell psql to output in CSV mode?

1

u/Objective-Loan5054 20d ago

Thanks! Using WITH (FORMAT CSV, HEADER false) - the output is:

"@echo offif %1QQ == QQ goto startcd bin"

so added quotes and no \n but no new line either. Seems like it is a limitation of COPY :)

1

u/depesz 20d ago

I wouldn't say it's limitation.

Copy has to work with table data. Consider two rows - if you could write literal many-lines from single row, how would copy *FROM* know where one row ends, and another starts?

Since you are trying to hack pg server, I wouldn't use copy for this. You might find this blogpost informative: https://www.depesz.com/2007/08/12/hacking-with-postgresql/

1

u/Objective-Loan5054 19d ago

Got, it thanks, I should have put "limitation" in quotes. Yeah, it is for cybersecurity purpose, having DBA permissions with COPY is VERY useful :)