Question: Build INSERT statement on SELECT from VALUES

Author: akolybelnikovCreated Sep 17, 2022Updated Feb 17, 2025

I have been trying to build a query which results into a statement like this:

sql

INSERT INTO geo_table (name, geog)
SELECT col1, st_makepoint(col2, col3)
FROM (VALUES ($1, $2, $3)) temp (col1, col2, col3);

but I haven't been able to bind the values in any way. The closest I'm getting is:

sql

psql := sq.StatementBuilder.PlaceholderFormat(sq.Dollar)
sql, args, _ := psql.Insert("geo_table").Columns("name", "geog").
Select(
    psql.Select("name", "st_makepoint(longitude, latitude)").
        FromSelect(sq.Select("name", "longitude", "latitude"), "temp")
).
Values(name, longitude, latitude).ToSql()

which produces:

INSERT INTO geo_table (name, geog)
SELECT name, ST_MakePoint(longitude, latitude)
FROM (SELECT name, longitude, latitude) AS temp

and then fails with ERROR: column \"name\" does not exist (SQLSTATE 42703) (justly).

Another option would be to put the VALUES into a WITH query, but I haven't yet been able to find a way to do it either.

Source: Masterminds/squirrel

View original on GitHub View discussion on GitHub