Given a PostgresSQL.jl connection string, what's the easiest way to create a table in the database by copying a dataframe into it?

Specific Domains Data
1

I have tried

using ODBC

conn = ODBC.Connection("Driver={PostgreSQL Unicode(x64)};Server=db.bit.io;Port=5432;Database=bitdotio;Uid=xxx;Pwd=xxx;")

tbl = DataFrame(a=1:3)
ODBC.load(tbl, conn, "tbl1"; append=false)

But it gives error

ERROR: HY000: LOG: Error occurred when processing query: relation ""."tbl1" does not exist (error ID: e6555b01-5bf7-4801-9941-3cbc2067d6d7);

The LibPQ insert statement might work but I am trying to upload a largish file so I want to make it as efficient as possible.

2

You can use https://github.com/lungben/TableIO.jl to upload large data amounts to Postgres via LibPQ.jl.
It uses internally the Postgres COPY function to upload the data in CSV format (using CSV.jl), which is much more efficient than uploading with an SQL Insert, see https://github.com/lungben/TableIO.jl/blob/master/src/postgresql.jl#L20.

1 Like
3

I agree, you want to use COPY FROM stdin. This closed PR https://github.com/invenia/LibPQ.jl/pull/172 contains some examples. When providing a CSV, be sure to quote the fields accordingly.

Edit: There is also an example in the docs Home · LibPQ.jl

1 Like
4

Doesn’t work it says “tablename must only contain alphanumeric characters and underscores” which is not true for Postgres Tables

After fixing that function I get an error say the table doesn’t exist. I was looking for something that would create the table for me if it doesn’t exist. I guess i have to write some code now.

5

The CSV upload does not create a table, you need to do this beforehand.
Usually, for a “persistent” database like Postgres you have a fixed schema which you use to create your tables and you populate your data afterwards. Thus creating a new table at the same time as filling it is not a very common use case.

1 Like
6

no worries. I wrote a function to create the table.

7

Just for context; ODBC.jl doesn’t automatically create a table for you since it’s just a “middleware” and there isn’t a mechanism in the ODBC spec to generically create a table in any supporting database.

2 Likes