topic Re: How to generate Surrogate Key for table in Postgresql in Talend Studio

How to generate Surrogate Key for table in Postgresql

Anonymous — Sat, 16 Nov 2024 12:21:15 GMT

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

Re: How to generate Surrogate Key for table in Postgresql

Anonymous — Wed, 22 Feb 2012 05:13:33 GMT

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

Re: How to generate Surrogate Key for table in Postgresql

Anonymous — Fri, 02 Mar 2012 01:37:01 GMT

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

Re: How to generate Surrogate Key for table in Postgresql

Anonymous — Thu, 23 Aug 2012 11:18:50 GMT

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