Can i use RETURNING in INSERT?

[bigBron]

Using the 'RETURNING' syntax to get incremented ids in PostgresQL, how does it work in go-sqlbuilder？

INSERT INTO table (field) VALUES (a) RETURNING id;

[huandu]

It's recommended to build the SQL like this.

ib := sqlbuilder.PostgreSQL.NewInsertBuilder()
ib.InsertInto("table").Cols("field").Values("some value")

// Use Build to add "RETURNING id" as a postfix.
sql, args  := sqlbuilder.Build("$? RETURNING id;", ib)

println(sql)
println(args)

// Output:
// INSERT INTO table (field) VALUES (?) RETURNING id;
// [some value]

[KyleSanderson]

It's recommended to build the SQL like this.

ib := sqlbuilder.PostgreSQL.NewInsertBuilder()
ib.InsertInto("table").Cols("field").Values("some value")

// Use Build to add "RETURNING id" as a postfix.
sql, args  := sqlbuilder.Build("$? RETURNING id;", ib)

println(sql)
println(args)

// Output:
// INSERT INTO table (field) VALUES (?) RETURNING id;
// [some value]

Is this still the best way to do this? I'd like to use as many builders as I can without writing my own SQL.

[huandu]

Is this still the best way to do this? I'd like to use as many builders as I can without writing my own SQL.

A (maybe) better way is to use SQL() method like this.

ib.InsertInto("table").Cols("field").Values("some value")

// Add the RETURNING expression at the end of the INSERT statement.
ib.SQL("RETURNING id")

println(ib) // OUTPUT: INSERT INTO table (field) VALUES (?) RETURNING id

As RETURNING id is a PostgreSQL specific syntax and there is no widely accepted de-facto way to "return" last insert id among major DBMS, I don't think it's feasible to add a method to InsertBuilder for such feature.

[KyleSanderson]

Is this still the best way to do this? I'd like to use as many builders as I can without writing my own SQL.

A (maybe) better way is to use SQL() method like this.

ib.InsertInto("table").Cols("field").Values("some value")

// Add the RETURNING expression at the end of the INSERT statement.
ib.SQL("RETURNING id")

println(ib) // OUTPUT: INSERT INTO table (field) VALUES (?) RETURNING id

As RETURNING id is a PostgreSQL specific syntax and there is no widely accepted de-facto way to "return" last insert id among major DBMS, I don't think it's feasible to add a method to InsertBuilder for such feature.

It's built-in to sqlite and a couple others. On subqueries within a transaction it's absolutely invaluable.

[vanyauhalin]

Hello everyone. I am using SQLite and was also looking for this feature. I would be happy to see it as a builder method.

The RETURNING syntax has been supported by SQLite since version 3.35.0 (2021-03-12).

SQLite Reference

[huandu]

Since everyone here is interested in this feature, let me consider how to design it well.