用postgreSQL
编写此查询的正确方法是什么? 我正在尝试规范化(即标准化(地址。如果我给它一个硬编码的地址,我可以毫无问题地使用pagc
。但是,我需要向它提供从部分解析的地址。我看到这里有几个类似的问题,堆栈溢出引用了相同的错误。这些查询很复杂,都与我的查询有很大不同,所以我无法通过阅读其他帖子来获得解决方案。
我试过:
with full_address as (home_address1 || ','|| home_city ||','|| home_state ||,','|| home_zip) update contacts set (home_house_num, home_predirection, home_street_name, home_street_type,home_postdirection, home_unit_num) = (addy.address_alphanumeric,addy.predirabbrev,addy.streetname, addy.streettypeabbrev,addy.postdirabbrev,addy.internal) FROM pagc_normalize_address(full_address) AS addy where contact_id = 833826;
这将引发错误:
"home_address1"处或附近出现语法错误 第 26 行:full_address为 (home_address1 ||','||home_city |。
我也尝试过:
update contacts set (home_house_num, home_predirection, home_street_name, home_street_type,home_postdirection, home_unit_num) = (addy.address_alphanumeric,addy.predirabbrev,addy.streetname, addy.streettypeabbrev,addy.postdirabbrev,addy.internal) FROM pagc_normalize_address(home_address1 ||','||home_city||','||home_state||','||','||home_zip) AS addy where contact_id = 833826;
错误:
错误:对表"联系人"的 FROM 子句条目的引用无效 第 24 行:...缩写,addy.internal( 从 pagc_normalize_address(home_addre... ^ 提示:表"联系人"有一个条目,但不能从查询的这一部分引用它。 SQL 状态:42P10 字符: 2297
第一个查询是乱码,第二个查询有意义但失败,因为不能在 FROM
子句中使用对更新表列的横向引用。
尝试像这样使用 CTE:
WITH addy AS (
SELECT addy.* FROM
contacts
CROSS JOIN LATERAL
pagc_normalize_address(home_address1
|| ',' || home_city || ',' || home_state || ',' || ',' || home_zip) AS addy
WHERE contacts.contact_id = 833826
)
UPDATE contacts
SET (home_house_num, home_predirection, home_street_name, home_street_type,home_postdirection, home_unit_num)
= (addy.address_alphanumeric,addy.predirabbrev,addy.streetname, addy.streettypeabbrev,addy.postdirabbrev,addy.internal)
FROM addy
WHERE contact_id = 833826;