# Create a PostgreSQL Table from a DataFrame or CSV with Schema Inference

**URL:** <https://discourse.julialang.org/t/create-a-postgresql-table-from-a-dataframe-or-csv-with-schema-inference/77328>\
**Category:** New to Julia\
**Tags:** question, dataframes, postgresql\
**Created:** [March 2, 2022, 11:49pm UTC](https://discourse.julialang.org/t/create-a-postgresql-table-from-a-dataframe-or-csv-with-schema-inference/77328 "2022-03-02T23:49:32Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![dliden](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dliden/32/34270_2.png) [@dliden](https://discourse.julialang.org/u/dliden)\
**Post date:** [March 2, 2022, 11:49pm UTC](https://discourse.julialang.org/t/create-a-postgresql-table-from-a-dataframe-or-csv-with-schema-inference/77328/1 "2022-03-02T23:49:33Z")

</div>

I am interested in finding a good method for creating a new table in a PostgreSQL database from a CSV or DataFrame without explicitly identifying the column types, much in the way that the Pandas `DataFrame.to_sql()` works if you attempt to load data to a table that does not exist.

I’ve read through a few related posts (e.g. [this one](https://discourse.julialang.org/t/given-a-postgressql-jl-connection-string-whats-the-easiest-way-to-create-a-table-in-the-database-by-copying-a-dataframe-into-it/68131) and [this one](https://discourse.julialang.org/t/how-to-create-a-table-in-a-database-using-dataframes/75759)) addressing similar questions, but all of the solutions I’ve seen so far require that the table exists already.

I’m pretty much exclusively using the `LibPQ` package for talking to the database at this point but I’m also happy to use any other packages that might offer this functionality! (Or write something myself if there aren’t ready-made solutions).

Any pointers on where to look/possible workarounds/etc. are appreciated!

---

<div class="post-metadata">

**Author:** ![jd-foster](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jd-foster/32/35824_2.png) [@jd-foster](https://discourse.julialang.org/u/jd-foster)\
**Post date:** [March 3, 2022, 3:06am UTC](https://discourse.julialang.org/t/create-a-postgresql-table-from-a-dataframe-or-csv-with-schema-inference/77328/2 "2022-03-03T03:06:06Z")

</div>

Welcome to Julia discourse!

Here is a work-around that uses an in-memory SQLite database to generate the schema:

```julia
using DataFrames, SQLite, LibPQ
# An example test dataframe:
df = DataFrame(A = 1:3, B = [2.0, -1.1, 2.8], C = ["p","q","r"])
# Get the table schema:
table_sch = Tables.schema(df)
# Create an in-memory SQLite database:
db = SQLite.DB()
# Just create the schema in the SQLite database:
SQLite.createtable!(db, "mytable", Tables.schema(df))
# Now get back the generated SQL CREATE statement:
res = DataFrame(DBInterface.execute(db, "SELECT sql FROM sqlite_master;"))
# alternative to just get a specific name:
# res = DataFrame(DBInterface.execute(db, "SELECT * FROM sqlite_master WHERE name='mytable';"))

str = res[1,:sql] # or change the first index of `1` to other indices (e.g for multiple tables)
# Now use the string to create a table in LibPQ:
conn = LibPQ.Connection("dbname=postgres") # replace postgres with your db name.
result = LibPQ.execute(conn, str)

```

and from here, you can use the function from this post

> [@How to Create a Table in a DataBase Using DataFrames?](https://discourse.julialang.org/t/how-to-create-a-table-in-a-database-using-dataframes/75759/3):
>
> LibPQ itself doesn’t provider a wrapper for writing tables, here’s an example one that I’ve written (with limited testing, so please use at your own caution!) function load\_table!(conn, df, tablename, columns=names(df)) table\_column\_names = join(string.(columns), ", ") placeholders = join(("\$$num" for num in 1:length(columns)), ", ") data = select(df, columns) try LibPQ.execute(conn, "BEGIN;") LibPQ.load!( data, conn, "INSERT …

to load the table into the database:

```julia
load_table!(conn, df, "mytable")

```

---

<div class="post-metadata">

**Author:** ![dliden](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dliden/32/34270_2.png) [@dliden](https://discourse.julialang.org/u/dliden)\
**Post date:** [March 3, 2022, 5:19am UTC](https://discourse.julialang.org/t/create-a-postgresql-table-from-a-dataframe-or-csv-with-schema-inference/77328/3 "2022-03-03T05:19:03Z")

</div>

Thank you! I’ll give that a try.