PostgreSQL :: insert into A (id, val) VALUES ((select x from y where a = ?), 'bbb')

[jdevelop]

package testing

import (
	"database/sql"
	"fmt"
	"testing"

	sq "github.com/Masterminds/squirrel"
	_ "github.com/lib/pq"
	"github.com/stretchr/testify/require"
)

var psql = sq.StatementBuilder.PlaceholderFormat(sq.Dollar)

func TestInsertSelect(t *testing.T) {
	driver, path := "postgres", "host=localhost sslmode=disable port=5432 user=postgres dbname=probes password=docker connect_timeout=10"
	db, err := sql.Open(driver, path)
	require.NoError(t, err)
	require.NoError(t, db.Ping())
	for _, tbl := range []string{"A", "B"} {
		_, err = db.Exec(fmt.Sprintf("DROP TABLE IF EXISTS %s", tbl))
		require.NoError(t, err)
	}
	_, err = db.Exec("CREATE TABLE A(id int, val varchar)")
	require.NoError(t, err)
	_, err = db.Exec("CREATE TABLE B(id int, val varchar)")
	require.NoError(t, err)
	for i, val := range []string{"xyz", "aab"} {
		_, err = psql.
			Insert("B").
			Columns("id", "val").
			Values(i, val).RunWith(db).Exec()
		require.NoError(t, err)
	}
	mkVsb := psql.Select("id").From("B").Where("val = ?", "xyz").Prefix("(").Suffix(")")
	stmt := psql.
		Insert("A").
		Columns("id", "val").
		Values(mkVsb, "aaa")
	t.Log(stmt.ToSql())
	_, err = stmt.RunWith(db).Exec()
	require.NoError(t, err)
}

The result is failure because the parameters are not substituted properly:

=== RUN   TestInsertSelect
    insertselect_test.go:40: INSERT INTO A (id,val) VALUES (( SELECT id FROM B WHERE val = $1 ),$1) [xyz aaa] <nil>
    insertselect_test.go:42: 
                Error Trace:    insertselect_test.go:42
                Error:          Received unexpected error:
                                pq: got 2 parameters but the statement requires 1
                Test:           TestInsertSelect
--- FAIL: TestInsertSelect (0.06s)
FAIL
FAIL    sample  0.061s
FAIL

[lann]

This looks like another manifestation of #128, which was fixed for WHERE clauses in #129. Same (annoying) workaround should apply: mkVsb := psql.Select -> mkVsb := sq.Select (i.e. use the default PlaceholderFormat(sq.Question) for nested statements). `