NLog, Npgsql.PostgresException: '42804: column "DateTime" is of type timestamp without time zone but expression is of type text

Question

I use NLog to log into the PostrgeSQL database.

I created the table:

CREATE TABLE "Data"."Logs"
(
    "ID" serial primary key,
    "Level" character varying(20),
    "DateTime" timestamp,
    "Message" character varying,
    "Exception" character varying
)

Then I configured target:

var logDB = new DatabaseTarget()
{
    Name = "logDB",
    DBProvider = "Npgsql.NpgsqlConnection,Npgsql",
    ConnectionString = connectionString,
    KeepConnection = true,
    CommandText = $@"INSERT INTO ""{SchemaName}"".""{FileManagerLogsTableName}""(""{Level}"", ""{DateTime}"", ""{Message}"", ""{Exception}"") VALUES (@level, @datetime, @message, @exception)"
};

logDB.Parameters.Add(new DatabaseParameterInfo("@level", "${level}"));
logDB.Parameters.Add(new DatabaseParameterInfo("@datetime", "${date:format=yyyy-MM-dd hh:mm:ss}"));
logDB.Parameters.Add(new DatabaseParameterInfo("@message", "${message}"));
logDB.Parameters.Add(new DatabaseParameterInfo("@exception", "${exception:format=shortType,message :separator= - }${newline}${exception:format=method}${newline}${exception:format=stackTrace:maxInnerExceptionLevel=5:innerFormat=shortType,message,method}"));

config.LoggingRules.Add(new NLog.Config.LoggingRule("*", LogLevel.Info, logDB));
LogManager.Configuration = config;
LogManager.ThrowExceptions = Debugger.IsAttached;

But when I want to log:

logger.Error(ex, ex.Message);

it throws an exception:

Npgsql.PostgresException: '42804: column "DateTime" is of type timestamp without time zone but expression is of type text

Answer 1

It is very simple solution. Just cast DateTime as timestamp in CommandText:

CommandText = $@"INSERT INTO ""{SchemaName}"".""{FileManagerLogsTableName}""(""{Level}"", ""{DateTime}"", ""{Message}"", ""{Exception}"") VALUES (@level, CAST(@datetime AS timestamp), @message, @exception)"