Returns an SQL string that describes the SQL data type to be used for an object. The default implementation of this generic determines the SQL type of an R object according to the SQL 92 specification, which may serve as a starting point for driver implementations. DBI also provides an implementation for data.frame which will return a character vector giving the type for each column in the dataframe.
Methods in other packages
This documentation page describes the generics. Refer to the documentation pages linked below for the documentation for the methods that are implemented in various backend packages.
adbi::dbDataType("AdbiConnection", "ANY")adbi::dbDataType("AdbiDriver", "ANY")bigrquery::dbDataType("BigQueryConnection", "ANY")bigrquery::dbDataType("BigQueryDriver", "ANY")duckdb::dbDataType("duckdb_connection", "ANY")duckdb::dbDataType("duckdb_driver", "ANY")pool::dbDataType("Pool", "ANY")RH2::dbDataType("H2Connection", "ANY")RJDBC::dbDataType("JDBCConnection", "ANY")RMariaDB::dbDataType("MariaDBConnection", "ANY")RMariaDB::dbDataType("MariaDBDriver", "ANY")RMySQL::dbDataType("MySQLConnection", "ANY")RMySQL::dbDataType("MySQLDriver", "ANY")RPostgres::dbDataType("PqConnection", "ANY")RPostgres::dbDataType("PqDriver", "ANY")RPostgreSQL::dbDataType("PostgreSQLObject", "ANY")RPresto::dbDataType("PrestoDriver", "ANY")RSQLite::dbDataType("SQLiteConnection", "ANY")RSQLite::dbDataType("SQLiteDriver", "ANY")sergeant::dbDataType("DrillConnection", "ANY")sparklyr::dbDataType("spark_connection", "ANY")
Arguments
- dbObj
A object inheriting from DBIDriver or DBIConnection
- obj
An R object whose SQL type we want to determine.
- ...
Other arguments passed on to methods.
Value
dbDataType() returns the SQL type that corresponds to the obj argument
as a non-empty
character string.
For data frames, a character vector with one element per column
is returned.
Details
The data types supported by databases are different than the data types in R, but the mapping between the primitive types is straightforward:
Any of the many fixed and varying length character types are mapped to character vectors
Fixed-precision (non-IEEE) numbers are mapped into either numeric or integer vectors.
Notice that many DBMS do not follow IEEE arithmetic, so there are potential problems with under/overflows and loss of precision.
Specification
The backend can override the DBI::dbDataType() generic
for its driver class.
This generic expects an arbitrary object as second argument.
To query the values returned by the default implementation,
run example(dbDataType, package = "DBI").
If the backend needs to override this generic,
it must accept all basic R data types as its second argument, namely
logical,
integer,
numeric,
character,
dates (see Dates),
date-time (see DateTimeClasses),
and difftime.
If the database supports blobs,
this method also must accept lists of raw vectors,
and blob::blob objects.
As-is objects (i.e., wrapped by I()) must be
supported and return the same results as their unwrapped counterparts.
The SQL data type for factor and
ordered is the same as for character.
The behavior for other object types is not specified.
All data types returned by dbDataType() are usable in an SQL statement
of the form
"CREATE TABLE test (a ...)".
See also
Other DBIDriver generics:
DBIDriver-class,
dbCanConnect(),
dbConnect(),
dbDriver(),
dbGetInfo(),
dbIsReadOnly(),
dbIsValid(),
dbListConnections()
Other DBIConnection generics:
DBIConnection-class,
dbAppendTable(),
dbAppendTableArrow(),
dbCreateTable(),
dbCreateTableArrow(),
dbDisconnect(),
dbExecute(),
dbExistsTable(),
dbGetException(),
dbGetInfo(),
dbGetQuery(),
dbGetQueryArrow(),
dbIsReadOnly(),
dbIsValid(),
dbListFields(),
dbListObjects(),
dbListResults(),
dbListTables(),
dbQuoteIdentifier(),
dbReadTable(),
dbReadTableArrow(),
dbRemoveTable(),
dbSendQuery(),
dbSendQueryArrow(),
dbSendStatement(),
dbUnquoteIdentifier(),
dbWriteTable(),
dbWriteTableArrow()
Other DBIConnector generics:
DBIConnector-class,
dbConnect(),
dbGetConnectArgs(),
dbIsReadOnly()
Examples
dbDataType(ANSI(), 1:5)
#> [1] "INT"
dbDataType(ANSI(), 1)
#> [1] "DOUBLE"
dbDataType(ANSI(), TRUE)
#> [1] "SMALLINT"
dbDataType(ANSI(), Sys.Date())
#> [1] "DATE"
dbDataType(ANSI(), Sys.time())
#> [1] "TIMESTAMP"
dbDataType(ANSI(), Sys.time() - as.POSIXct(Sys.Date()))
#> [1] "TIME"
dbDataType(ANSI(), c("x", "abc"))
#> [1] "TEXT"
dbDataType(ANSI(), list(raw(10), raw(20)))
#> [1] "BLOB"
dbDataType(ANSI(), I(3))
#> [1] "DOUBLE"
dbDataType(ANSI(), iris)
#> Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> "DOUBLE" "DOUBLE" "DOUBLE" "DOUBLE" "TEXT"
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbDataType(con, 1:5)
#> [1] "INTEGER"
dbDataType(con, 1)
#> [1] "REAL"
dbDataType(con, TRUE)
#> [1] "INTEGER"
dbDataType(con, Sys.Date())
#> [1] "REAL"
dbDataType(con, Sys.time())
#> [1] "REAL"
dbDataType(con, Sys.time() - as.POSIXct(Sys.Date()))
#> [1] "REAL"
dbDataType(con, c("x", "abc"))
#> [1] "TEXT"
dbDataType(con, list(raw(10), raw(20)))
#> [1] "BLOB"
dbDataType(con, I(3))
#> [1] "REAL"
dbDataType(con, iris)
#> Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> "DOUBLE" "DOUBLE" "DOUBLE" "DOUBLE" "TEXT"
dbDisconnect(con)