SQL 将带有十六进制的文本字符串作为字节插入 PostgreSQL

Inserting text string with hex into PostgreSQL as a bytea

提问by Donald Miner

I have a text file with several strings of hex in it:

我有一个包含几个十六进制字符串的文本文件：

013d7d16d7ad4fefb61bd95b765c8ceb
007687fc64b746569616414b78c81ef1

I would like to store these in the database as a bytea, instead of a varchar. That is, I would like the database to store 01 as the single byte 00000001, not characters '0' & '1'.

我想将这些作为bytea而不是varchar存储在数据库中。也就是说，我希望数据库将 01 存储为单字节 00000001，而不是字符“0”和“1”。

I can easily run this file through sed to format/escape it any way I need to.

我可以轻松地通过 sed 运行此文件，以我需要的任何方式对其进行格式化/转义。

This is what I have tried:

这是我尝试过的：

create table mytable (testcol BYTEA);

This works:

这有效：

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

However, as soon as I have a byte that goes above \x7f, I get this error:

但是，一旦我有一个超过 \x7f 的字节，我就会收到此错误：

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

Any ideas, or am I approaching things wrong?

有什么想法，还是我处理错了？

回答by araqnid

You can convert a hex string to bytea using the decodefunction (where "encoding" means encoding a binary value to some textual value). For example:

您可以使用该decode函数将十六进制字符串转换为 bytea （其中“编码”表示将二进制值编码为某个文本值）。例如：

select decode('DEADBEEF', 'hex');
      decode      
------------------
 6567

which is more understandable with 9.0's default output:

使用 9.0 的默认输出更容易理解：

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

The reason you can't just say E'\xDE\xAD\xBE\xEF'is that this is intended to make a text value, not a bytea, so Postgresql will try to convert it from the client encoding to the database encoding. You could write the bytea escape format like that, but you need to double the backslashes: E'\\336\\255\\276\\357'::bytea. I think you can see why the bytea format is being changed.... IMHO the decode()function is a reasonable way of writing inputs, even though there is some overhead involved.

不能只说的原因E'\xDE\xAD\xBE\xEF'是这是为了生成文本值，而不是字节，因此 Postgresql 会尝试将其从客户端编码转换为数据库编码。您可以像这样编写 bytea 转义格式，但是您需要将反斜杠加倍：E'\\336\\255\\276\\357'::bytea. 我想你可以明白为什么要改变 bytea 格式......恕我直言，该decode()函数是一种合理的输入方式，即使涉及一些开销。

回答by Julius Musseau

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

回答by Clint Pachl

The Ruby Way

红宝石之路

I recently needed to read/write binary data from/to Postgres, but via Ruby. Here's how I did it using the Pg library.

我最近需要通过 Ruby 从/向 Postgres 读取/写入二进制数据。这是我使用Pg 库的方法。

Although not strictly Postgres-specific, I thought I'd include this Ruby-centric answer for reference.

虽然不是严格特定于 Postgres，但我想我会包含这个以 Ruby 为中心的答案以供参考。

Postgres DB Setup

Postgres 数据库设置

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

Insert Binary Data

插入二进制数据

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

Select Binary Data

选择二进制数据

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

回答by rogerdpack

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