Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: getting around bug with TableOutput and PostgreSQL return auto-generated key

  1. #1
    Join Date
    Feb 2011
    Posts
    152

    Default getting around bug with TableOutput and PostgreSQL return auto-generated key

    I just thought I would share a hack that gets around this http://jira.pentaho.com/browse/PDI-8069 bug where the returned key field must be the first field in a table you are inserting into.

    If your table is defined like this:

    Code:
    CREATE TABLE my_table (
       some_field varchar(40) null,
       some_int integer null,
       my_table_pk SERIAL NOT NULL PRIMARY KEY, -- notice primary key not first field
       other_field1 varchar(30) null
    );
    You can insert into this table and return the auto-generated serial value by creating a view on top of the table and then inserting into the view.

    Code:
    CREATE VIEW v_my_table as
    SELECT
         my_table_pk,
         some_field,
         some_int,
         other_field1
    FROM my_table;
    Just re-order the fields so that the serial field is the first one in the view, and then point the TableOutput step to this new view and it works using PostgreSQL 9.3! It may work on older 9.x branches, but have not tested.
    Last edited by dnrickner; 11-19-2014 at 10:50 PM. Reason: formatting

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

    Default

    Thanks for sharing. I hope I will remember this when in need ...
    So long, and thanks for all the fish.

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.