postgresql 通过命令行查询postgres中的bytea字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6637843/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query bytea field in postgres via command line
提问by John Smith
I have a table with a bytea field, and it would be convenient if I could do queries via the command line (or pgAdmin's query executor). I've got the hex value as a string. Is there a built in function to convert hex to bytea?
我有一个带有 bytea 字段的表，如果我可以通过命令行（或 pgAdmin 的查询执行器）进行查询会很方便。我将十六进制值作为字符串。是否有内置函数将十六进制转换为字节？
I'd like to do something like:
我想做类似的事情：
SELECT * FROM table WHERE my_bytea_field=some_???_function('fa26e312');
where 'fa26e312' is the hex value of the bytea field I want.
其中 'fa26e312' 是我想要的 bytea 字段的十六进制值。
Note: this is just to be helpful while I'm developing / debugging things, I can do it via code but I'd like to be able to do it by hand in a query.
注意：这只是为了在我开发/调试事物时有所帮助，我可以通过代码来完成，但我希望能够在查询中手动完成。
采纳答案by Grzegorz Szpetkowski
Try using built-in decode(string text, type text)function (it returns bytea). You can run queries via CLI using psqlin non-interactive mode, that is with -cswitch (there are some formatting options if you like):
尝试使用内置decode(string text, type text)函数（它返回bytea）。您可以psql在非交互模式下通过 CLI 运行查询，即使用-cswitch（如果您愿意，可以使用一些格式选项）：
psql -c "SELECT * FROM table WHERE my_bytea_field=decode('fa26e312', 'hex');"
Example:
例子：
CREATE TABLE test(id serial, my_bytea_field bytea);
INSERT INTO test (my_bytea_field) VALUES
(E'\320\170'::bytea),
(E'\100\070'::bytea),
(E'\377\377'::bytea);
psql -tc "SELECT * FROM test WHERE my_bytea_field=decode('ffff', 'hex');"
3 | 77
回答by Garen
SELECT * FROM table WHERE my_bytea_field=E'\xfa26e312';
Just as in the example in the Binary Data Typesdocs (note the E'\\x' prefix):
就像二进制数据类型文档中的示例一样（注意 E'\\x' 前缀）：
SELECT E'\xDEADBEEF';
