我有一个文本文件，其中包含几个十六进制字符串：
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
你有任何想法，或者是我的方法有问题吗？
使用decode函数可以将十六进制字符串转换为bytea类型（其中“编码”是指将二进制值编码为某些文本值）。例如：
select decode('DEADBEEF', 'hex');
decode
------------------
\336\255\276\357
使用9.0的默认输出结果更易懂:
decode
------------
\xdeadbeef
不能仅使用E'\xDE\xAD\xBE\xEF'，因为它是为了生成文本值而不是bytea而设计的，所以PostgreSQL会尝试将其从客户端编码转换为数据库编码。你可以像那样编写bytea转义格式，但需要将反斜杠加倍：E'\\336\\255\\276\\357'::bytea。我认为你可以看出为什么bytea格式正在被更改了...在我看来，decode()函数是一种合理的输入方法，即使有一些额外的开销。
插入数据到表mytable中的testcol列中，插入的数据为将十六进制字符串'013d7d16d7ad4fefb61bd95b765c8ceb'解码后得到的值。
最近我需要使用Ruby从Postgres读写二进制数据。以下是我使用Pg库完成的操作。
虽然这不是严格意义上的Postgres特定内容，但我认为提供一个以Ruby为中心的解决方案供参考。
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) }
这是一个更新的答案，它包括了如何插入和查询。
可以使用 decode 函数将十六进制转换为 bytea 值。这应该用于查询和插入操作。
这可以用于插入和查询。
SELECT * FROM mytable WHERE testcol = (decode('013d7d16d7ad4fefb61bd95b765c8ceb', 'hex'));
一个用户提出了以下问题：
在文档"二进制字符串函数和操作"中，他们对How does searching the bytea field by hex value after inserting it?
SELECT * FROM my_table WHERE myHexField = (encode('013d7d16d7ad4fefb61bd95b765c8ceb', 'hex'));does not work.
encode和decode进行了描述。 +==================================+=============+=======================================================================================================+=======================================+============+
| Function | Return Type | Description | Example | Result |
+==================================+=============+=======================================================================================================+=======================================+============+
| decode(string text, format text) | bytea | Decode binary data from textual representation in string. Options for format are same as in encode. | decode('123\000456', 'escape') | 123\000456 |
+----------------------------------+-------------+-------------------------------------------------------------------------------------------------------+---------------------------------------+------------+
| encode(data bytea, format text) | text | Encode binary data into a textual representation. Supported formats are: base64, hex, escape. escape | encode('123\000456'::bytea, 'escape') | 123\000456 |
| | | converts zero bytes and high-bit-set bytes to octal sequences (\nnn) and doubles backslashes. | | |
+----------------------------------+-------------+-------------------------------------------------------------------------------------------------------+---------------------------------------+------------+
因此，您会注意到Encode用于将二进制数据编码为文本字符串，并返回文本。然而，由于我们存储的是bytea，所以我们必须在插入和查询时使用decode。
create table mytable (testcol BYTEA);
INSERT INTO
mytable (testcol)
VALUES
(decode('013d7d16d7ad4fefb61bd95b765c8ceb', 'hex'));
来源：请看之前的回答
来源：https://www.postgresql.org/docs/current/functions-binarystring.html
这个页面介绍了PostgreSQL数据库中与二进制字符串处理相关的函数。在计算机科学中，二进制字符串是由0和1组成的序列，常用于表示图像、音频和视频等非文本数据。INSERT INTO
mytable (testcol)
VALUES
('\x013d7d16d7ad4fefb61bd95b765c8ceb'::bytea);
-- how to insert the string "123[a char of value zero]abc456"
insert into mytable (testcol) values decode(E'123\\000abc456', 'escape');
-- how to insert the string "123abc456"
insert into mytable (testcol) values decode(E'123abc456', 'escape');
-- how to insert in base64: insert string "abc456"
insert into mytable (testcol) values decode('YWJjNDU2', 'base64');
bytea列。 - VSO