get id value of last entered record postgres

Question

Read answers on SO regarding similar Q's and tried a few out but to no joy. Using PostGresql I want to add a record to the table and record the value in the 'id' column of that record.

Tried currval, and RETURNING...crashes on Id = int.Parse(sqlcmd.ExecuteScalar().ToString()); Any ideas on what I am doing wrong?

        try
        {
            StringBuilder command = new StringBuilder();
            command.Append("resultId BIGINT");
            command.Append("INSERT INTO image_table (\"id\", \"table_name\", \"file_name\", \"image_data\", \"creation_date\") ");
            command.Append("VALUES ('" + ID + "', '" + tableName + "', '" + fileName + "', @Image, (SELECT LOCALTIMESTAMP))");
            command.Append("RETURNING id INTO resultId;"); 
            //command.Append("SELECT currval('id');"); 
            //command.Append("RETURNING id;"); 
            //command.Append("SELECT @@IDENTITY");


            using (NpgsqlCommand sqlcmd = CreateTextCommand(command.ToString(), connectionDataString))
            {
                sqlcmd.Parameters.AddWithValue("@Image", imageAsBytes);

                Id = int.Parse(sqlcmd.ExecuteScalar().ToString());

            }
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            CloseConnection();
        }
        return Id;
    }

Please see Get the last insert id from : http://www.postgresqltutorial.com/postgresql-insert/

isn't this what I am doing?

Update I got it using

K  command.Append("INSERT INTO image_table (\"id\", \"table_name\", \"file_name\", \"image_data\", \"creation_date\") ");
                command.Append("VALUES ('" + ID + "', '" + tableName + "', '" + fileName + "', @Image, (SELECT LOCALTIMESTAMP))");
                command.Append("RETURNING id;");

  using (NpgsqlCommand sqlcmd = CreateTextCommand(command.ToString(), connectionDataString))
                {
                    sqlcmd.Parameters.AddWithValue("@Image", imageAsBytes);
                    Id = int.Parse(sqlcmd.ExecuteScalar().ToString());
                }

Use Parameters for creating SQL statement, instead of string concatenation, your code is prone to SQL injeciton. Also see this question, it should be duplicate: http://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id — Habib, Feb 15 '16 at 15:05
check out these topics: http://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id http://stackoverflow.com/questions/4734589/retrieve-inserted-row-id-in-sql — Ivan Starostin, Feb 15 '16 at 15:35

score 0 · Answer 1 · answered Feb 15 '16 at 15:07

0

In Postgres, I would typically phrase this as:

WITH inserts as (
      INSERT INTO image_table (feature_id, table_name, file_name, image_data, creation_date)
          VALUES (. . .)
          RETURNING *
     )
SELECT id
FROM inserts;

Then you can fetch the value as you would from any SELECT query.

answered Feb 15 '16 at 15:07

Gordon Linoff

1,242,037
58
646
786

thanks for reply, but didnt work..please see original Q...how would I capture the returned value? – John Feb 15 '16 at 15:25
@John . . . I think you would use `ExecuteQuery()` as you would with any `SELECT`. – Gordon Linoff Feb 15 '16 at 17:46

score 0 · Answer 2 · answered Feb 18 '16 at 12:39

It seems you already found a solution, but to clarify, one issue appears to have been this:

command.Append("resultId BIGINT");
command.Append("INSERT INTO image_table ... ");

This is an immediate problem because with Append, it literally concats the two strings as such:

resultId BIGINTINSERT INTO image_table ... 
              ^^

Notice the lack of a space between "BIGINT" and "INSERT." If you use AppendLine instead of Append for future efforts, it can help prevent accidental string mashings.

For what it's worth, you used the bind variable for your image, which is completely appropriate, but it's always preferable to use bind variables for all of your values passed rather than literals.

Bind variables:

Manage all of the nastiness with datatypes between C# and your RDBMS
Eliminate complications if your string contains funny characters (ie a single quote that would cause the SQL to not compile
Eliminate the need to 'quote' non-numeric values in your SQL
Protect your code from SQL Injection
Is kinder to the database because it compiles the SQL one time and can potentially execute it over and over with different values

Here is your code converted to use all bind variables:

NpgsqlConnection conn = new NpgsqlConnection(connectionDataString);
conn.Open();

StringBuilder command = new StringBuilder();
command.Append("INSERT INTO image_table ");
command.Append("\"id\",\"table_name\",\"file_name\",\"image_data\",\"creation_date\") ");
command.Append("VALUES (:ID, :TABLENAME, :FILENAME, :IMAGE, current_timestamp) ");
command.Append("RETURNING id");

NpgsqlCommand cmd = new NpgsqlCommand(command.ToString(), conn);
cmd.Parameters.AddWithValue("ID", ID);
cmd.Parameters.AddWithValue("TABLENAME", tableName);
cmd.Parameters.AddWithValue("FILENAME", fileName);
cmd.Parameters.AddWithValue("IMAGE", imageAsBytes);

int Id = Convert.ToInt32(cmd.ExecuteScalar());

conn.Close();

get id value of last entered record postgres

2 Answers2