Draft a SQL that creates a table using DDL derived from a dataframe. Drafting is stopped if the given tablename or fields in the dataframe are a SQL reserve word.

draft_create_table(schema, table_name, if_not_exists = TRUE, ...)

Arguments

...

Named vector of field names and their corresponding data definition.

See also

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