23502: null value in column “id” violates not-null constraint in postgreSQL

Question

I create a table with primary key.

I tried to insert new data with entityframework6, but it would get 23502 error.

But I add the default value to the column before I insert it.

I don't understand why it would get this error.

Table DDL:

CREATE TABLE ERRORLOG(
id numeric NOT NULL,
message varchar(50) NULL,
CONSTRAINT pterrorlog_pk PRIMARY KEY (id)
);

Model:

public partial class ERRORLOG
{
    [Key]
    [Column(Order = 0)]
    public long ID { get; set; } = DateTimeOffset.Now.ToUnixTimeMilliseconds();
    public string MESSAGE { get; set; }
}

Funcation:

using (DbContext Db as new DbContext)
using (TransactionScope transactionScope = new TransactionScope())
{
    ERRORLOG iLog = new ERRORLOG();
    iLog.MESSAGE = Message;
    Db.ERRORLOG.Add(iLog);
    Db.SaveChanges(); //Get 23502 error
}

Here is the insert script, it looks like didn't insert the id, why is that?

INSERT INTO "pterrorlog"("message") VALUES (@p_0) RETURNING "id"

Edit:

After I add this script on the Model, it works fine now.

public partial class ERRORLOG
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.None)]
  public long ID { get; set; } = DateTimeOffset.Now.ToUnixTimeMilliseconds();
  public string MESSAGE { get; set; }
}

The error msg is clear. You are trying to insert NULL to non null column id.Show your insert statement as well. — Arun Palanisamy
@ArunPalanisamy I update the statement, it looks like it didn't insert the id column, why is that? — EvaHHHH
I could see you are not passing any values to id. Is it an auto increment column? — Arun Palanisamy
@ArunPalanisamy I use this script to set its value : public long ID { get; set; } = DateTimeOffset.Now.ToUnixTimeMilliseconds(); — EvaHHHH

Steve Py Steve Py · Accepted Answer · 2020-12-26T12:37:08

You can use PGAdmin to profile the SQL that EF is actually attempting to execute on SaveChanges. C# is case sensitive while Postgres defaults to lower case. If I recall NPGSQL will format all EF SQL Queries with double-quotes so if your Entities were declared with properties like ID, it would be generating statements like INSERT INTO "ERRORLOG" ( "ID", "MESSAGE" ) VALUES ( ... ) so a column named "id" wouldn't be getting set.

If you want your entities to use a different case than the DB, and leave Postgres using lower case then I'd recommend using [Column] attributes to rename the columns:

public partial class ERRORLOG
{
    [Key, Column(Name = "id")]
    public long ID { get; set; } = DateTimeOffset.Now.ToUnixTimeMilliseconds();
    [Column(Name = "message")]
    public string MESSAGE { get; set; }
}

The other detail is that Order on the Column attribute is only needed when dealing with composite keys, such as many-to-many joining tables where the PK is made up of two or more columns. It isn't needed for normal single-value PKs.

If that isn't the cause, checking the insert statement in PGAdmin should give you a clue what NPGSQL / EF is attempting to execute.

23502: null value in column “id” violates not-null constraint in postgreSQL

2 Answers