Postgresql database, schemas and case-sensitivity

One Star

Postgresql database, schemas and case-sensitivity

I work with a Postgresql database. I can't use postgresql schemas. Talend dosen't recognize them. It works only with the public schema. I also have an other problem with the case-sensitivity : Talend dosen't recognize tables or fields which contain capital letters... For example, noStreet is unknown and nostreet is ok.
Will it be corrected in further versions ?
Thanks a lot.
Kim

Accepted Solutions
Employee

Re: Postgresql database, schemas and case-sensitivity

Hi,
I have created a feature in our Bug Tracker about to retrieve VIEWS as TABLE in the Db Connection wizard.
Regards.

All Replies
Employee

Re: Postgresql database, schemas and case-sensitivity

Hello,
Have you define a Schema value for the Db PostgreSQL connection ?
Have you some problems in the repositories or to use it in the Job Designer ?
In the SQL properties of tDbInput (Job Designer) don't forget to specify your Schema as example : 'Select * from mySchema.myTable'
Regards
One Star

Re: Postgresql database, schemas and case-sensitivity

Hello !
It seems to be okay when you use double quote in the query for DBInput. You also can retreive metadata with upper and lower letters.
Thanks a lot
Kim
One Star

Re: Postgresql database, schemas and case-sensitivity

Hi !
I still have problems with postgresql.
I just want to insert data from a inputFile in a table on a Postgresql database. And, I can't.....
I have a Postgresql database called "testTalend", in which I have a schema called "myTalendSchema". In this schema, I have a table "Rue". The structure of the table :
CREATE TABLE "myTalendSchema"."Rue" (
"idLocalite" BIGINT NOT NULL,
"idRuePoste" BIGINT NOT NULL,
"libelle" VARCHAR(50) NOT NULL,
"genreRue" SMALLINT DEFAULT 0 NOT NULL,
"codePreposition" SMALLINT DEFAULT 0 NOT NULL,
"npaAdressage" VARCHAR(20) NOT NULL
) ;
I can create a connection with a right click on the DbConnections. And then, the schema. All is okay. You will find images below. You can also find the property of the DbOutput.
But when I try to execute the insert, I have this error message :
.....
ThreadStat started...
Connecting to talendStudio on port 3334...connected.
DBD:Smiley Tongueg::st execute failed: ERROR: column "idlocalite" of relation "Rue" does not exist
can't execute insert query
A thread exited while 2 threads were running.
Job writeTalend ended at 10:45 19/10/2006.
......
Talend can read fields with different cases (idLocalite <=> idlocalite) ? I don't think that...
Thanks a lot for you help.
Kim
Highlighted
Employee

Re: Postgresql database, schemas and case-sensitivity

Hello,
I have created the same Table in our PostgreSQL Database, and i have the same error for your job.
Then i have testing in another SQLClient and it's the same result as you can see in attach file.
It's a PostgreSQL bug :
In PostgreSQL the Queries are case sensitive, and it appears that PostgreSQL automatically lower-case the column names in queries before running them - even though the columns have mixed-case names.
The solution is to enclose colum_name with " character as INSERT INTO "mypostgres"."Rue" ("idLocalite","idRuePoste","libelle","genreRue","codePreposition","npaAdressage") VALUES (1,1,'tt',1,1,'uu');.
We are working on this solution, you can follow this features in our BugTracker.
Normally it will be include in our next Update Site(v1.0.1).
Best regards.
One Star

Re: Postgresql database, schemas and case-sensitivity

Hello !
When you do an insert query, you can put the ". But when you want to insert data from Talend to a postgres table, how to do ?
Best regards.
Kim
Employee

Re: Postgresql database, schemas and case-sensitivity

There is a bug with PostgreSQL queries, but we can workaround it in Talend, when the bug 205 will be resolved.
One Star

Re: Postgresql database, schemas and case-sensitivity

Hi !
We can't see views in the postgresql schema. How to reach a view on a database ? (I have the same problem with an odbc driver for AS400)
Thanks a lot
Kim
Employee

Re: Postgresql database, schemas and case-sensitivity

Hi,
I have created a feature in our Bug Tracker about to retrieve VIEWS as TABLE in the Db Connection wizard.
Regards.

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 3

Read about some useful Context Variable ideas

Blog

Talend Studio Improvements for API Services

Take a look at the Talend Studio improvements for API Services

Watch Now

Data Integration Success Stories

Take a look at some Data Integration success stories

Read