postgresql 中的编码和解码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35476033/
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
Encoding and decoding in postgresql
提问by Edijs Petersons
Let's say we have a string 'a\b'. I need to encode it first, save to file, then read it from file and puck back in db.
假设我们有一个字符串 'a\b'。我需要先对它进行编码，保存到文件，然后从文件中读取它并在数据库中回滚。
How to encode and decode text that has escape characters?
如何编码和解码具有转义字符的文本？
select encode(E'a\b'::bytea, 'base64')
"YQg="
select decode('YQg=', 'base64')
"a0"
After decoding I am not getting back string as it was in it's original form.
解码后，我没有取回原始形式的字符串。
回答by Craig Ringer
You're using an E''string (escape string) and castingto bytea. The result will be a representation of that string in your current database encoding - probably UTF-8.
您正在使用E''字符串（转义字符串）并转换为bytea. 结果将是您当前数据库编码中该字符串的表示 - 可能是 UTF-8。
E'a\b'is the character athen the character represented by the escape \bwhich is ordinal \x08. PostgreSQL represents this string with a hex-escape when printing to the terminal because it's a non-printable character. The string is still two characters long.
E'a\b'是字符，a然后是由\b有序转义符表示的字符\x08。PostgreSQL 在打印到终端时用十六进制转义表示这个字符串，因为它是一个不可打印的字符。该字符串仍然是两个字符长。
postgres=> SELECT E'a\b';
?column?
----------
a\x08
(1 row)
postgres=> SELECT length(E'a\b');
length
--------
2
(1 row)
The cast to byteaimplicitly does a conversion to the current database encoding:
bytea强制转换为隐式转换为当前数据库编码：
postgres=> SELECT E'a\b'::bytea;
bytea
--------
\x6108
(1 row)
(\x61is the ASCII ordinal for ain most encodings).
(\x61是a大多数编码中的 ASCII 序数)。
Except you must be on an old PostgreSQL since you have bytea_output = escape, resulting in octal escape output instead:
除非你必须在旧的 PostgreSQL 上，因为你有bytea_output = escape，导致八进制转义输出：
postgres=> SELECT E'a\b'::bytea;
bytea
-------
a0
(1 row)
You need to decodethe bytea back into a text string, e.g.
您需要将 bytea解码回文本字符串，例如
convert_from(decode('YQg=', 'base64'), 'utf-8')
... and even then the nonprintable character \b will be printed as \x08by psql. You can verify that it is really that character inside the database using another client.
...甚则不可打印字符\ B将被打印为\x08通过psql。您可以使用另一个客户端验证它确实是数据库中的那个字符。
BTW, what's going on would be clearer if you instead explicitly encoded it when you stored it rather than relying on a cast to bytea:
顺便说一句，如果您在存储它时对其进行显式编码，而不是依赖强制转换为 bytea，那么发生的事情会更清楚：
encode(convert_to(E'a\b', 'utf-8'), bytea)
