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

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download