Inserting a new record to PostgreSQL with default values from PetaPoco (and Npgsql)

Question

In PostgreSQL (9.5), I have the following table definition:

CREATE TABLE icd9
(
  recid serial NOT NULL,
  code text,
  cdesc text NOT NULL,
  "timestamp" timestamp without time zone DEFAULT now(),
  CONSTRAINT pk_icd9_recid PRIMARY KEY (recid)
)

Using PetaPoco, I have the following statement:

 icd9 newicd9 = db.SingleOrDefault<icd9>("select * from icd9 where lower(cdesc) = lower(@0)", newdx.cdesc);

 if (newicd9 == null)
 {       
       newicd9 = new icd9 { cdesc = newdx.cdesc.ToLower(), code = newdx.code};
       db.Insert(newicd9);
  }

Performing the Insert(newicd9) with PetaPoco/Npgsql results in no value being given to the "timestamp" of the new record. How do I fix this such that the PostgreSQL DEFAULT value is used?

(I can assign the timestamp a value when creating the newicd9 instance, but I would like PostgreSQL to assign it with the DEFAULT now() method).

Any help is most appreciated.

Edit: The icd9 class is defined by the PetaPoco T4 template as:

    [TableName("nova.icd9")]
    [PrimaryKey("recid")]
    [ExplicitColumns]
    public partial class icd9 : chaosDB.Record<icd9>  
    {
        [Column] public int recid { get; set; }
        [Column] public string code { get; set; }
        [Column] public string cdesc { get; set; }
        [Column] public DateTime? timestamp { get; set; }
    }

@harmic The icd9 class is defined by the PetaPoco T4 template. Please see added information. Thanks. — Alan Wayne, Oct 07 '16 at 00:27

score 2 · Accepted Answer · answered Oct 07 '16 at 05:05

When you perform an SQL INSERT into a table, if you provide a value for a column, then that value will be stored even if there was a default value for the column.

The only way to have the default applied is for the column not to be included in the SQL INSERT statement.

Inspecting the PetaPoco source code, I can see that it constructs INSERT statement using all columns except:

Columns marked as 'result columns'
the Primary key column

You could mark this time column as a result column (by decorating with [ResultColumn]) but by my reading of the code, that would also exclude it from the automatically generated SELECT clauses.

Disclaimer: While I do know a bit about postgresql, I don't know anything about .NET or petapoco!

Thanks. By using a Trigger with Before Insert, I can force a timestamp on null. Thanks again. — Alan Wayne, Oct 08 '16 at 22:35

Inserting a new record to PostgreSQL with default values from PetaPoco (and Npgsql)

1 Answers1