Draft SQL to Create a Table

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

list2 render

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

Other draft functions: draft_base(), draft_create_table_from_df(), draft_in(), draft_join(), draft_limit(), draft_random(), draft_table_path(), draft_where_in(), draft_where_like(), draft_where_lower_in(), draft_where_lower_like(), draft_where_lower_not_in(), draft_where_not_in(), paste_wheres(), table.path()

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