gpt4 book ai didi

sql - postgres dblink转义单引号

转载 作者:行者123 更新时间:2023-11-29 11:14:23 24 4
gpt4 key购买 nike

相关链接:

这是我的错误:

ERROR:  type "e" does not exist

这是我的查询:

SELECT *
FROM dblink('host=theHostName port=1234 dbname=theDBName user=theUser password=thePassword',
E'SELECT field_1,
CASE WHEN field_2 IS NOT NULL
THEN \'inactive\' ELSE \'active\'
END AS field_status
FROM the_table
')
AS linkresults(field_1 varchar(20),field_2 varchar(8))

如果我使用双引号,删除单引号的反斜杠转义并删除 SELECT 语句前的 E

SELECT *
FROM dblink('host=theHostName port=1234 dbname=theDBName user=theUser password=thePassword',
"SELECT field_1,
CASE WHEN field_2 IS NOT NULL
THEN 'inactive' ELSE 'active'
END AS field_status
FROM the_table
")
AS linkresults(field_1 varchar(20),field_2 varchar(8))

我明白了:

NOTICE:  identifier "SELECT ..." will be truncated

而且由于我的查询已被截断,我也得到了错误。

我以前用dblink逃过,有没有服务器设置或者我需要配置的东西?

我知道如果我在 sql server 本身上运行该查询,但使用 dblink 则不行。有什么想法吗?

Postgres 版本 8.4

最佳答案

尝试将 \'inactive\' 替换为 ''inactive'' -- 注意:两个单引号

   SELECT *
FROM dblink('host=theHostName port=1234 dbname=theDBName user=theUser password=thePassword',
'SELECT field_1,
CASE WHEN field_2 IS NOT NULL
THEN ''inactive'' ELSE ''active''
END AS field_status
FROM the_table
')

AS linkresults(field_1 varchar(20),field_2 varchar(8))

替代(先前)解决方案

   SELECT *
FROM dblink('host=theHostName port=1234 dbname=theDBName user=theUser password=thePassword',
'SELECT field_1,
CASE WHEN field_2 IS NOT NULL
THEN E\'inactive\' ELSE E\'active\'
END AS field_status
FROM the_table
')

AS linkresults(field_1 varchar(20),field_2 varchar(8))

关于sql - postgres dblink转义单引号,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/6615732/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com