search

go-pg / pg

Golang ORM with focus on PostgreSQL features and performance
https://pg.uptrace.dev/
BSD 2-Clause "Simplified" License
5.67k stars 404 forks source link

Do we support select to insert #1972

Open cryptodogge opened 1 year ago

cryptodogge commented 1 year ago

I have a need to do something like INSERT INTO foo (x, y) VALUES ( (SELECT b1.id FROM boo b1 WHERE …), 3 );, do we support this function in gopg?

elliotcourant commented 1 year ago

I believe it can do a variation of that via https://pg.uptrace.dev/queries/#insert-from-select

However, the query you show there where only one value is derived from the subquery and other values are provided outright might be a bit odd to build.

Tinkering around I don't think that specific query is possible to build using the ORM functions.

It would probably be easier to build the select query using go-pg's ORM, but then convert it to a string and then build the insert query by hand.

The proper way to go about this use case with go-pg through would be to issue the select query first, then issue the insert query with its result as a value on your struct.

This does cause 2 round trips to the database, but won't be significantly different at all in performance besides network latency.

If the value of the select sub-query is time sensitive and could change between when it was read and when the insert is performed then you would need to perform them in a serializable transaction, but may run into serialization issues if this is the case.