连字符表名周围的双引号失败 [英] double-quotes around hyphenated table name fails

问题描述

我们使用SaaS软件从云中获取我们的营销数据。当我尝试将查询结果转储到CSV文件中时

We use SaaS software to pull our marketing data from the cloud. When I attempt to dump the result of a query to a CSV file like so

psql my.server -p 5432 -U myuser -d mydb -c "\copy (SELECT 
c.*,cip.*,cipi.* FROM saas.contacts c LEFT JOIN 
saas."contacts__identity-profiles" cip ON 
cip."_sdc_source_key_vid" = c.vid LEFT JOIN 
saas."contacts__identity-profiles__identities" cipi ON 
cipi."_sdc_source_key_vid" = cip."_sdc_source_key_vid" AND 
cipi."_sdc_level_0_id" = cip."_sdc_level_0_id") to 
'/tmp/contacts.csv' DELIMITER ',' CSV HEADER "

它失败并出现以下错误

ERROR:  syntax error at or near "-"
LINE 1: ...EFT JOIN saas.contacts__identity-profiles ...

我知道Postgres要求我输入用双引号引起来的连字符表名，但这显然行不通。我已经尝试过

I know Postgres requires I put double-quotes around hyphenated table names but this is obviously not working. I have tried

• 在表名中使用连字符转义，例如 contacts__identity\-profiles 但无济于事


• 将表名周围的双引号加倍但无济于事。

• escaping the hyphen in the table name like so "contacts__identity\-profiles" but to no avail
• doubling the double-quotes around the table name but to no avail.

该问题似乎与在COPY命令中使用双引号有关。我能解决这个问题吗？

The issue seems related to the use of double-quotes inside the COPY command. Any way I can get around that?

推荐答案

您有双引号同时执行两项操作：

You have double quotes doing two separate things at once:

• 外部双引号用于 bash 将该命令传递给 psql 作为一个参数。


• 内部双引号用于PostgreSQL，以便您可以引用带连字符的标识符。

• The outer double quotes are for bash to pass that command to psql as one argument.
• The inner double quotes are for PostgreSQL so that you can quote your hyphenated identifiers.

您需要转义内部引号以使其通过 bash 并进入 psql ：

You need to escape the inner quotes to get them through bash and into psql:

psql my.server -p 5432 -U myuser -d mydb -c "... saas.\"contacts__identity-profiles\" ..."
# ----------------------------------------------------^^ ------------------------- ^^

等。将所有字符转义（从 bash ）将为您提供：

and so on. Escaping them all (from bash) would give you:

psql my.server -p 5432 -U myuser -d mydb -c "\copy (SELECT c.*,cip.*,cipi.* FROM saas.contacts c LEFT JOIN saas.\"contacts__identity-profiles\" cip ON cip.\"_sdc_source_key_vid\" = c.vid LEFT JOIN saas.\"contacts__identity-profiles__identities\" cipi ON cipi.\"_sdc_source_key_vid\" = cip.\"_sdc_source_key_vid\" AND cipi.\"_sdc_level_0_id\" = cip.\"_sdc_level_0_id\") to '/tmp/contacts.csv' DELIMITER ',' CSV HEADER "

这篇关于连字符表名周围的双引号失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！