topic Re: Postgresql sequence in primary key in Installing and Upgrading

Postgresql sequence in primary key

Anonymous — Sat, 16 Nov 2024 14:38:27 GMT

Hi !
I use sequence for incrementing my primary key. (e.g : CREATE SEQUENCE "public"."personne_idpersonne_seq" INCREMENT 1 MINVALUE 1
MAXVALUE 9223372036854775807 START 1 CACHE 1
In my table I have this : idpersonne, which is a Bigint type and it default value is "nextval('personne_idpersonne_seq'::regclass)"
When I want insert data into my "personne" table, I don't want give it an idpersonne. My sequence does that.
But I have this message :
"DBD: g::st execute failed: ERROR: null value in column "idpersonne" violates not-null constraint
can't execute insert query
A thread exited while 2 threads were running. "
How to do that ?
Thanks a lot.
Kim

Re: Postgresql sequence in primary key

Anonymous — Fri, 10 Nov 2006 17:13:14 GMT

Hi,
We need some explanation about your Data Integration Job.
Can you send us a screenshot of your Job, and explain your need?
If I understand your problem, you would like to insert with an External Sequence in your "idpersonne" a PrimaryKey.
I think idpersonne is not a true PRIMARY_KEY but a simulated PRIMARY_KEY, isn't it ?
I am waiting more complements to reproduce your problem and resolve it.
Regards.

Re: Postgresql sequence in primary key

Anonymous — Mon, 13 Nov 2006 10:30:16 GMT

Hi !
My idPersonne is really my primary key and it is a big serial type. In Postgresql, bigserial is converted in bigint with a sequence. If you want the sequence works, the must delete the primary key from the Talend 's schema. I tried it and it works fine. Now it's Postgresql which increments my primary key with the sequence.
But, now, how to do with the default value ? For example, I use an idProfession with a default value at 92101. But if the profession dosen't match, I have a null value in the column, not a 92101. How to do for having my default value when the profession is not found ?
Thanks a lot.
Kim

Re: Postgresql sequence in primary key

Anonymous — Tue, 14 Nov 2006 13:50:22 GMT

Hi,
I agree with your first solution, if you want to use your default value for the PRIMARY_KEY, ignore it of your Schema Table.
In this case the tDBOuput component will ignore the idpersonne column and insert by consequence your Default value ( for example = nextval('personne_idpersonne_seq'::regclass)).
In the other case : you must use a tMap component, you can create Constraint conditions and manage reject data. (for Example : $row1 eq '' or $row1 ne '')
I propose to you to look at the tMap tutorial for the Constraint conditions creation.
You can look at this two screenshots the Job and the tMap configuration for your issue.
Screenshot 1 : the Job with one source, a tMap (management of reject) and two tDBOuput (your table destination for two output).
Screenshot 2 : the tMap properties, $row1 is your idProfession and TalendTest in Out1 is your 92101 default Value.
Out2 are the data with a Constraint condition : $row1 ne '' (libelle not equal empty).
Out1 are the Data rejected with TalendTest in subsitution of $row1.
In red circle the Important point of tMap properties for your Issue.
Regards.

Re: Postgresql sequence in primary key

Anonymous — Mon, 20 Nov 2006 10:34:07 GMT

Hi !
I think it's a big job for having just a default value in a column, when you have no other value to insert...
When you tell "two tDBOuput (your table destination for two output)", you speak about the same table ? If so, I tried to do again your example on the same output table. The result is not really what I was waiting for... I have twice the number of rows ...
How to implement this right ?
Thanks a lot.
Kim

Re: Postgresql sequence in primary key

Anonymous — Mon, 20 Nov 2006 12:03:14 GMT

Hi,
Have you checked the Reject checkBox ?
I try to reproduce your issue and it works fine, the 'null' value becomes 92101.
But another problem is present, we know this bug, referenced in our https://community.talend.com/t5/Archive/merging-two-source-files/td-p/167056.
It will be fixed in the next Talend version.
Regards.