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 ?
ON CONFLICTclauses).ON CONFLICTin only one of theINSERTs (the one where use actually use your primary key).ON CONFLICTclause does not change the nature of your problem.INSERTsyntax is usually the same in every dialect (at least the part, where you enumerate the columns for insert & theDEFAULTkeyword is also not an expression in any of them) -- some may allow you to useNULL& will insert the default value instead, but this could be emulated with a trigger in PostgreSQL (but IMHO it is very hacky workaround).