将十六进制文本字符串作为bytea插入PostgreSQL [英] Inserting text string with hex into PostgreSQL as a bytea
问题描述
我有一个文本文件,其中包含多个十六进制字符串:
I have a text file with several strings of hex in it:
013d7d16d7ad4fefb61bd95b765c8ceb
007687fc64b746569616414b78c81ef1
我想将它们作为 bytea 而不是存储在数据库中> varchar 。也就是说,我希望数据库将01存储为单字节00000001,而不是字符 0和 '1'。
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'.
我可以通过sed轻松运行此文件,以任何需要的方式对其进行格式化/转义。
I can easily run this file through sed to format/escape it any way I need to.
这是我尝试过的:
create table mytable (testcol BYTEA);
工作原理:
insert into mytable (testcol) values (E'\x7f\x7f');
但是,一旦我有一个超出\x7f的字节,就会出现此错误:
However, as soon as I have a byte that goes above \x7f, I get this error:
insert into mytable (testcol) values (E'\x7f\x80');
ERROR: invalid byte sequence for encoding "UTF8": 0x80
任何想法,还是
推荐答案
您可以使用解码$将十六进制字符串转换为bytea。 c $ c>函数(其中 encoding表示将二进制值编码为某些文本值)。例如:
You can convert a hex string to bytea using the decode
function (where "encoding" means encoding a binary value to some textual value). For example:
select decode('DEADBEEF', 'hex');
decode
------------------
\336\255\276\357
使用9.0的默认输出更容易理解:
which is more understandable with 9.0's default output:
decode
------------
\xdeadbeef
您不能只说 E'\xDE\xAD\xBE\xEF'
的原因是旨在生成文本值,而不是字节数,因此Postgresql会尝试将其从客户端编码转换为数据库编码。您可以这样编写bytea转义格式,但需要将反斜杠加倍: E'\\336\\255\\276\\357':: bytea
。我想您可以看到为什么更改了bytea格式的问题。...恕我直言, decode()
函数是一种合理的输入输入方式,即使其中涉及一些开销
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.
这篇关于将十六进制文本字符串作为bytea插入PostgreSQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!