I suspect your data is not what you think it is. What do you see when you do
SELECT ts FROM from sql_log_import LIMIT 3; On Mon, Sep 8, 2014 at 4:52 PM, Lou Oquin <loq...@nammotalley.com> wrote: > I’ve imported a csv export of an MS SQL Server log file into a staging > table on my local install of Postgresql (9.3/UTF8 encoding) for analysis. > > > > The staging table definition is: > > > > CREATE TABLE sql_log_import > > ( > > id serial NOT NULL, > > ts text, -- will convert to ts when merging into sql_server_logs > > source character varying(30), > > severity character varying(20), > > message text, > > CONSTRAINT sql_log_import_pk PRIMARY KEY (id) > > ) > > WITH ( > > OIDS=FALSE > > ); > > ALTER TABLE sql_log_import > > OWNER TO postgres; > > COMMENT ON COLUMN sql_log_import.ts IS 'will convert to ts when merging > into sql_server_logs'; > > > > Here’s a copy of the first few lines of the data imported to table > sql_log_import: > > 08/06/2014 03:08:58,spid4s,Unknown,SQL Trace was stopped due to server > shutdown. Trace ID = '1'. This is an informational message only; no user > action is required. > > 08/06/2014 03:08:58,spid12s,Unknown,The current event was not reported to > the Windows Events log. Operating system error = 1717(The interface is > unknown.). You may need to clear the Windows Events log if it is full. > > 08/06/2014 03:08:58,spid12s,Unknown,Error: 17054<c/> Severity: 16<c/> > State: 1. > > 08/06/2014 03:08:58,Server,Unknown,The connection has been lost with > Microsoft Distributed Transaction Coordinator (MS DTC). Recovery of any > in-doubt distributed transactions involving Microsoft Distributed > Transaction Coordinator (MS DTC) will begin once the connection is > re-established. This is an informational message only. No user action is > required. > > 08/06/2014 03:08:58,spid12s,Unknown,Service Broker manager has shut down. > > 08/06/2014 03:08:55,Server,Unknown,SQL Server is terminating because of a > system shutdown. This is an informational message only. No user action is > required. > > > > The final table is very similar, but with a timestamp with timezone field > for the logged server data. But, when I try to populate the target table > with data from the staging table, I keep getting an error. The issue is > associated with the to_timestamp function. > > > > Here’s what I’m seeing: If I use to_timestamp with the text data (copied > from table sql_log_import.ts), the select statement returns a timestamp > with timezone, as expected: > > -- Executing query: > > select to_timestamp('08/06/2014 03:08:58', 'MM/DD/YYYY > hh24:mi:ss')::timestamp with time zone as tstamp > > Total query runtime: 78 ms. > > 1 row retrieved. > > > > > > But, when I select data from the table sql_log_import, I get an error: > > -- Executing query: > > select to_timestamp(ts, 'MM/DD/YYYY hh24:mi:ss')::timestamp with time zone > as tStamp > > from sql_log_import > > where id <= 10 > > ********** Error ********** > > > > > > SQL state: 22007 > > Detail: Value must be an integer. > > > > > > Any Ideas? > > > > Thanks > > > > *Lou O’Quin* > > > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.