lpad - postgresql bytea image

Insertar cadena de texto con hexadecimal en PostgreSQL como bytea

Question

Tengo un archivo de texto con varias cadenas de hexadecimal:

013d7d16d7ad4fefb61bd95b765c8ceb
007687fc64b746569616414b78c81ef1

Me gustaría almacenar estos en la base de datos como bytea , en lugar de varchar . Es decir, me gustaría que la base de datos almacene 01 como el byte único 00000001, no los caracteres ''0'' y ''1''.

Puedo ejecutar este archivo fácilmente a través de sed para formatearlo / escapar de cualquier forma que lo necesite.

Esto es lo que he intentado:

create table mytable (testcol BYTEA);

Esto funciona:

insert into mytable (testcol) values (E''/x7f/x7f'');

Sin embargo, tan pronto como tengo un byte que va por encima de / x7f, aparece este error:

insert into mytable (testcol) values (E''/x7f/x80'');
ERROR: invalid byte sequence for encoding "UTF8": 0x80

Alguna idea, o me estoy acercando a las cosas mal?

Answer 1

El camino de Ruby

Recientemente necesité leer / escribir datos binarios de / a Postgres, pero a través de Ruby. Así es como lo hice usando la biblioteca Pg .

Aunque no es estrictamente específico de Postgres, pensé incluir esta respuesta centrada en Ruby como referencia.

Configuración de Postgres DB

require ''pg''
DB = PG::Connection.new(host: ''localhost'', dbname:''test'')
DB.exec "CREATE TABLE mytable (testcol BYTEA)"
BINARY = 1

Insertar datos binarios

sql = "INSERT INTO mytable (testcol) VALUES ($1)"
param = {value: binary_data, format: BINARY}
DB.exec_params(sql, [param]) {|res| res.cmd_tuples == 1 }

Seleccionar datos binarios

sql = "SELECT testcol FROM mytable LIMIT 1"
DB.exec_params(sql, [], BINARY) {|res| res.getvalue(0,0) }

Answer 2

Puede convertir una cadena hexadecimal en bytea usando la función de decode (donde "codificación" significa codificar un valor binario a algún valor textual). Por ejemplo:

select decode(''DEADBEEF'', ''hex'');
decode
------------------
/336/255/276/357

que es más comprensible con la salida predeterminada de 9.0:

 decode
------------
/xdeadbeef

La razón por la que no se puede decir que E''/xDE/xAD/xBE/xEF'' es que se trata de hacer un valor de texto, no un bytea, entonces Postgresql intentará convertirlo de la codificación del cliente a la codificación de la base de datos. Podría escribir el formato de escape bytea de esa manera, pero necesita duplicar las barras diagonales inversas: E''//336//255//276//357''::bytea . Creo que se puede ver por qué se está cambiando el formato bytea ... En mi humilde opinión, la función de decode() es una forma razonable de escribir entradas, a pesar de que hay una sobrecarga involucrada.

Answer 3

More y More options :

-- insert 123[char of value zero]abc456
insert into mytable (testcol) values decode(E''123//000abc456'', ''escape'');
-- insert abc456
insert into mytable (testcol) values decode(''YWJjNDU2'', ''base64'');

Answer 4

INSERT INTO mytable (testcol) VALUES (decode(''013d7d16d7ad4fefb61bd95b765c8ceb'', ''hex''))