SQL 转储Postgres Sql时在文本中转义引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5785219/
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
Escaping quotes inside text when dumping Postgres Sql
提问by Marco
Let's say my table is:
假设我的表是：
id text
+-----+----------+
123 | foo bar
321 | bar "baz"
Is there any way to escape those quotes around 'baz' when dumping?
倾倒时有什么办法可以逃避'baz'周围的那些引号吗？
My query is in the form:
我的查询格式如下：
SELECT text FROM aTable WHERE ...
And I would like the output to be:
我希望输出是：
foo bar
bar \"baz\"
rather than:
而不是：
foo bar
bar baz
采纳答案by mu is too short
You probably want to use replace:
你可能想使用replace：
SELECT REPLACE(text, '"', E'\"') FROM aTable WHERE ...
You'll need to escape your escape character to get a literal backslash (hence the doubled backslash) and use the "E" prefix on the replacement string to get the right escape syntax.
您需要对转义字符进行转义以获得文字反斜杠（因此是双反斜杠），并在替换字符串上使用“E”前缀来获得正确的转义语法。
UPDATE: And thanks to a_horse_with_no_name's usual strictness (a good thing BTW), we have a solution that doesn't need the extra backslash or non-standard "E" prefix:
更新：感谢 a_horse_with_no_name 通常的严格性（顺便说一句，一件好事），我们有一个不需要额外反斜杠或非标准“E”前缀的解决方案：
set standard_conforming_strings = on;
SELECT REPLACE(text, '"', '\"') FROM aTable WHERE ...
The standard_conforming_stringsoption tells PostgreSQL to use standard syntax for SQL strings:
该standard_conforming_strings选项告诉 PostgreSQL 对 SQL 字符串使用标准语法：
This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard.
这控制普通字符串文字 ('...') 是否按照 SQL 标准中的规定逐字处理反斜杠。
This would also impact your \x5Cescape:
这也会影响你的\x5C逃生：
If the configuration parameter standard_conforming_strings is off, then PostgreSQL recognizes backslash escapes in both regular and escape string constants. This is for backward compatibility with the historical behavior, where backslash escapes were always recognized.
如果配置参数 standard_conforming_strings 关闭，那么 PostgreSQL 会识别常规和转义字符串常量中的反斜杠转义符。这是为了与历史行为向后兼容，其中反斜杠转义总是被识别。
回答by dawebber
You can use the following incarnation of the COPYcommand:
您可以使用以下COPY命令的化身：
COPY (SELECT * FROM table) TO ... WITH FORMAT 'CSV', ESCAPE '<WHATEVER ESCAPE CHARACTER YOU WANT>'
as described here.
如此处所述。
You might not have to do anything, as in some cases your QUOTEoption will be doubled automatically. Please consult examples for the referenced link. You can also use VALUESin addition to SELECT. No further data mangling should be necessary.
您可能不必做任何事情，因为在某些情况下，您的QUOTE选择会自动加倍。请查阅参考链接的示例。您还可以使用VALUES除SELECT。不需要进一步的数据修改。
This is assuming you are using 7.3 or higher. The syntax is slightly different between 7.3 and 9.0, so please consult the appropriate docs.
这是假设您使用的是 7.3 或更高版本。7.3 和 9.0 之间的语法略有不同，因此请查阅相应的文档。
