0

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.

2

1 Answer 1

1

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 :

column1 
 "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

returns:

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

4
  • 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, 2014 at 11:03
  • i still dont understand question Sep 18, 2014 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, 2014 at 11:44
  • I have created a new question here - dba.stackexchange.com/questions/76972/…
    – vfclists
    Sep 18, 2014 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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