postgres last inserted id : "RETURNING id" vs "lastval()"

[abdeldjalil-fellah]

"RETURNING id" is more safe and reliable
"lastval()" can cause some issues with triggers

https://www.postgresql.org/docs/current/dml-returning.html
https://github.com/sqlkata/querybuilder/blob/master/QueryBuilder/Compilers/PostgresCompiler.cs
https://stackoverflow.com/questions/17819001/postgres-not-returning-lastval-properly
https://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id

[for7raid]

Yes, it is. But the "id" keyword is not a constant, it is the name of the real table column. So in this case we have to change function design to support referencing to primary key column name.

[abdeldjalil-fellah]

yes rewriting the function will fix that.

[for7raid]

Rewriting the function can propose losing back compatibility, and may be very specific compiler features.

[soags]

Hello, thanks for developing this project.
For those who are in trouble right now on this issue, I would like to introduce my workaround.
I made extension method for postgres specific InsertGetId instead of rewriting the library core.

public static class QueryExtensions
{
    public static async Task<T> InsertGetIdPostgresAsync<T>(
        this Query query,
        object data,
        string idColumn,
        QueryFactory db,
        IDbTransaction transaction = null,
        int? timeout = null)
    {
        var compiledQuery = db.Compiler.Compile(query.AsInsert(data));
        string sql = compiledQuery.Sql + $" RETURNING {idColumn}";

        var row = (await db.SelectAsync<InsertGetIdRow<T>>(
            sql,
            compiledQuery.NamedBindings,
            transaction,
            timeout
        )).First();

        return row.Id;
    }
}

public async Task<int> InsertAsync(MUser entity)
{
    var query = Db.Query("M_USER");

    int id = Db.Compiler.EngineCode switch
    {
        // PostgreSQL
        EngineCodes.PostgreSql =>
            await query.InsertGetIdPostgresAsync<int>(
                data: entity,
                idColumn: "ID",
                db: Db,
                transaction: Transaction),
        // Other
        _ => await query.InsertGetIdAsync<int>(entity, Transaction)
    };

    return id;
}

Thanks

[Sibusten]

lastval() also causes issues on postgres tables without sequences, such as when a UUID is used as the ID.

CREATE TABLE my_table
(
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    PRIMARY KEY (id)
);

Trying to insert a row with returnId set will lead to a failed insertion with the following error:
55000: lastval is not yet defined in this session

[nester-a]

lastval() also causes issues on postgres tables without sequences, such as when a UUID is used as the ID.
Trying to insert a row with returnId set will lead to a failed insertion with the following error: 55000: lastval is not yet defined in this session

That's all, because the lastval() returns a bigint, not a UUID. You cannot get the last UUID value added with this method.