Search code examples
postgresqlpostgis

postgreSQL There is an entry for table "xxx", but it cannot be referenced from this part of the query

What is the proper way to write this query in postgreSQL? I am trying to normalize (i.e. standardize) an address. I can use pagc with no problem if I feed it a hardcoded address. However, I need to feed it an address parsed from parts. I see there are several similar questions here on stack overflow referencing the same error. These queries are complex and are all pretty different from mine, so I couldn't get to the solution from reading the other posts.

I have tried:

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;

This throws error:

syntax error at or near "home_address1" LINE 26: with full_address as (home_address1 || ','|| home_city |.

I have also tried:

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;

Error:

ERROR: invalid reference to FROM-clause entry for table "contacts" LINE 24: ...abbrev,addy.internal) FROM pagc_normalize_address(home_addre... ^ HINT: There is an entry for table "contacts", but it cannot be referenced from this part of the query. SQL state: 42P10 Character: 2297

Solution

  • The first query is gibberish, the second makes sense but fails because you cannot use a lateral reference to the updated table's columns in the FROM clause.

    Try a CTE like this:

    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;