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

Question

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!

simar · Accepted Answer · 2018-12-29 09:00:05Z

3

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

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.

edited Dec 29, 2018 at 9:00

answered Jan 18, 2016 at 13:48

simar

1,8523 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

simar Over a year ago

or u can just download data into database and run function which will process all data.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related