Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Postgresql: Table output / Forced to specify database field

  1. #1
    Join Date
    Oct 2007
    Posts
    107

    Default Postgresql: Table output / Forced to specify database field

    Hello,

    I've been using PDI since a while now but I'm trying to work with Postgresql for the first time (used to work with Oracle, SQL Server and MySQL).

    I've been struggling to import a flat file into a table and it was not working. All my stream fields can map to a field within my output table (same name and exact number of fields).

    When running my transformation, I get the error :
    2017/03/05 15:44:48 - Table output.0 - ERROR: column "route_id" of relation "trips" does not exist
    2017/03/05 15:44:48 - Table output.0 - Position: 29
    2017/03/05 15:44:48 - Table output.0 -
    2017/03/05 15:44:48 - Table output.0 -
    2017/03/05 15:44:48 - Table output.0 - Error updating batch
    2017/03/05 15:44:48 - Table output.0 - Batch entry 0 INSERT INTO "public".trips (route_id, service_id, trip_id, trip_headsign, direction_id, block_id, shape_id, wheelchair_accessible) VALUES ( 1, 'DI', '1_1_R_DI_1601_06_45', 'Test data', 0, '1601_2', '1_1_R', 1) was aborted. Call getNextException to see the cause.
    2017/03/05 15:44:48 - Table output.0 -
    2017/03/05 15:44:48 - Table output.0 -


    However, if I do specify the fields in the table output, then it works...(yes, route_id exists...)
    This is a bit weird to me and looks like a bug but just wondering if anyone is getting this with Postgresql ?

    Thanks.

    BR,
    C.

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    What's the exact DDL name of column route_id?
    PostgreSQL identifiers are lowercase unless quoted.
    DB connection has an option "Quote all in database" to adjust for non-lowercase names.
    You can read about it in the docs - section 4.1.1 last paragraph.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Oct 2007
    Posts
    107

    Default

    grr.... can't post the error, pentaho firewall keep rejecting and don't know why..

    anyway, DDL statement is route_id, all in lowercase, nothing special here.

    Already tried the quoting you suggest, still get an error about the field "route_id" not existing... funny, when I specify database field, it exists...
    Name:  Screenshot from 2017-03-06 21-00-24.jpg
Views: 407
Size:  15.6 KB
    Name:  ddl.jpg
Views: 411
Size:  22.9 KB
    Last edited by CHamel; 03-06-2017 at 10:02 PM.

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Did you change the data type of column route_id lately?
    I'm asking because Kettle generates an Integer value for that INSERT statement, but your DDL says CHAR VARYING.
    If so, you should clear the Spoon database cache.
    There's even an option disabling the cache permanently.

    BTW: You're not the only one suffering from the application firewall rules...
    Last edited by marabu; 03-07-2017 at 03:53 PM.
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Oct 2007
    Posts
    107

    Default

    Quote Originally Posted by marabu View Post
    Did you change the data type of column route_id lately?
    I'm asking because Kettle generates an Integer value for that INSERT statement, but your DDL says CHAR VARYING.
    If so, you should clear the Spoon database cache.
    There's even an option disabling the cache permanently.

    BTW: Your not the only one suffering from the application firewall rules...

    You're right. I changed that, it was my input that detected an int and I forgot to changed it. However, it doesn't change the behavior.
    I'm currently running this on a local linux box. I'll try to do the same thing from my windows workstation to see if I get the same problem.

    Thanks for your help.

    BR,
    C.

  6. #6
    Join Date
    Jul 2009
    Posts
    476

    Default

    This probably isn't your specific problem, but the way Postgres selects schemas is a bit different than other DBMS. Postgres has the concept of a "schema search path," which it uses to search for tables that don't have schema qualifiers. It's very similar to how an operating system uses your $PATH variable to find executable files when you don't include the full directory path to the file. The way it works is documented here: https://www.postgresql.org/docs/curr...l-schemas.html

    Again, it sounds like your issue is something else, but when you described the original problem, I wondered if maybe you had two "trips" tables in different schemas, one with a "route_id" column and one without it.

  7. #7
    Join Date
    Oct 2007
    Posts
    107

    Default

    Hello Rob,

    No, only one table named trips and I only have the "public" schema.
    I also provide the schema (Target Schema = public) in the table output step.

    Bah, I'll specify the database fields and that's it, I intended to anyway because I'm planning to use Metadata Injection in my project. I was just curious about why it was not working like I'm used to...
    Thanks for your help.

    BR,
    C.

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.