Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848

    Unanswered: What am I not seeing...

    A little background

    First, it's been a looong week, topped off by a long day (woke up at 3:00 AM and couldn't get back to sleep. Yech.)

    Today, I've imported a SQL Server log file into a staging table on my local pg (v9.3) server for some analysis.

    The staging table is defined as
    Code:
    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 character varying(400),
      CONSTRAINT sql_log_import_pk PRIMARY KEY (id)
    )
    The target table (where the data will be placed) has a couple of extra columns, PLUS, the column ts is defined as timestamp with timezone.

    I've posted an image of some of the data in the table, just after double-clicking the ts (varchar) field to copy the data for a test.
    Click image for larger version. 

Name:	postgres_sql_log_import.png 
Views:	8 
Size:	8.8 KB 
ID:	15801

    I paste the copied data into a query, and pg correctly casts & displays the timestamp with timezone:
    Code:
    select to_timestamp('08/06/2014 03:08:58', 'MM/DD/YYYY hh24:mi:ss')::timestamp with time zone as tstamp
    Now comes the fun part.

    I edit the SQL above, to change the pasted datetime to the column name from the staging table and add the from clause:
    Code:
    select to_timestamp(ts, 'MM/DD/YYYY hh24:mi:ss')::timestamp with time zone as tStamp
    from sql_log_import
    and I get the following result:
    Code:
    ********** Error **********
    
    
    SQL state: 22007
    Detail: Value must be an integer.
    WHAT am I not seeing?

    The person who sees the problem gets the "Winner! Winner! Chicken Dinner!" Post :-)
    Last edited by loquin; 08-29-14 at 19:44. Reason: added the 'reward' ...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848

    Resolved!


    There were leading UTF8 encoding bytes in the data of the first record. Apparently I selected ascii rather than UTF8 encoding in pgAdmin's import utility, so it simply passed the extra hi-bit bytes along to the table when importing.

    When I replaced the date/time with the 'Identical' date/time from the second record, the timestamp casting works as advertised!

    There is a tiny clue in the pgAdmin data view; before selecting the datetime for editing, there were a few extra pixels on the leading zero - almost like a horizontal apostrophe merged into the character.
    Click image for larger version. 

Name:	Offending UTF8 encoding bytes Affect in pgAdmin.png 
Views:	4 
Size:	842 Bytes 
ID:	15818

    So, the 'Winner-Winner, Chicken Dinner' award goes out to Adrian Klaver and Jerry Sievers on the postgresql mailing lists. Jerry's reply had me checking the output using psql, which explicitly raised the error of UTF8 encoding within the data. Then, after I found the offending bytes, I read Jerry's (earlier) reply, where he identified the problem exactly. (My email client is set to list by descending datetime, so I didn't see Jerry's reply until I had also found the problem.)
    Last edited by loquin; 09-09-14 at 14:41. Reason: add image
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •