Dapper issue with postgres returning id or select currval(pg_get_serial_sequence('<table>','<id>'))?

Question

I'm trying to use Dapper, Npgsql to connect to a postgres database from a .net application. After an insert, I want to know the id of inserted row. I have read and tried options discussed here. I have tried using returning id like so:

id = connection.Execute("insert into document_tag (tag) values (@tag) returning id;",
            new { tag },
            transaction);

but I get only '1' in id.

I also tried to use currval like so:

id = connection.Execute("insert into document_tag (tag) values (@tag); select currval(pg_get_serial_sequence('document_tag','id'));",
            new { tag },
            transaction);

Even in this case, I get only '1' in id.

In both cases, the insert was successful and the id column in the database had a valid serial number other than '1'.

Is my expectation wrong or is there an alternative?

Answer 1

To insert a row and get back a database-generated column, use:

INSERT INTO document_tag (tag) VALUES (@tag) RETURNING id

Note that there is no semicolon before "RETURNING" - it's just a clause of the INSERT statement, as you can see in the PostgreSQL docs.

In addition, once you add a RETURNING clause, the INSERT statement returns a resultset containing the requested information, just as if you executed a SELECT statement. Dapper's Execute() is meant to execution without resultsets, so you will need to switch to Query(), and probably add a Single() method to extract the single value you need. An alternative would be ExecuteScalar().