Okay, now that I have the &quot;&quot; -&gt; Null replacements, now I have another escape issue.<br><br>Making use of Postgresql&#39;s bigserial, it reads:<br>&lt;qoute <a href="http://www.postgresql.org/docs/9.0/interactive/datatype-numeric.html#DATATYPE-SERIAL">http://www.postgresql.org/docs/9.0/interactive/datatype-numeric.html#DATATYPE-SERIAL</a>&gt;<br>
To insert the next value of the sequence into the <tt class="TYPE">serial</tt>      column, specify that the <tt class="TYPE">serial</tt>
      column should be assigned its default value. This can be done     
 either by excluding the column from the list of columns in      the <tt class="COMMAND">INSERT</tt> statement, or through the use of      the <tt class="LITERAL">DEFAULT</tt> key word.     <br>&lt;/quote&gt;<br><br>Which means I have to have *no* quotes, which the syslog-ng enforces :(<br>
<br>destination d_pgsql {<br>  sql(type(pgsql)<br>  host(&quot;127.0.0.1&quot;) username(&quot;logwriter&quot;) password(&quot;logwriter&quot;) port(&quot;5432&quot;)<br>  database(&quot;syslog&quot;) table(&quot;SystemEvents&quot;)<br>
columns(&quot;ID bigserial primary key&quot;,&quot;ReceivedAt timestamptz NULL&quot;, &quot;DeviceReportedTime timestamptz NULL&quot;,<br>&quot;Facility smallint NULL&quot;,&quot;Priority smallint NULL&quot;,&quot;FromHost varchar(60) NULL&quot;,<br>
&quot;Message text&quot;,&quot;InfoUnitID int NULL&quot;,&quot;SysLogTag varchar(60)&quot;,<br>&quot;CustomerID bigint&quot;,&quot;NTSeverity int NULL&quot;,&quot;Importance int NULL&quot;,&quot;EventSource varchar(60)&quot;,&quot;EventUser varchar(60) NULL&quot;,<br>
&quot;EventCategory int NULL&quot;,&quot;EventID int NULL&quot;,&quot;EventBinaryData text NULL&quot;,&quot;MaxAvailable int NULL&quot;,&quot;CurrUsage int NULL&quot;,&quot;MinUsage int NULL&quot;,<br>&quot;MaxUsage int NULL&quot;,&quot;EventLogType varchar(60)&quot;,&quot;GenericFileName VarChar(60)&quot;,&quot;SystemID int NULL&quot;)<br>
values(DEFAULT,&quot;$R_ISODATE&quot;, &quot;$S_ISODATE&quot;,&quot;$FACILITY_NUM&quot;,&quot;$LEVEL_NUM&quot;,&quot;$HOST&quot;,<br>&quot;$MSGONLY&quot;,&quot;1&quot;,&quot;$MSGHDR&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;)<br>
indexes(&quot;ID&quot;,&quot;ReceivedAt&quot;,&quot;Facility&quot;,&quot;Priority&quot;,&quot;FromHost&quot;,&quot;SysLogTag&quot;,)<br>null(&quot;&quot;));<br>};<br><br><br><br>Running SQL query; query=&#39;INSERT INTO SystemEvents (ID, ReceivedAt, DeviceReportedTime, Facility, Priority, FromHost, Message, InfoUnitID, SysLogTag, CustomerID, NTSeverity, Importance, EventSource, EventUser, EventCategory, EventID, EventBinaryData, MaxAvailable, CurrUsage, MinUsage, MaxUsage, EventLogType, GenericFileName, SystemID) VALUES (\&#39;DEFAULT\&#39;, \&#39;2011-03-11T14:14:12+02:00\&#39;, \&#39;2011-03-11T11:12:37+02:00\&#39;, \&#39;20\&#39;, \&#39;6\&#39;, \&#39;betvmi01\&#39;, \&#39;[2011-03-11 11:12:37.565 11AA6B90 verbose \&#39;\&#39;App\&#39;\&#39;] [VpxaVMAP] CheckThreshold percent changes (0,0,0,0) - threshold 5\&#39;, \&#39;1\&#39;, \&#39;Vpxa: \&#39;, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)&#39;<br>
Error running SQL query; type=&#39;pgsql&#39;, host=&#39;127.0.0.1&#39;, port=&#39;5432&#39;, user=&#39;logwriter&#39;, database=&#39;syslog&#39;, error=&#39;<b>ERROR:  invalid input syntax for integer: &quot;DEFAULT&quot;</b>\x0aLINE 1: ... EventLogType, GenericFileName, SystemID) VALUES (\&#39;DEFAULT\&#39;,...\x0a                                                             ^\x0a&#39;, query=&#39;INSERT INTO SystemEvents (ID, ReceivedAt, DeviceReportedTime, Facility, Priority, FromHost, Message, InfoUnitID, SysLogTag, CustomerID, NTSeverity, Importance, EventSource, EventUser, EventCategory, EventID, EventBinaryData, MaxAvailable, CurrUsage, MinUsage, MaxUsage, EventLogType, GenericFileName, SystemID) VALUES (\&#39;DEFAULT\&#39;, \&#39;2011-03-11T14:14:12+02:00\&#39;, \&#39;2011-03-11T11:12:37+02:00\&#39;, \&#39;20\&#39;, \&#39;6\&#39;, \&#39;betvmi01\&#39;, \&#39;[2011-03-11 11:12:37.565 11AA6B90 verbose \&#39;\&#39;App\&#39;\&#39;] [VpxaVMAP] CheckThreshold percent changes (0,0,0,0) - threshold 5\&#39;, \&#39;1\&#39;, \&#39;Vpxa: \&#39;, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)&#39;<br>
<br>AS long as there is now escapes for the DEFAULT, then it &quot;works&quot;:<br><br>syslog=# INSERT INTO SystemEvents (ID, ReceivedAt, DeviceReportedTime, Facility, Priority, FromHost, Message, InfoUnitID, SysLogTag, CustomerID, NTSeverity, Importance, EventSource, EventUser, EventCategory, EventID, EventBinaryData, MaxAvailable, CurrUsage, MinUsage, MaxUsage, EventLogType, GenericFileName, SystemID) VALUES (DEFAULT, &#39;2011-03-11T14:10:18+02:00&#39;, &#39;2011-03-11T11:08:43+02:00&#39;, &#39;20&#39;, &#39;6&#39;, &#39;betvmi01&#39;, &#39;[2011-03-11 11:08:43.522 11921B90 verbose &#39;&#39;App&#39;&#39;] [VpxaVMAP::Invoke] Command returned successfully&#39;, &#39;1&#39;, &#39;Vpxa: &#39;, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);<br>
INSERT 0 1<br>syslog=# <br><br><div class="gmail_quote">On Tue, Feb 22, 2011 at 12:59 PM, Zoltán Pallagi <span dir="ltr">&lt;<a href="mailto:pzolee@balabit.hu">pzolee@balabit.hu</a>&gt;</span> wrote:<br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">


  
    
  
  <div bgcolor="#ffffff" text="#000000">
    Try this:<br>
        columns(&quot;datetime varchar(16)&quot;, &quot;host varchar(32)&quot;, &quot;program
    varchar(16)&quot;, &quot;pid varchar(4) default null&quot;, &quot;message varchar&quot;)<br>
          values(&quot;$R_DATE&quot;, &quot;$HOST&quot;, &quot;$PROGRAM&quot;, &quot;$PID&quot;, &quot;$MSG&quot;)<div class="im"><br>
            indexes(&quot;datetime&quot;, &quot;host&quot;, &quot;program&quot;, &quot;pid&quot;, &quot;message&quot;)<br></div>
      null(&quot;&quot;));<br>
    <br>
    null(): If the content of a column matches the string specified in
    the <i><code>null()</code></i> parameter, the
    contents of the column will be replaced with an SQL NULL value<br>
    <br>
    It worked for me:<br>
    <br>
    Incoming log entry; line=&#39;&lt;166&gt;Feb 22 08:38:59 Vpxa:
    HostChanged Event Fired, properties changed []&#39;<br>
    Running SQL query; query=&#39;INSERT INTO t2105010221cdb33661c82e91cb0b
    (datetime, host, program, pid, message) VALUES (\&#39;Feb 22 11:50:17\&#39;,
    \&#39;thor-t410\&#39;, \&#39;Vpxa\&#39;, NULL, \&#39;HostChanged Event Fired, properties
    changed []\&#39;)&#39;<br>
    <br>
    Ofc, you can use integer type for pid, but this is a string and not
    an integer.<div><div></div><div class="h5"><br>
    <br>
    On 2011-02-22 10:57, Hendrik Visage wrote:
    </div></div><blockquote type="cite"><div><div></div><div class="h5">Hi there,<br>
      <br>
       Okay, looks like I was looking/thinking about something else, so
      lets explain my problem.<br>
      <br>
       I want the pid inseted to be a &quot;proper&quot; number/integer, not a
      string/character, and the fun starts with several of my sources
      not having a pid entry, thus that field I would like to be filled
      with NULL values. However, it still gets escaped, and when it gets
      escaped, the NULL is not null anymore, but the string &#39;NULL&#39; :(<br>
      <br>
      I was hoping that in some why I could coerce syslog-ng to
      understand that that column is a integer column, thus should not
      be escaped as it is currently done ;(<br>
      <br>
      I have the following destination:<br>
      destination d_pgsql {<br>
        sql(type(pgsql)<br>
        host(&quot;127.0.0.1&quot;) username(&quot;logwriter&quot;) password(&quot;logwriter&quot;)
      port(&quot;5432&quot;)<br>
        database(&quot;syslog&quot;)<br>
        table(&quot;logs_${HOST}_${R_YEAR}${R_MONTH}${R_DAY}&quot;) #or whatever
      you want, example ${HOST}&quot; for hosts, ${LEVEL}&quot; for levels.. etc<br>
        columns(&quot;datetime varchar(16)&quot;, &quot;host varchar(32)&quot;, &quot;program
      varchar(16)&quot;, &quot;pid varchar(8) default null&quot;, &quot;message varchar&quot;)<br>
        values(&quot;$R_DATE&quot;, &quot;$HOST&quot;, &quot;$PROGRAM&quot;, &quot;${PID:-NULL}&quot;, &quot;$MSG&quot;)<br>
        indexes(&quot;datetime&quot;, &quot;host&quot;, &quot;program&quot;, &quot;pid&quot;, &quot;message&quot;));<br>
      };<br>
      <br>
      and the following log lines:<br>
      <br>
      Incoming log entry; line=&#39;&lt;166&gt;Feb 22 08:38:59 Vpxa:
      [2011-02-22 08:38:59.862 1B7A7B90 verbose \&#39;App\&#39;]
      [VpxaHalServices] HostChanged Event Fired, properties changed []&#39;<br>
      Running SQL query; query=&#39;INSERT INTO logs_somedevice_20110222
      (datetime, host, program, pid, message) VALUES (\&#39;Feb 22
      11:39:27\&#39;, \&#39;somedevice\&#39;, \&#39;Vpxa\&#39;, \&#39;NULL\&#39;, \&#39;[2011-02-22
      08:38:59.862 1B7A7B90 verbose \&#39;\&#39;App\&#39;\&#39;] [VpxaHalServices]
      HostChanged Event Fired, properties changed []\&#39;)&#39;<br>
      <br>
      </div></div><pre><fieldset></fieldset>
______________________________________________________________________________
Member info: <a href="https://lists.balabit.hu/mailman/listinfo/syslog-ng" target="_blank">https://lists.balabit.hu/mailman/listinfo/syslog-ng</a><div class="im">
Documentation: <a href="http://www.balabit.com/support/documentation/?product=syslog-ng" target="_blank">http://www.balabit.com/support/documentation/?product=syslog-ng</a>
FAQ: <a href="http://www.campin.net/syslog-ng/faq.html" target="_blank">http://www.campin.net/syslog-ng/faq.html</a>

</div></pre>
    </blockquote>
    <br>
  </div>

</blockquote></div><br>