Search code examples
mysqlpostgresqlenumspentahokettle

how to insert data to a postgres table with enum data type in kettle pentaho?

I am trying to move data from mysql to postgres table. so I am using Table input step to get data from mysql table and using insert/update step to insert data to postgres table.

The postgres table has a enum data type in it. so, when I try to insert the data to that field it throws this error:

2016/01/18 12:36:56 - Insert / Update.0 - ERROR: column "subject_classification" is of type subject_classification_type but expression is of type character varying
2016/01/18 12:36:56 - Insert / Update.0 -   Hint: You will need to rewrite or cast the expression.
2016/01/18 12:36:56 - Insert / Update.0 -   Position: 166

I know this is a casting issue, but I didn't know how to cast it to enum data type.

This is the table schema of the table:

    CREATE TABLE subject (
    subject_id bigint NOT NULL,
    created_at timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    updated_at timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    code character varying(2000) NOT NULL,
    display_code character varying(2000) NOT NULL,
    subject_classification subject_classification_type NOT NULL,
    );


    CREATE TYPE subject_classification_type AS ENUM (
    'Math',
    'Social Science',
    'Language Arts'
);

Please somebody help me with this. Thanks!

Solution

  • Example 

    create type suser as enum ('admin', 'user' , 'staff');
drop table if exists user_login;
create table user_login(
    id serial primary key, 
  who_logged suser, 
    when_logged timestamp default CURRENT_TIMESTAMP
);

    Example solution

    enter image description here

    Keep in mind, this solution don't use power of PreparedStatement, thereby it is slow. If there is insert of million records, this may not a good solution and have to be measured.

    But it actually simplified version of generating insert, update statement and use same step "Execute SQL statement" to execute it.