1

Given a table like:

CREATE TABLE myTable (
 identity SERIAL PRIMARY KEY,
 foo VARCHAR(32) NOT NULL
)

I would like to perform an UPSERT on that table using ADO.NET, my POCO in C# looks like:

public class myEntity {
 //When null the entity has never been stored in the database
 public int? Identity { get; set; }
 public string Foo { get; set; }
}

I came up with the following query to use with a DbCommand

INSERT INTO myTable (identity, foo) VALUES (@identity, @foo) 
ON CONFLICT (identity) DO UPDATE SET
foo = @foo
RETURNING identity;

The DbCommand is initialized as follows

using (var dbCommand = new NpgsqlCommand(INSERT_QUERY, dbConnection))
{
  dbCommand.Parameters.AddWithValue("@identity", oneOfMyEntities.Identity);
  dbCommand.Parameters.AddWithValue("@foo", oneOfMyEntities.Foo);

  int idReturned = (int)await dbCommand.ExecuteScalar();

  if (!oneOfMyEntity.Identity.HasValue) oneOfMyEntity.Identity = idReturned;
}

What I would like to achieve is that when an instance of myEntity has the property Identity set to null a new row is inserted into the database with the identity generated from the sequence, otherwise an update is performed.

The problem is that in PostgreSQL the only way to "force" the use of the next value in the sequence is to either omit the identity column from the INSERT query or to use DEFAULT. I can't simply put the literal "DEFAULT" in the dbCommand parameter @identity because, as far as I know, that would be interpreted as a string value a not the keyword DEFAULT.

I've tried to generate the query text at runtime so that if the entity has not an identity I replace the @identity parameter with DEFAULT, but this doesn't look good to me because I could simply remove ON CONFLICT, have two queries (one INSERT and one UPDATE) and execute an INSERT or an UPDATE depending on whether the entity identity is null or not thus not taking advantage of the ON CONFLICT clause.

How can I write this logic so that when an entity as a null identity I can somehow "force" PostgreSQL to use the next value in the sequence without resorting to generating the query text at runtime (and replacing the @identity parameter with DEFAULT) or using an UPDATE or an INSERT separately depending on the case ?

9
  • 1
    Almost duplicate of: stackoverflow.com/questions/35939897/… (you'll just need to add ON CONFLICT clauses). Commented Mar 30, 2017 at 8:19
  • @poszs Thank you, but the selected answer completely defeats the purpose of ON CONFLICT because that way I will never have a conflict so there is no need to even use the ON CONFLICT clause rendering the solution identical to using UPDATE or INSERT separately Commented Mar 30, 2017 at 8:57
  • 1
    there are suggestions to use dynamic SQL too, which is also a good alternative. But the selected answer could apply too, but you will want to use ON CONFLICT in only one of the INSERTs (the one where use actually use your primary key). Commented Mar 30, 2017 at 9:30
  • 1
    yes, but these are your options. I'm just saying that your ON CONFLICT clause does not change the nature of your problem. Commented Mar 30, 2017 at 11:27
  • 1
    yes. But I would say that this is an SQL issue, rather than a specific PostgreSQL one. The INSERT syntax is usually the same in every dialect (at least the part, where you enumerate the columns for insert & the DEFAULT keyword is also not an expression in any of them) -- some may allow you to use NULL & will insert the default value instead, but this could be emulated with a trigger in PostgreSQL (but IMHO it is very hacky workaround). Commented Mar 30, 2017 at 13:08

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.