search

dylex / postgresql-typed

Haskell PostgreSQL library with compile-time type inference
http://hackage.haskell.org/package/postgresql-typed
Other
84 stars 12 forks source link

Inserting multiple rows in a single SQL statement #10

Closed jwoudenberg closed 6 years ago

jwoudenberg commented 6 years ago

This is probably something extremely basic, and I'm sorry for bothering you with it. I'm looking for a way to insert a [Thing] into a things table as a single sql statement. Ideally, I'd like to execute a statement like this in SQL:

INSERT INTO things (id, name)
VALUES (1, "one thing"), (2, "another thing");

I'm currently run a quasi-quoted statement like the following once for every item in the list of Things I want to insert:

INSERT INTO things (id, name)
VALUES (${id}, ${name});

This is a N+1 query, and I was hoping there might be a better way to do this. I'd be really appreciative for any tip pointing me in the right direction!

dylex commented 6 years ago

Unless you know how many things there are ahead of time, there's not currently a good type-safe way of doing this in a single statement. However, running a prepared insert in a loop is not too bad in many cases, because it's only one round-trip per item.

You could also of course construct a dynamic query (just as you'd do in any other DBI) using pgSafeLiteral or some such.

It is hypothetically possible to do this by combining the PGArray and PGRelation models with the unnest function, but I have never tried this, and given the type of unnest you probably lose type safety as a result anyway, but it would be an interesting exercise, and make for generally clean syntax.

There is actually a way of doing this with some greater type safety with the backend Copy protocol, which includes type information, but I've not implemented it, and not exactly sure how to in the general case since there's no equivalent to prepare-but-don't-execute for copies. If there's interest, though, I could look into this at some point. (You can still use the normal non-type-safe COPY interface too, of course.)

jwoudenberg commented 6 years ago

Wow, what an expansive answer dylex! That's a lot of good avenues for me to explore. Thank you!

jwoudenberg commented 6 years ago

Having tried using unnest(), that seems to work fine. Going to close this issue. Thank you for your help!