topic Re: Postgresql database, schemas and case-sensitivity in Installing and Upgrading

Postgresql database, schemas and case-sensitivity

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

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

Re: Postgresql database, schemas and case-sensitivity

Anonymous — Mon, 16 Oct 2006 10:31:01 GMT

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

Re: Postgresql database, schemas and case-sensitivity

Anonymous — Tue, 17 Oct 2006 15:50:33 GMT

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

Re: Postgresql database, schemas and case-sensitivity

Anonymous — Thu, 19 Oct 2006 09:52:27 GMT

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: g::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

Re: Postgresql database, schemas and case-sensitivity

Anonymous — Thu, 19 Oct 2006 12:44:22 GMT

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.

Re: Postgresql database, schemas and case-sensitivity

Anonymous — Thu, 19 Oct 2006 13:50:48 GMT

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

Re: Postgresql database, schemas and case-sensitivity

Anonymous — Thu, 19 Oct 2006 14:07:45 GMT

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

Re: Postgresql database, schemas and case-sensitivity

Anonymous — Fri, 03 Nov 2006 08:16:11 GMT

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

Re: Postgresql database, schemas and case-sensitivity

Anonymous — Fri, 03 Nov 2006 10:14:29 GMT

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