Return ID of newly inserted row on a PostgreSQL database using C# and Npgsql?

Question

I'm building a WinForms project in C# using a PostgreSQL database and the Npgsql framework.

For inserting a record, I need to return the ID of the new record. This SO question says to add SELECT SCOPE_IDENTITY() to the query string passed to cmd. So my query string looks like this:

string insertString = "INSERT INTO sometable (company_name, category, old_value, old_desc, new_value, new_desc, reference1, reference2) VALUES (@comp, @cat, @oldValue, @oldDesc, @newValue, @newDesc, @ref1, @ref2); SELECT SCOPE_IDENTITY();";

and then get the ID with something like

int modified = cmd.ExecuteNonQuery();

But that's likely SQL Server-specific. If I use that method, I get an exception at the above line saying, "fuction scope_identity() does not exist".

I wasn't able to find anything that seemed to address this on the Npgsql documentation.

Per the linked SO question and Denis' suggestions I've tried adding both

RETURNING id;

and

CURRVAL(pg_get_serial_sequence('my_tbl_name','id_col_name'))

to the query string, replacing SELECT SCOPE_IDENTITY(); with those statements in the code above. In both cases they work as intended in DBeaver on an insert, but in my C# code in my WinForm project, modified was set to "1".

NOTE: I re-titled the question and added more information about what I've done.

There is no SCOPE_IDENTITY function in PostgreSQL. See this topic: https://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id — Denis Stukalov, Apr 06 '20 at 18:15
@DenisStukalov, thanks for the link. I added `RETURNING id;` to the query string and left the `ExecuteNonQuery` line alone, expecting it to put the id in `modified`, but when I ran it, `modified`'s value was "1". I tried the query in DBeaver and it returned the ID of the new row, so I'm just not certain how to access that value in my C# code. — marky, Apr 06 '20 at 18:59
I think, you better use this expression - `SELECT CURRVAL(pg_get_serial_sequence('my_tbl_name','id_col_name'))` and then user cmd.ExecuteScalar() for filling 'modified' variable — Denis Stukalov, Apr 06 '20 at 19:25
I just tried that and am still getting a 1 for the `modified` variable. — marky, Apr 06 '20 at 19:35
`CURRVAL` also worked in DBeaver with an insert, but like `RETURNING`, in my WinForm C# code, `modified` is just set to 1. — marky, Apr 06 '20 at 19:55

score 4 · Answer 1 · answered Oct 20 '21 at 08:13

Add "returning idcolumn" to the end of the sql query, then run the command with the ExecuteScalar() method instead of ExecuteNonQuery(). It should return with an int.

string insert = "insert into table1 (col1) values (something) returning idcol";
int id = cmd.ExecuteScalar();

score 1 · Answer 2 · answered Apr 14 '22 at 19:30

All the comments above were almost nearly spot on and got me to a solution but didn't exactly wrap it in a bow -- so I thought i'd post my implementation that works (with silly fake example tables of course).

        private int? InsertNameIntoNamesTable(string name)
        {
            int? id = null;
            
            using (var dbcon = new NpgsqlConnection(_connectionString))
            {
                dbcon.Open();

                StringBuilder sb = new StringBuilder();

                var sql = $@"                    
                    insert into names_table
                        (name)
                    values
                        ({name})
                        returning id;
                ";

                sb.Append(sql);

                using (var cmd = new NpgsqlCommand(sql, dbcon))
                {
                    id = (int)cmd.ExecuteScalar();
                }

                dbcon.Close();
            }

            return id;          
        }

score 0 · Answer 3 · answered Apr 10 '23 at 06:24

If you want to read multiple values after inserting you might use command.ExecuteReader(). command.ExecuteScalar() working for single column.

string insertCommand = "Insert into tableX (lastName, firstName,) values ('abc', 'efg') returning id, autoIncrementedClumn";
command.CommandText = insertCommand;
var reader = command.ExecuteReader();
if (reader.Read())
{
  int id = Convert.ToInt32(reader["id"].ToString());
  int autoIncrementedClumn=Convert.ToInt32(reader["autoIncrementedClumn"].ToString());
{

Return ID of newly inserted row on a PostgreSQL database using C# and Npgsql?

3 Answers3