转储 Postgres Sql 时转义文本内的引号

发布于 2024-11-03 11:06:38 字数 337 浏览 0 评论 0原文

假设我的表格是：

  id    text
+-----+----------+
  123 | foo bar
  321 | bar "baz"

转储时有什么办法可以转义“baz”周围的引号吗？

我的查询采用以下形式：

SELECT text FROM aTable WHERE ...

我希望输出为：

foo bar
bar \"baz\"

而不是：

foo bar
bar baz

原文

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?

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

南笙 2024-11-10 11:06:38

您可能想使用 replace ：

SELECT REPLACE(text, '"', E'\\"') FROM aTable WHERE ...

您需要转义转义字符以获得文字反斜杠（因此是双反斜杠），并在替换字符串上使用“E”前缀以获得正确的转义语法。

更新：由于 a_horse_with_no_name 一贯的严格性（顺便说一句，这是一件好事），我们有了一个不需要额外反斜杠或非标准“E”前缀的解决方案：

set standard_conforming_strings = on;
SELECT REPLACE(text, '"', '\"') FROM aTable WHERE ...

standard_conforming_strings 选项告诉 PostgreSQL 对 SQL 字符串使用标准语法：

这控制普通字符串文字（'...'）是否按照 SQL 标准中的规定按字面处理反斜杠。

这也会影响您的 \ x5C转义：

如果配置参数 standard_conforming_strings 关闭，则 PostgreSQL 会识别常规字符串常量和转义字符串常量中的反斜杠转义。这是为了向后兼容历史行为，其中反斜杠转义始终被识别。

You probably want to use 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.

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:

set standard_conforming_strings = on;
SELECT REPLACE(text, '"', '\"') FROM aTable WHERE ...

The standard_conforming_strings option tells PostgreSQL to use standard syntax for SQL strings:

This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard.

This would also impact your \x5C escape:

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.

回复收藏 0 原文

最笨的告白 2024-11-10 11:06:38

您可以使用以下形式的 COPY 命令：

COPY (SELECT * FROM table) TO ... WITH FORMAT 'CSV', ESCAPE '<WHATEVER ESCAPE CHARACTER YOU WANT>'

如

您可能不需要执行任何操作，因为在某些情况下，您的 QUOTE 选项将自动加倍。请参阅参考链接的示例。除了 SELECT 之外，您还可以使用 VALUES。不需要进一步的数据处理。

假设您使用的是 7.3 或更高版本。 7.3 和 9.0 之间的语法略有不同，因此请参阅相应的文档。

You can use the following incarnation of the COPY command:

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 QUOTE option will be doubled automatically. Please consult examples for the referenced link. You can also use VALUES in addition to SELECT. No further data mangling should be necessary.

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.

回复收藏 0 原文

~没有更多了~