How to generate Surrogate Key for table in Postgresql

One Star

How to generate Surrogate Key for table in Postgresql

I am running into issue while trying to generate Surrogate key on the table in PostgreSQL database.
Scenario:
1) I have a tabe Reject_Log with primary key RejectLogID defined as below:
CREATE TABLE reject_log
(
"RejectLogID" SERIAL NOT NULL,
"RejectMessage" character varying,
CONSTRAINT reject_log_pkey PRIMARY KEY ("RejectLogID")
)
WITH (
OIDS=FALSE
);
ALTER TABLE reject_log OWNER TO postgres;
GRANT ALL ON TABLE reject_log TO postgres;
GRANT ALL ON TABLE reject_log TO talenduser;
2) I have a tMap component, where I assign error message "Test" to column "RejectMessage"
3) Column "RejectLogID" is not part of mapping to ensure that PostgreSQL database uses default functionality assigning unique value from sequence.
4) When job is executed in Talend, it fails with error:
connecting to socket on port 3355
connected
Batch entry 0 INSERT INTO "stage"."reject_log" ("RejectMessage") VALUES (Test) was aborted. Call getNextException to see the cause.
disconnected
Questions:
1) why Talend fails while trying to insert the row into "Reject_Log" table?
2) when I change column RejectLogID data type in database from SERIAL to INTEGER - Talend works with no errors. Why it does not work with Serial data type?!?
3) what is the recommendation on " how to avoid and error?"?
Any help would be greately appreciated
One Star

Re: How to generate Surrogate Key for table in Postgresql

Hi
Welcome to Talend Community!
Before answering your questions, I guess you didn't create a job correctly in Talend way.
Could you upload a screenshot of your job?
Then we can know how to solve it.
Regards,
Pedro
One Star

Re: How to generate Surrogate Key for table in Postgresql

Thank you Pedro for your reply!
I hae resolved this issue by adding lookup to tMap.
Look up is connecting to db and selecting nextvalue from explicitely defined sequence.
I also setup that look up is re-loaded for each row - this insures that new value is generated by sequence for every new row.
Thanks again,
Elena
One Star

Re: How to generate Surrogate Key for table in Postgresql

Hi elena,
please can you ellobrate on how you have declared sequance explicitly and achived this task, please attach screen shots if possible.
thanks
rishabh