[resolved] !!!Remove double quotes while creating table in PostgreSQL!!!

One Star

[resolved] !!!Remove double quotes while creating table in PostgreSQL!!!

Hello,
I'm using a CSV file as input and putting it into my PostgreSQL database. Talend(TOS 6.2.0) creates tables and columns with double quotes. Since PostgreSQL uses case sensitive, so I gotta type in double quotes for every single statement in database. I'm looking for a solution to remove double quotes while creating tables and columns.
I searched on Internet for two days:
1. They said that I could modify the ITEM file of the job that I'm working on to completely fix this problem. It's called '' XXX_0.1.item". By changing the 'value' below, I created the table without double quotes only on its name. But the columns are still with double quotes.

2. I saw someone had asked the same question on the forum. But generally there's no exact answer.
3. To find out where the problem is, I also put a MySQL output to compare the Java code that Talend had created, the first one is for MySQL output, the second one is for PostgreSQL output:

Maybe the difference is just the different syntax in two DB...
So here I wanna describe this problem more precisely and look for my last hope.
Any answer would be appreciated.
Thank you.

Accepted Solutions
One Star

Re: [resolved] !!!Remove double quotes while creating table in PostgreSQL!!!

tell me about it!
this is due to the way the postgresql components are written. You can work around it by making everything in talend lowercase (it will still quote the generated sql but since pg downcases SQL unless you double quote it, it will work) 
The long-term fix would be editing the components to not generate DDL with double quotes, or if you're like me just cursing alot when you're writing pg queries against talend created tables. 

Thanks a lot John!
Just to make a summary:
In fact the column names in CSV are in uppercase and I didin't change them. So PostgreSQL 'thinks' that I prefered uppercase and to keep them, PostgreSQL put double quotes. Now I gotta change the names in lowercase and everything's OK!

All Replies
One Star

Re: [resolved] !!!Remove double quotes while creating table in PostgreSQL!!!

Hi,
Could you give more détails pllease about content csv file.
Regards,
One Star

Re: [resolved] !!!Remove double quotes while creating table in PostgreSQL!!!

Hi,
Could you give more détails pllease about content csv file.
Regards,

Thanks for replying,
in fact i don't think it would be the problem of my csv file. Because even if I use an Excel file or Access table, I always got the same problem. But if I change the output, MySQL for example, there's no double quotes on table and column names.
So I'm wondering is it the problem of PostgreSQL? Or the java code that Talend had created?

Re: [resolved] !!!Remove double quotes while creating table in PostgreSQL!!!

tell me about it!
this is due to the way the postgresql components are written. You can work around it by making everything in talend lowercase (it will still quote the generated sql but since pg downcases SQL unless you double quote it, it will work) 
The long-term fix would be editing the components to not generate DDL with double quotes, or if you're like me just cursing alot when you're writing pg queries against talend created tables. 
One Star

Re: [resolved] !!!Remove double quotes while creating table in PostgreSQL!!!

tell me about it!
this is due to the way the postgresql components are written. You can work around it by making everything in talend lowercase (it will still quote the generated sql but since pg downcases SQL unless you double quote it, it will work) 
The long-term fix would be editing the components to not generate DDL with double quotes, or if you're like me just cursing alot when you're writing pg queries against talend created tables. 

Thanks a lot John!
Just to make a summary:
In fact the column names in CSV are in uppercase and I didin't change them. So PostgreSQL 'thinks' that I prefered uppercase and to keep them, PostgreSQL put double quotes. Now I gotta change the names in lowercase and everything's OK!