R语言调用PostgreSQL子查询报错：连接参数无法识别的解决办法

Let's walk through what's causing your error and how to fix it properly.

What's Wrong with Your Original Code? #

Your core issue is that you're trying to nest a dbGetQuery() call directly inside paste0(). Here's why that breaks:

• dbGetQuery() returns a data frame, not plain text. When you stick that object into your SQL string, the database has no idea how to interpret it.
• You also have syntax gaps: your inner SQL query is missing a closing single quote, and the parentheses around the inner query aren't structured correctly for string concatenation.

Step-by-Step Fix #

Split the process into two clear, manageable steps: fetch the location IDs first, then build your outer query with those values.

1. Fetch location IDs separately
   Run the inner query on its own to get the location_id values you need. Make sure your SQL syntax is complete (note the closing single quote for city_name):

   # Get location IDs matching the target city
   location_results <- dbGetQuery(con, paste0("select location_id from postgres.location where city_name = '", cityname, "'"))
   

2. Convert results to a SQL-friendly string
   location_results is a data frame, so extract the column into a vector and format it for the IN clause:

   • If location_id is a numeric type:

     id_list <- paste(location_results$location_id, collapse = ", ")
     

   • If location_id is a character type, wrap each value in single quotes:

     id_list <- paste0("'", location_results$location_id, "'", collapse = ", ")
     

3. Build and run the outer query
   Now plug the formatted ID string into your outer SQL query:

   query2 <- dbGetQuery(con, paste0("select uniqueno from postgres.asset where location_id in (", id_list, ")"))
   

Safer Alternative: Parameterized Queries #

Direct string concatenation can lead to SQL injection risks, especially if cityname comes from user input. For a more secure approach, use parameterized queries:

# Get location IDs with parameterized input (avoids SQL injection)
location_results <- dbGetQuery(con, "select location_id from postgres.location where city_name = $1", params = list(cityname))

# Use PostgreSQL's ANY operator to pass the ID vector as a parameter
query2 <- dbGetQuery(con, "select uniqueno from postgres.asset where location_id = ANY($1)", params = list(location_results$location_id))

This method skips messy string handling and keeps your queries secure.

内容的提问来源于stack exchange，提问作者Tarun Das