[syslog-ng] Database field types -- insert fails for null values

[Liam Kirsher]

This worked fine for the timestamps, but there was a problem with other
types.

Some of the numeric fields can have null values.

It looks like syslog-ng doesn't translate empty strings into nulls when
doing the insert.  Thus, the insert fails because the numeric field
types won't accept a string of any kind.

For now, I can just let them be text strings, but I'd prefer to be able
to use numeric types and allow null values.  For example, in the
destination below, lat and long could be float8 and pid and art_id could
be integers.
>
> destination d_ut_access {
>     sql(
>        type(pgsql)
>        host("localhost") username("postgres") password("xxxxxxx")
>        database("prodlogs")
>        table("ut_access_log")
>        columns("time_stamp timestamp with time zone", "query_time
> timestamp",
>            "host", "program", "pid", "request_id", "level", "ip",
> "phone_id",
>            "phone_type", "software_version", "client_version",
> "query_string",
>            "art_id", "session_id", "lat", "lng")
>        values("$R_ISODATE", "${UT.QTIME}", "$HOST_FROM", "$PROGRAM",
> "$PID",
>            "${UT.REQUEST_ID}", "$LEVEL", "${UT.IP}", "${UT.PHONE_ID}",
>            "${UT.PHONE_TYPE}", "${UT.SOFTWARE_VERSION}",
> "${UT.CLIENT_VERSION}",
>            "${UT.QUERY_STRING}", "${UT.ART_ID}", "${UT.SESSION_ID}",
>            "${UT.LAT}", "${UT.LNG}")
>        indexes("time_stamp", "query_time", "ip", "phone_id")
>     );
> };




Balazs Scheidler wrote:
> On Fri, 2009-03-06 at 19:08 -0800, Liam Kirsher wrote:
>   
>> Hi --
>>
>> I notice that when the database tables get created all the fields are
>> 'text' type.  That is okay.
>> However, it might in some cases be convenient for us to have different
>> field types.
>> Is there any problem with that?
>> If we create the table with the desired field types, will that take care
>> of it (assuming the data actually matches the type)?
>>     
>
> you can specify the field type, like this:
>
> sql(columns("date datetime"));
>
> e.g. separate the type with a space from the column name.
>
>   

-- 
Liam Kirsher
PGP: http://liam.numenet.com/pgp/