Write a Table

Unlike the dropTable and renameTable functions, this function is a wrapper around the DatabaseConnector::dbWriteTable() function rather than one where a SQL statement is rendered using the SqlRender package. This function that converts all inputs to a dataframe, especially in cases where the input is a tibble, in which case an error would be thrown when writing.

write_table(
  conn,
  conn_fun,
  schema,
  table_name,
  data,
  drop_existing = FALSE,
  verbose = TRUE,
  render_sql = TRUE,
  render_only = FALSE,
  ...
)

Arguments

conn	Connection object
schema	The target schema for the operation.
data	A dataframe or tibble.
verbose	If TRUE, details on the activity are returned in the console, such as when the querying starting and was completed.
render_sql	If TRUE, the SQL statement for the operation is returned in the console.
...	Additional arguments passed to `DatabaseConnector::dbWriteTable()`

Examples

library(pg13)
create_test_schema <-
        function(conn) {

                if (!schema_exists(conn = conn,
                                   schema = "test_schema")) {

                        create_schema(conn = conn,
                                      schema = "test_schema")

                }
        }

conn <- local_connect(dbname = "pg13_test")
#> Error in rJava::.jcall(jdbcDriver, "Ljava/sql/Connection;", "connect",     as.character(url), p): org.postgresql.util.PSQLException: Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
create_test_schema(conn = conn)
#> Error in h(simpleError(msg, call)): error in evaluating the argument 'conn' in selecting a method for function 'dbGetQuery': object 'conn' not found

# Write a table without dropping
write_table(conn = conn,
            schema = "test_schema",
            table_name = "test_table2",
            drop_existing = FALSE,
            data = data.frame(A = 1:3, B = letters[1:3]))
#> Error in check_conn_status(conn = conn): object 'conn' not found

# Write a table with dropping
write_table(conn = conn,
            schema = "test_schema",
            table_name = "test_table",
            drop_existing = TRUE,
            data = data.frame(A = 1:3, B = letters[1:3]))
#> [2021-06-20 15:26:30]	Dropping test_schema.test_table...
#> Error in check_conn_status(conn = conn): object 'conn' not found


# Include the name of the dataframe object in the messages
test_data <-
        data.frame(A = 1:3, B = letters[1:3])
write_table(conn = conn,
            schema = "test_schema",
            table_name = "test_table3",
            drop_existing = TRUE,
            data = test_data)
#> [2021-06-20 15:26:30]	Dropping test_schema.test_table3...
#> Error in check_conn_status(conn = conn): object 'conn' not found

# Append a table
append_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             data = data.frame(A = 1:3, B = letters[1:3]))
#> Error in check_conn_status(conn = conn): object 'conn' not found

# Warning is returned if NAs are in the input data
append_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             data = data.frame(A = 1:3, B = rep(NA_character_, 3)))
#> Error in check_conn_status(conn = conn): object 'conn' not found

# Alert is returned if the input data contains 0 rows
test_data2 <-
        data.frame(A = 1,
                   B = 2)
test_data2 <- test_data2[-1,]
append_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             data = test_data2)
#> Error in check_conn_status(conn = conn): object 'conn' not found

# Message is returned if incoming data contains more than 0 rows
test_data <- read_table(conn = conn,
                           schema = "test_schema",
                           table = "test_table")
#> Error in read_table(conn = conn, schema = "test_schema", table = "test_table"): unused arguments (conn = conn, schema = "test_schema", table = "test_table")

test_data
#>   A B
#> 1 1 a
#> 2 2 b
#> 3 3 c

# Alert is returned if incoming data contains 0 rows
write_table(conn = conn,
            schema = "test_schema",
            table_name = "test_table4",
            drop_existing = TRUE,
            data = test_data2)
#> [2021-06-20 15:26:30]	Dropping test_schema.test_table4...
#> Error in check_conn_status(conn = conn): object 'conn' not found
test_data <- read_table(conn = conn,
                        schema = "test_schema",
                        table = "test_table4")
#> Error in read_table(conn = conn, schema = "test_schema", table = "test_table4"): unused arguments (conn = conn, schema = "test_schema", table = "test_table4")
test_data
#>   A B
#> 1 1 a
#> 2 2 b
#> 3 3 c

search_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             values = 1:3)
#> Error in search_table(conn = conn, schema = "test_schema", table = "test_table",     values = 1:3): could not find function "search_table"

search_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             values = "a")
#> Error in search_table(conn = conn, schema = "test_schema", table = "test_table",     values = "a"): could not find function "search_table"

search_table(conn = conn,
             schema = "test_schema",
             table = "test_table",
             values = c("A", "b", "C"),
             case_insensitive = FALSE)
#> Error in search_table(conn = conn, schema = "test_schema", table = "test_table",     values = c("A", "b", "C"), case_insensitive = FALSE): could not find function "search_table"

drop_table(conn = conn,
           schema = "test_schema",
           table = "test_table",
           if_exists = FALSE)
#> [2021-06-20 15:26:30]	Dropping test_schema.test_table...
#> Error in check_conn_status(conn = conn): object 'conn' not found

drop_table(conn = conn,
           schema = "test_schema",
           table = "test_table2",
           if_exists = FALSE)
#> [2021-06-20 15:26:30]	Dropping test_schema.test_table2...
#> Error in check_conn_status(conn = conn): object 'conn' not found

drop_schema(conn = conn,
            schema = "test_schema",
            cascade = TRUE)
#> Error in check_conn_status(conn = conn): object 'conn' not found

dc(conn = conn)
#> [2021-06-20 15:26:30]	Postgres connection was already closed

Arguments

See also

Examples