case sensitivity on table & column names / workaround?
От | postgresql.org@tgice.com |
---|---|
Тема | case sensitivity on table & column names / workaround? |
Дата | |
Msg-id | 4491A34B.3040704@tgice.com обсуждение исходный текст |
Ответы |
Re: case sensitivity on table & column names / workaround?
(Tony Caduto <tony_caduto@amsoftwaredesign.com>)
Re: case sensitivity on table & column names / workaround? (Ludek Finstrle <luf@pzkagis.cz>) |
Список | pgsql-odbc |
Hi guys, Once again, as with my last post, this may be slightly off topic for the ODBC list, but I thought I'd try here again. I'm attempting to get a MSSQL 7 server to replicate a few tables (some of which are large) to a PG 8.0 installation. I only recently was pleasantly surprised to find that MSSQL 7 is supposed to support replication (even at a transactional level) to basically any ODBC driver which has a few features, which Postgres's seems to have. I also read this very helpful blog entry: http://people.planetpostgresql.org/mha/index.php?/archives/87-Replicating-from-MS-SQL-Server-to-PostgreSQL.html which help me over a few initial bumps in the road. One issue that the poster didn't seem to address and that I'm now struggling with is not one of great importance, since there is a known workaround, it just could be incredibly painful up front and then a large annoyance down the road. It has to do with case sensitivity on table & column identifiers. I'm fairly sure I understand how it works in Postgres, after experiencing it earlier, and reading this section of the docs: http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS The tables & columns in my MSSQL 7 database have capitalization, like "TableName" and "ColumnName". When MSSQL prepares the DDL & DML statements to go through ODBC, it appears that it is double quoting all of the identifiers. If it wouldn't do this, or if I could optionally control it, this would all go away. But since it does, PG correctly creates the objects with the original capitalization. The problem that causes me in my SQL on the PG side is that while I have written it with the same capitalization, for readability, I don't put double quotes on the identifiers, you know, because it's a pain in the ass. So as far as I can see, my options are: 1) Change *all* of my present and future PG code for this database (that touches the MSSQL tables, at least) to always surround table & column names with double quotes. 2) Figure out a way to get MSSQL or the PG ODBC driver to drop or ignore those double quotes on the SQL coming from the replication process. 3) Figure out a way for PG to modify its behavior and allow non-double-quoted identifiers to match ones that were originally double quoted. So I'm coming to the list to see if you guys have any idea how I might accomplish anything in the area of #2 or #3. I wouldn't expect you to have any pointers on changing MSSQL's behavior, but thought perhaps there's something I could shove around in the ODBC driver or in the PG configuration itself (though I'm not holding my breath on it). Pardon my gratuitous verbosity (and perhaps that phrase's redundancy?). For those of you who are still reading, I'd appreciate hearing your thoughts. Thanks, John
В списке pgsql-odbc по дате отправления:
Предыдущее
От: postgresql.org@tgice.comДата:
Сообщение: Re: float8 auto truncation issue in ODBC v. PGSQL
Следующее
От: Tony CadutoДата:
Сообщение: Re: case sensitivity on table & column names / workaround?