Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: PostGreSQL INET datatype support

  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Thumbs down PostGreSQL INET datatype support

    I have a CSV
    id hostname ipaddress macaddress
    101 abcd.com 10.20.30.40 00:0e:00:33:55:66
    102 abcdd.com 10.20.130.40 00:0e:00:31:55:66
    103 abc3d.com 10.120.30.40 00:0e:00:32:55:66
    104 abcd2.com 10.210.30.40 00:0e:00:34:55:66

    I have an postgresql table with the following columns
    id integer
    hostname char-varying
    ipaddress inet
    macaddres macaddr


    While trying to create a simple transformation to move the CSV data to PostGreSQL table,
    I am unable to choose the proper datatype for ipaddress and macaddress,
    If I choose string, I am getting runtime error


    How to go ahead with this?

    Is there any way to write custom datatype mapping? Any samples?

    Appreciate any help and/or links.
    Last edited by smartgk; 04-12-2011 at 02:02 PM.

  2. #2
    Join Date
    Apr 2011
    Posts
    2

    Default

    Any experts help?

  3. #3
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Sorry, I didn't even know the data type existed. Perhaps you need to update the PostgreSQL JDBC driver (we did in 4.2) to avoid the "runtime error".
    If that doesn't work I would Google a bit.

  4. #4
    Join Date
    Sep 2011
    Posts
    16

    Default

    Old thread, but I stumbled upon it with the same issue, and here is my solution.

    In Kettle, an inet value is set with setString, which internally is converted to varchar in postgres. Since at least 8.3, though, postgres does not automagically convert varchars to inet.
    The solution is to create an explicit CAST:
    CREATE CAST (CHARACTER VARYING AS inet) WITH INOUT AS ASSIGNMENT;
    That way a varchar will be properly handled in an inet typed field.

    Ideally Kettle would allow different types. I don't think it is possible to know all possible types of all possible jdbc bakends, but maybe it would be possibe to add a generic (free text) CAST in the select value step? This would not match against a java type, but merely surround the relevant field with CAST ... AS free_text_cast in the resulting SQL.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.