Ruby on Rails to Postgres Binary encoding

Question

I have a Ruby on Rails + Postgresql app that saves file images into db, in some postgres installations data is saved erroneously in what it looks like a different bytea format, so the image in some postgres installation fails to return file correctly

WORKING SAVED DATA

\xFF\xD8\xFF\xE1\x00\x\xFF\xEE\x00\x0EAdobe\x00d\xC0\x00\x00\x00\x01\xFF\xDB\x00\x84\x00\x06\x04\x04\x04\x05\x04\x06\x05\x05\x06\t\x06\x05\x06\t\v\b\x06\x06\b\v\f\n\n\v\n\n\f\x10\f\f\f\f\f\f\x10\f\x0E\x0F\x10\x0F\x0E\f\x13\x13\x14\x14\x13\x13\x1C\e\e\e\x1C\x1F\x1F\x1F\x1F\x1F\x1F\x1F\x1F\x1F\x1F\x01\a\a\a\r\f\r\x18\x10\x10\x18\x1A\x15\x11\x15

NON WORKING SAVED DATA

x89504e470d0a1a0a0000000d494844520000014a0000003d0806000000cb7920c80000001974455874536f6674776172650041646f626520496d616765526561647971c9653c000047eb4944415478daec5d07781455d79e99edbdef269bb62940208426bd4a5110503e51c40e28d8051145fcec62ff4041c08628624169a28234419ad2a477d2934db2bdf736ffb95bc226a46c4240f1cf7d9e81ecececade7bef73de79e7b062749126b4b6da92db5a5b6d470

any suggestions??

RoR

On RoR i do this to save the file

data = (!file.nil?) ? file.read.force_encoding("UTF-8") : nil

Brand.create!({name: brand[:name], value: brand[:value], file: file,  file_properties: get_file_properties(file_url), default: brand[:default], data: data})

and to get the the file image

image_tag(o.get_base64_image, style: 'max-width:100%')

With the model method

def get_base64_image
    return self.data != nil ? 'data:image/png;base64,' +     Base64.encode64(self.data) : ""
  end

Server encoding for both servers is UTF-8

Postgres

potgresql.conf

bytea_output = 'escape'

Working Server

List of databases
        Name        |   Owner    | Encoding |   Collate   |    Ctype    |     Access privileges     
--------------------+------------+----------+-------------+-------------+---------------------------
 postgres            | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Non-working server

List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | es_MX.UTF-8 | es_MX.UTF-8 |

Hi and welcome to stack overflow. I suggest that you tell us what you're trying to do... let us know why you're expecting a particular encoding over the other... show us the code you have so far and what you've tried to solve it... then we can begin to try to help you :) — Taryn East
Also: why does the different encoding matter? Does it actually work anyway? Is there actually an error? Can you show us what the actual error is instead of just telling us that the db stores things in different encodings? — Taryn East

Javi Flores Javi Flores · Accepted Answer · 2016-07-12T18:54:07

The issue is related to the output of BYTEA column in postgresql, i solved like this:

ALTER ROLE postgres SET bytea_output TO 'escape';

Ruby on Rails to Postgres Binary encoding

RoR

Postgres

1 Answers