Compose query to create a simple table

Source: R/sqlCreateTable.R

Exposes an interface to simple CREATE TABLE commands. The default method is ANSI SQL 99 compliant. This method is mostly useful for backend implementers.

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.

sqlCreateTable(con, table, fields, row.names = NA, temporary = FALSE, ...)

Arguments

con

A database connection.

table

The table name, passed on to dbQuoteIdentifier(). Options are:

  • a character string with the unquoted DBMS table name, e.g. "table_name",

  • a call to Id() with components to the fully qualified table name, e.g. Id(schema = "my_schema", table = "table_name")

  • a call to SQL() with the quoted and fully qualified table name given verbatim, e.g. SQL('"my_schema"."table_name"')

fields

Either a character vector or a data frame.

A named character vector: Names are column names, values are types. Names are escaped with dbQuoteIdentifier(). Field types are unescaped.

A data frame: field types are generated using dbDataType().

row.names

Either TRUE, FALSE, NA or a string.

If TRUE, always translate row names to a column called "row_names". If FALSE, never translate row names. If NA, translate rownames only if they're a character vector.

A string is equivalent to TRUE, but allows you to override the default name.

For backward compatibility, NULL is equivalent to FALSE.

temporary

If TRUE, will generate a temporary table statement.

...

Other arguments used by individual methods.

Details

The row.names argument must be passed explicitly in order to avoid a compatibility warning. The default will be changed in a later release.

Examples

sqlCreateTable(ANSI(), "my-table", c(a = "integer", b = "text"))
#> Warning: Do not rely on the default value of the row.names argument for sqlCreateTable(), it will change in the future.
#> <SQL> CREATE TABLE "my-table" (
#>   "a" integer,
#>   "b" text
#> )
#> 
sqlCreateTable(ANSI(), "my-table", iris)
#> Warning: Do not rely on the default value of the row.names argument for sqlCreateTable(), it will change in the future.
#> <SQL> CREATE TABLE "my-table" (
#>   "Sepal.Length" DOUBLE,
#>   "Sepal.Width" DOUBLE,
#>   "Petal.Length" DOUBLE,
#>   "Petal.Width" DOUBLE,
#>   "Species" TEXT
#> )
#> 

# By default, character row names are converted to a row_names colum
sqlCreateTable(ANSI(), "mtcars", mtcars[, 1:5])
#> Warning: Do not rely on the default value of the row.names argument for sqlCreateTable(), it will change in the future.
#> <SQL> CREATE TABLE "mtcars" (
#>   "row_names" TEXT,
#>   "mpg" DOUBLE,
#>   "cyl" DOUBLE,
#>   "disp" DOUBLE,
#>   "hp" DOUBLE,
#>   "drat" DOUBLE
#> )
#> 
sqlCreateTable(ANSI(), "mtcars", mtcars[, 1:5], row.names = FALSE)
#> <SQL> CREATE TABLE "mtcars" (
#>   "mpg" DOUBLE,
#>   "cyl" DOUBLE,
#>   "disp" DOUBLE,
#>   "hp" DOUBLE,
#>   "drat" DOUBLE
#> )
#>