Insert into postgres from array of dictionaries

General Usage
1

I’m trying to use LibPQ to insert data into a Postgres database. I have the data as an array of dictionaries. I’m trying to figure out the correct way to insert this but the documentation and examples are a little bit light.

Can someone provide an example for using an array of dictionaries to insert using LibPQ where the keys are used as the insert field names?

2

I’m not aware of that feature for LibPQ, the documentation only talks about numbered arguments. Though you could handle that with a couple simple helpers, such as:

# Create the insert query based on the first dict of the array
function insert_query(table_name::String, args::Array{Dict{String, Any},1})
"""INSERT INTO $(table_name) ($(join(keys(args[1]), ", ") ))
   VALUES ($(join(["\$$(i)" for i in 1:length(args[1])], ", ")))"""
end

# Convert from dict to named tuples (since array of named tuples is implemented in Tables.jl)
prepare(args::Array{Dict{String, Any},1}) = NamedTuple{Tuple(Symbol.(keys(args[1])))}.(values.(args))

LibPQ.load!(prepare(args), c, insert_query("table_name", args))
1 Like
3

Thanks, I’ll give it a shot!

4

by the way, it’s not clear from the documentation, but does LibPQ handle timestamp insertions natively? Currently, I’m converting to a string and then using the TO_TIMESTAMP insert statement but perhaps LibPQ is smarter than I’m giving it credit for, but I don’t see anything in the documentation that might suggest this is natively supported.

Any ideas and perhaps some help with some sample syntax on how that would work including handling of timezones?

5

I believe the code below should work normally for timestamp and timestamp with time zone (using TimeZones.jl package) respectively.

execute(c, "INSERT INTO test_date VALUES (\$1, \$2)", [Dates.now(), string(ZonedDateTime(2014, 1, 1, tz"Europe/Warsaw"))])