MSSQL server and PostgreSQL queries

hughespa · Post by **hughespa** » Tue 04 May 2010 04:21

Hi,

I've been looking at targeting PostgreSQL in addition to SQL Server.

Throughout my applications all my SQL table and field names are NOT quoted and are all uppercase.

When I run queries against PostgreSQL it seems to require quoted field names and fails if they are unquoted.

I know I can set QuoteNames := True on queries but is there anything I can do to get UniDAC to apply quotes globally for all queries (including any auto generated sql) when it's using the PostgreSQL provider and not when it's using SQL Server?

Alternatively, does anyone know if I can exclude this requirement to quote names on the PostgreSQL server or DB?

Or should I make sure all table and field names are lowercase on the PostgreSQL DB? I understand that that all unquoted names will be converted to lowercase. This might work...

Does anyone have a good suggestion what is best to do please?

Thanks in advance,
Regards, Paul.

bork · Post by **bork** » Thu 06 May 2010 13:31

Hello

PostgreSQL requires quoted names if you used quotes in the script when you created a table. For example pgAdmin always quote table and field names if you created a table by the constructor. So I recommend you to create all tables by a SQL script without quoting names, in this case you can execute any queries without quoting.

hughespa · Post by **hughespa** » Thu 06 May 2010 13:58

Thanks Bork.

I have been successful in creating all the table and field names in lowercase (I generate these automatically from scanning the source tables before loading data) and the uppercase SQL used elsewhere by my other applications -without quotes- works OK as expected.

Regards, Paul.