How to generate next Primary Key value

Question

I'm using Visual Studio 2010 PostgreSQL 9.x Npgsql

I'm trying to insert data from fields on a C# WinForms app. I just can't figure out how to generate/retrieve the next primary key value on the fly. Here are my column names:

epiphanyKey [PK] bigserial transaction numeric license character dateOfActv date time time

I need the insert command to be "session-safe" since multiple people could be entering data into the database at the same time.

 NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=postgres;Password=password;Database=epiphany;"); // postgres 8.3 on my test system
 conn.Open(); // opens the connection

 NpgsqlCommand cmd = new NpgsqlCommand("INSERT INTO wsmsmrs210 (epiphanyKey,transaction,license,dateOfActv,time, conn);

   NpgsqlDataReader dr = cmd.ExecuteReader();

In the code above the NpgsqlCommand cmd = ... statement doesn't work correctly because I don't know the next primary key value for the primary key value epiphanyKey.

Any ideas or code sniplets to generate the next primary key value when sending the query to the db?

Answer 1

You can use the returning keyword to make the query return the id that was just created. Example from the docs:

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
RETURNING did;

The code that you posted is not complete, or even possible to compile, so I can't give you a complete example how to use it in your case, but here is a start:

NpgsqlCommand cmd = new NpgsqlCommand(
  "INSERT INTO wsmsmrs210 " +
  "(epiphanyKey,transaction,license,dateOfActv,time, conn) " +
  "VALUES (...) " +
  "RETURNING epiphanyKey");

int id = cmd.ExecuteScalar();