[resolved] Talend & PostgreSQL Input Componant

[Anonymous]

Hello,
I'm trying to use a table in PostgreSQL 9.0 as an input (tPostGreSQLInput) in a talend job (using just a tlog for now) but I keep getting this error message :
Exception in component tPostgresqlInput_1
org.postgresql.util.PSQLException: ERROR: relation "priscilla.ref_col_collab" does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:235)
at priscilla.inputpostgre_0_1.INPUTPOSTGRE.tPostgresqlInput_1Process(INPUTPOSTGRE.java:1703)
at priscilla.inputpostgre_0_1.INPUTPOSTGRE.runJobInTOS(INPUTPOSTGRE.java:3716)
at priscilla.inputpostgre_0_1.INPUTPOSTGRE.main(INPUTPOSTGRE.java:3545)
I thought at first that it was because of Postgre case sensitivity, but it doesn't seem to be the case, and I have this problem for any postgreSQL table I try to use, although I have no trouble for Oracle or MySQL tables.
What I find really strange is that when I use this same table as an output (tPostGreSQLOutput) everything works fine and I can see through pgAdmin that the data is indeed charged in the table.
Can you please help me slove this issue? I'm still pretty new at Talend.

[Anonymous]

Hi CA,
Could you please indicate the build version you are using?
Best regards
Sabrina

[Anonymous]

Hello,
I'm using Talend Open Studio for MDM 5.4.1 (r111543)...
Regards
CA

[Anonymous]

Hi CA,
Can you show the query used in input component along with the metadata?
thanks
Vaibhav

[Anonymous]

Hi,
I left the by-default query using the context (I have lots of columns) :
"SELECT
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_id\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_matrcule_cegid\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_nom\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_prenom\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_nom_naissance\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_civilite\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_date_naissance\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_lieu_naissance\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_date_entree_entreprise\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_date_sortie_entreprise\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_disponibilite_txt\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_disponibilite_date\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_adresse\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_ville\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_pays\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_email_personnel\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_email_professionnel\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_tel_mobile\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_tel_fixe_perso\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_societe\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_bu\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_statut\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_categorie\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_tarif_cible\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_sstrtnt_nom_entreprise\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_p_responsable_dossier\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_p_numero_dossier\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_p_date_premier_contact\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_p_origine\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_p_parrain\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_mot_clef_cv_planning\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_mot_clef_cv_record\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_nb_annee_experience\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_periode_essai\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_competence_clef\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_niveau_etude\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_date_signature_embauche\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_type_contrat\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_type_stage\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_signataire_embauche\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_redacteur_embauche\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_util_login_aubay\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_util_login_gipssi\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_util_login_cegid\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_util_actif_gipssi\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_util_actif_planning\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_util_actif_teamtools\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_util_nom_gipssi\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_util_nom_record\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_util_nom_teamtools\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_nationalite\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_situation_familiale\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_securite_sociale\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_prochaine_visite_medical\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_derniere_visite_medicale\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_sexe\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_membre_ce\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_delegue_personnel\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_code_postal\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_id_gipssi\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_id_planning\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_id_teamtools\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_id_ad\",
\""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_id_record\"
FROM \""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\"
GROUP BY \""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_matrcule_cegid\"
HAVING ( COUNT( \""+context.REFERENTIEL1_Database+"\".\"ref_col_collab\".\"col_matrcule_cegid\") > 1 )"
What do you mean by "the metadata" ? Do you mean the parameters for the connection ? I don't know how to export those but when I try check connection, everything is ok ?
Regards
Claire

[Anonymous · Accepted solution]

This error was due to the fact that the automated query specifies the whole path for each column, and the "Schema" was blank in my case. The path to the database was thus wrong and the database not found, despite it working fine in case of outputs or just checking the connection. Once I changed it to "public" as it was in Postgres, it worked fine.
The solution was to check the Scheme field and complete it as it should be.