如何使用dbReadTable指定变量类型，避免丢失前导零？

Great question—losing leading zeros when numeric columns get coerced is a super common gotcha with database reads. The dbReadTable() function from the DBI package has a built-in parameter to explicitly set column types, so you can force your target variable to read as character or factor instead of numeric.

Solution 1: Use the col.types Parameter #

The col.types argument accepts a named list where you map database column names to the R data type you want. Here's how to apply it to your code:

library(odbc)
library(DBI)

# Define your table ID as before
Id_test <- Id(schema = "Eelke", table = "test")

# Specify the column type for your target variable (replace "your_target_column" with the actual column name)
raw <- dbReadTable(
  con, 
  Id_test,
  col.types = list(your_target_column = "character")  # Use "factor" instead if you need factor type
)

Key Notes: #

• Match column names exactly: Make sure the name in the list matches the column name in your database (case sensitivity depends on your database system—e.g., PostgreSQL is case-sensitive if columns were created with quoted names).
• Multiple columns: If you need to set types for multiple columns, expand the list:

  col.types = list(
    column1 = "character",
    column2 = "factor",
    column3 = "integer"
  )
  

• Verify the result: After reading, use str(raw) to confirm your target column is now chr or fctr—your leading zeros should be preserved.

Alternative: Explicit SQL Cast (If col.types Doesn’t Work) #

In rare cases where col.types doesn’t behave as expected (e.g., with certain database drivers), you can use dbGetQuery() to write a SQL query that casts the column to a string type directly:

raw <- dbGetQuery(
  con,
  "SELECT CAST(your_target_column AS VARCHAR) AS your_target_column, * FROM Eelke.test"
)

Just make sure to replace your_target_column with the actual name, and adjust the SQL cast syntax to match your database (e.g., NVARCHAR for SQL Server, TEXT for MySQL).

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