Вставка текстовой строки с шестнадцатеричным в PostgreSQL в виде байта

У меня есть текстовый файл с несколькими строками шестнадцатеричного в нем:

013d7d16d7ad4fefb61bd95b765c8ceb
007687fc64b746569616414b78c81ef1

Я хотел бы сохранить их в базе данных как bytea, а не varchar. То есть, я хотел бы, чтобы база данных сохраняла 01 как единственный байт 00000001, а не символы '0' и '1'.

Я могу легко запустить этот файл через sed, чтобы форматировать/бежать так, как мне нужно.

Вот что я пробовал:

create table mytable (testcol BYTEA);

Это работает:

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

Однако, как только у меня есть байт, который выше \x7f, я получаю эту ошибку:

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

Любые идеи, или я не ошибаюсь?

Ответ 1

Вы можете преобразовать шестнадцатеричную строку в bytea с помощью функции decode (где "кодирование" означает кодирование двоичного значения до некоторого текстового значения). Например:

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

что более понятно с 9.0 по умолчанию:

   decode   
------------
 \xdeadbeef

Причина, по которой вы не можете просто сказать E'\xDE\xAD\xBE\xEF', состоит в том, что она предназначена для создания текстового значения, а не байта, поэтому Postgresql попытается преобразовать его из клиентской кодировки в кодировку базы данных. Вы можете написать формат выхода bytea, но вам нужно удвоить обратную косую черту: E'\\336\\255\\276\\357'::bytea. Я думаю, вы можете понять, почему изменяется формат bytea... IMHO Функция decode() является разумным способом записи входов, хотя есть некоторые накладные расходы.

Ответ 2

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

Ответ 3

Рубиновый путь

Мне недавно нужно было читать/записывать двоичные данные из/в Postgres, но через Ruby. Вот как я это сделал, используя Pg library.

Хотя это не строго специфично для Postgres, я думал, что включу этот рубиноцентричный ответ для справки.

Настройка Postgres DB

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

Вставить двоичные данные

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

Выберите двоичные данные

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

Ответ 4

Больше и разнообразные варианты:

-- 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');