0

I have a strange behavior in psql that makes long base64 encoded text break with newline when converting to json string

I encode my text in base64 as followed:

db=> select encode('-------------------------------------------------------------------'::bytea, 'base64');
                                    encode                                    
------------------------------------------------------------------------------
 LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t+
 LS0tLS0tLS0tLQ==

Here the output text is wrapped, that's not actually a problem. But then if I convert this base64 encoded text to a json string using to_json():

db=> select to_json(encode('-------------------------------------------------------------------'::bytea, 'base64')::text);
                                             to_json                                              
--------------------------------------------------------------------------------------------------
 "LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t\nLS0tLS0tLS0tLQ=="

Here the base64 encoded text in json has a new line character (\n) near the end which totally breaks the base64 code when decoding (Thanks Laurenz Albe for the correction!). The new line character gives me trouble later in my program and I'm searching a solution to fix it in psql.

I've tried using the /pset format command or setting PAGER="less -SF" psql ... (from other stackoverflow issue: Disable wrapping in Psql output) but without success.

The only solution I've found (and a very dirty one) is to do:

db=> select to_json(regexp_replace((select to_json(encode('----------------------------------------------------------'::bytea, 'base64')::text))::text, '(\\n|")', '', 'g'));
                                      to_json                                       
------------------------------------------------------------------------------------
 "LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ=="

Here I convert to JSON string (with to_json()) then I remove the JSON string quotes and new line characters (with regexp_replace()) and then re-converting to JSON again to get the expected result.

Improve this question
3
  • I don't understand how that breaks base64. base64 encoded values do contain line breaks. Commented Jul 17, 2018 at 10:27
  • That I didn't know. Apparently the base64 decoding do indeed work on that encoded value but my problem is actually elsewhere in my program where I just can't have new line characters in my base64 field. Is this new line character really inevitable here? Commented Jul 17, 2018 at 13:37
  • Unfortunately the line length of 76 bytes is hard coded in the PostgreSQL source. There is no comment that explains that choice. Commented Jul 17, 2018 at 17:49
Related questions
0
Wrapping text in JQGrid column using JSON data
0
Removing linebreak from php json output
2
How to trim whitespaces in JSON datatype of Postgres?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.