create_table_from_df: Create a Table with a Dataframe

In patelm9/pg13: pg13 Package

Description

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.

Usage

create_table_from_df(
  conn,
  conn_fun = "pg13::local_connect()",
  schema,
  table_name,
  if_not_exists = TRUE,
  data,
  verbose = TRUE,
  render_sql = TRUE
)

See Also

Other create functions: create_schema(), create_table(), draft_create_table_from_df(), draft_create_table()

Other table functions: appendTable(), append_table(), create_table(), drop_all_staging_tables(), drop_table_batch(), drop_table(), read_table(), searchTable(), search_table(), write_staging_table(), write_table()

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")
create_test_schema(conn = conn)

# 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]))

# 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)")

append_table(conn = conn,
             schema = "test_schema",
             table = "test_table_b",
             data = data.frame(A = 1:3, B = letters[1:3]))

# Under the hood is the draft_create_table()
draft_create_table(schema = "test_schema",
                   table_name = "test_table_b",
                   A = "integer",
                   B = "varchar(1)")

# 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]))

# 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]))

drop_schema(conn = conn,
            schema = "test_schema",
            cascade = TRUE)

dc(conn = conn)

patelm9/pg13 documentation built on Dec. 26, 2021, 8:17 p.m.