Getting binary data from PostgreSQL as a base-64 encoded string
1 min readJun 21, 2019
Suppose you have a bytea column containing some binary data:
When selecting, use the encode function to encode the binary data as a base-64 string:
As you can see, postgres splits the base-64 string across multiple lines, at 76 character-width. The important thing to notice here is that the + at the end of each line is NOT part of the base-64 string.
niel$ echo -n IlRoZSBxdWljayBicm93biBmb3gganVtcHMgb3ZlciB0aGUgbGF6eSBkb2ciIGlzIGFuIEVuZ2xpc2gtbGFuZ3VhZ2UgcGFuZ3JhbeKAlGEgc2VudGVuY2UgdGhhdCBjb250YWlucyBhbGwgb2YgdGhlIGxldHRlcnMgb2YgdGhlIGFscGhhYmV0LiBJdCBpcyBjb21tb25seSB1c2VkIGZvciB0b3VjaC10eXBpbmcgcHJhY3RpY2UsIHRlc3RpbmcgdHlwZXdyaXRlcnMgYW5kIGNvbXB1dGVyIGtleWJvYXJkeXBpbmcgcHJhY3RpY2UsIHRlc3RpbmcgdHlwZXdyaXRlcnMgYW5kIGNvbXB1dGVyIGtleWJvYXJkcywgZGlzcGxheWluZyBleGFtcGxlcyBvZiBmb250cywgYW5kIG90aGVyIGFwcGxpY2F0aW9ucyBpbnZvbHZpbmcgdGV4dCB3aGVyZSB0aGUgdXNlIG9mIGFsbCBsZXR0ZXJzIGluIHRoZSBhbHBoYWJldCBpcyBkZXNpcmVkLiBPd2luZyB0byBpdHMgYnJldml0eSBhbmQgY29oZXJlbmNlLCBpdCBoYXMgYmVjb21lIHdpZGVseSBrbm93bi4= | base64 --decode"The quick brown fox jumps over the lazy dog" is an English-language pangram—a sentence that contains all of the letters of the alphabet. It is commonly used for touch-typing practice, testing typewriters and computer keyboardyping practice, testing typewriters and computer keyboards, displaying examples of fonts, and other applications involving text where the use of all letters in the alphabet is desired. Owing to its brevity and coherence, it has become widely known.
You need to remove the + characters from the result to form the valid, complete base-64 string, as shown in this example.
