PostgreSQL positional argument must be used in order

[radhus]

We use Postgres dialect and it seems like positional arguments in a query must be used in the order they are defined, i.e. $1 must be used before $2.

Example test where the second query fails:

func helloWorldFromPostgreSQL(projectId, instanceId, databaseId string) error {
	ctx := context.Background()
	db, err := sql.Open("spanner", fmt.Sprintf("projects/%s/instances/%s/databases/%s", projectId, instanceId, databaseId))
	if err != nil {
		return fmt.Errorf("failed to open database connection: %v", err)
	}
	defer db.Close()

	rows, err := db.QueryContext(
		ctx,
		"SELECT $1::varchar as message, $2::integer as number",
		"Hello World from Spanner PostgreSQL",
		42,
	)
	if err != nil {
		return fmt.Errorf("failed to execute query: %v", err)
	}
	defer rows.Close()

	var msg string
	var num int64
	for rows.Next() {
		if err := rows.Scan(&msg, &num); err != nil {
			return fmt.Errorf("failed to scan row values: %v", err)
		}
		fmt.Printf("%s: %d\n", msg, num)
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("failed to execute query: %v", err)
	}

	rows, err = db.QueryContext(
		ctx,
		"SELECT $2::integer as number, $1::varchar as message",
		"Hello World from Spanner PostgreSQL",
		42,
	)
	if err != nil {
		return fmt.Errorf("failed to execute query: %v", err)
	}
	defer rows.Close()

	for rows.Next() {
		if err := rows.Scan(); err != nil {
			return fmt.Errorf("failed to scan row values: %v", err)
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("failed to execute query: %v", err)
	}
	return nil
}

func main() {
	examples.RunSampleOnEmulatorWithDialect(helloWorldFromPostgreSQL, databasepb.DatabaseDialect_POSTGRESQL)
}

Is this a documented limitation? Some query builder that we're using creates queries like this, and we'd need to fix them if so 😅

Thanks!

Environment details

• Programming language: Go
• OS: macOS
• Language runtime version: Go 1.25.5
• Package version: latest