Does PostgreSQL have a built in option for converting multi-line text to single line?

I need to store multiline text as Name=Value pairs, basically line .ini files, but need to convert Value back to its original form. Value might contain some escape sequences etc.

The multiline text is not that short and could be upto 65K in length.

Postgres does is capable of exporting multi-line text as single lines when it backups a table in plain format without using SQL inserts. It is the type of text passed to the COPY tablename (column1, column2, columnxx)from stdin when the plain text backup is views.

Is the function that does the conversion standalone?

I tried quote_literal but it didn't come out the way I want.


To get row and columns from key=value;key=value;key=value string use , ; acts as newline in this case.

select unnest(string_to_array('key=value ; key2=value2', ';' )); 

returns :

 "key=value "
 " key2=value2"

select (string_to_array(a.r, '=' ))[1] as  k , (string_to_array(a.r, '=' ))[2] as v  from (select unnest(string_to_array('key=value ; key2=value2', ';' )) as r  ) as a


k , v 
"key";"value "
" key2";"value2"

To other way around (table test has k and v colums)

select string_agg(k || '=' ||v, ';') from test

if you have table like 'conf_file', 'key', 'value' you can use group by clause in string_agg

| improve this answer | |
  • I think you have misunderstood my question. My recent edit expresses it better. The value part is the multi-line text encoded to be on a single line, and that is how it should be stored in the text field. The white space codes must converted to \n\r etc or numeric form. I think there is a standard name for text encoded as such. It is somewhat similar to the bytea format which is a mixture of text and some escape sequences. – vfclists Sep 18 '14 at 11:03
  • i still dont understand question – simplexio Sep 18 '14 at 11:36
  • I realized that I wasn't using the right terminology to describe my question. I am looking for a generic solution as well as one that may be a built in Postgres function. The general version is here - stackoverflow.com/questions/25904302/…. I think I will ask a new Postgres specific one. – vfclists Sep 18 '14 at 11:44
  • I have created a new question here - dba.stackexchange.com/questions/76972/… – vfclists Sep 18 '14 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.