Create a Table with a Dataframe

Derive DDL using the data classes of each field in a dataframe. The map between the R data classes and the Postgresql data types can be found at renderCreateTableFromDF. The dataframe can then be appended to the table using appendTable. This method is favorable to a direct call to writeTable because in some cases, future appends to the table may not adhere to the data definitions created at the time of writing. For example, writeTable defaults to VARCHAR(255) for all character classes whereas future appends may contain text greater than 255 characters, resulting in error. This function rolls all character classes to TEXT data types instead.

create_table_from_df(
  conn,
  conn_fun,
  schema,
  table_name,
  if_not_exists = TRUE,
  data,
  verbose = TRUE,
  render_sql = TRUE
)

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
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:02]	Dropping test_schema.test_table...
#> Error in check_conn_status(conn = conn): object 'conn' not found

# Write the same table using create_table() instead
create_table(conn = conn,
             schema = "test_schema",
             table_name = "test_table_b",
             if_not_exists = TRUE,
             A = "integer",
             B = "varchar(1)")
#> Error in check_conn_status(conn = conn): object 'conn' not found

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

# Under the hood is the draft_create_table()
draft_create_table(schema = "test_schema",
                   table_name = "test_table_b",
                   A = "integer",
                   B = "varchar(1)")
#> [1] "\n                                        CREATE TABLE IF NOT EXISTS test_schema.test_table_b (\n                                                A integer,\nB varchar(1)\n                                        );\n                                        "

# The DDL can be automatically discerned using create_table_from_df()
create_table_from_df(conn = conn,
                     schema = "test_schema",
                     table_name = "test_table_c",
                     data = data.frame(A = 1:3, B = letters[1:3]))
#> Warning: Unknown levels in `f`: POSIXct, POSIXt, Date, double, numeric
#> Error in check_conn_status(conn = conn): object 'conn' not found

# Under the hood is the draft_create_table_from_df()
draft_create_table_from_df(schema = "test_schema",
                           table_name = "test_table_c",
                           data = data.frame(A = 1:3, B = letters[1:3]))
#> Warning: Unknown levels in `f`: POSIXct, POSIXt, Date, double, numeric
#> [1] "\n                                        CREATE TABLE IF NOT EXISTS test_schema.test_table_c (\n                                                A bigint,\nB text\n                                        );\n                                        "

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:02]	Postgres connection was already closed

See also

Examples