使用RPostgreSQL/DBI连接PostgreSQL时表不存在报错的排查

Hey there, let's work through the two issues you've run into with R 3.6.3, PostgreSQL 12, and the RPostgreSQL package:

Issue 1: "relation does not exist" error for ebird_sel_spt #

The root cause here is that your ebird_sel_spt table lives in the p_loc schema, but when you connect to PostgreSQL without specifying a schema, it only searches the schemas listed in your connection's search_path (usually just public by default). That's why direct references to the table name fail, even though dbListTables(con) shows it (since dbListTables returns all tables across all schemas).

Fixes for this: #

• Option 1: Explicitly specify the schema in your queries
  This is the method you already got working. Just prepend the schema name to the table name:

  df <- dbGetQuery(con, "SELECT * FROM p_loc.ebird_sel_spt LIMIT 1")
  

• Option 2: Add the p_loc schema to your connection's search path
  This way you can use the table name directly without specifying the schema every time:

  # Update the search path to include both public and p_loc
  dbSendQuery(con, "SET search_path TO public, p_loc;")
  # Now you can query directly
  df <- dbGetQuery(con, "SELECT * FROM ebird_sel_spt LIMIT 1")
  

• Option 3: Use the schema+table vector for existence checks
  When verifying if the table exists, pass a vector with the schema and table name to dbExistsTable:

  dbExistsTable(con, c("p_loc", "ebird_sel_spt")) # Returns TRUE
  

Issue 2: Unrecognized PostgreSQL geometry field warning #

The warning about the unrecognized geometry field (id:29533) happens because the base RPostgreSQL driver doesn't have built-in support for PostgreSQL's spatial geometry types. Here's how to handle this:

If you don't need the geometry column: #

Simply exclude it from your query to avoid the warning:

# Replace with the actual column names you need, omitting the geometry column
df <- dbGetQuery(con, "SELECT col1, col2, col3, ... FROM p_loc.ebird_sel_spt LIMIT 1")

If you need to work with the spatial data: #

Use the sf package, which is designed for spatial data in R and has native support for PostgreSQL's PostGIS geometry types. Here's how:

1. First install and load the package:

   install.packages("sf")
   library(sf)
   

2. Read the table directly with st_read—this will automatically convert the geometry column into an sf spatial object:

   sf_df <- st_read(con, query = "SELECT * FROM p_loc.ebird_sel_spt LIMIT 1")
   

This not only eliminates the warning but also gives you access to all of sf's spatial analysis tools, which is way more useful than handling raw geometry data manually.

Bonus: About the RPostgreSQL version warning #

The message "package ‘RPostgreSQL’ was built under R version 3.6.2" is just a compatibility heads-up—it shouldn't break your code. If you want to get rid of it, you can reinstall RPostgreSQL to ensure it's compiled for your exact R version:

install.packages("RPostgreSQL")

内容的提问来源于stack exchange，提问作者John Polo