CODEWITHSUNDEEP
postgresqlbase64encodepsql
MacUsers
MacUsers

Reputation: 2241

Stop PostgreSQL from spliting value(s) in multiple lines?

I'm converting a binary data (bytea) data type to string using encode(foo::bytea, 'base64') but the output is being split in multiple lines:

-[ RECORD 1 ]-+-----------------------------------------------------------------------
 req_id       | 132675
 b_string     | d4IF4jCCBd4GCSqGSIb3DQEHAqCCBc8wggXLAgEDMQ0wCwYJBAIBMIGYBgZngQgBAQGg+
              | gY0EgYowgYcCAQAwCwYJYIZIAQAwCwYJYIZIAWUDBAIHUwUdH0JybzpY2evf+v9Xg86b+
              | HSGTGYBIb/QwJQIBAgQg1M6/cJ+S39XY1lm43oenxJNLrYcc3hVw7fgwJQIBDgQgIAil+
              | 1JnYbdS0p4pK07kMkb/dbMcxryx6mqbLTzx+YJ6gggQbMI2gAwIBAgIESS7vwTAKBggq+
              | LUxRjUXbTgfGwUKOFwemsc4KXbsLZ13MkbNfAQ==

How can get a single string instead?

UPDATE: based on the solution by @LaurenzAlbe

Just for the completeness, this is what I end up doing the gave me the result I wanted:

translate(encode(foo::bytea, 'base64'), E'\n', '')

Upvotes: 10

Views: 2930

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248175

psql doesn't split your string in multiple lines.

It's the string that contains new-line characters (ASCII 10), and psql displays them accurately. The + at the end of every line is psql's way of telling you that the value is continued in the next line.

You can use the unaligned mode (psql option -A) to get rid of the +, but the output format is less attractive then.

You can get rid of the newlines in a string with

SELECT translate(..., E'\n', '');

decode will be able to handle such a string.

Upvotes: 12

Related Questions