Обсуждение: case sensitivity on table & column names / workaround?
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
postgresql.org@tgice.com wrote: > > 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. Postgresql forces all sql to lower case unless you quote it (at creation time). Quoting forces the capitalization, and then you have to refer to it forever with quotes. The long term solution would be to change the MS SQL side of things to use all lower case. It's really a good idea on any db system to just use lower case for everything. Unless you are using Firebird or Interbase, then it works just the opposite of PG, it forces everything to uppercase. It must be the odbc driver doing the quoting because if you do the following in PG: create table MyTestDB( varchar(10) without oids; postgresql creates it as mytestdb if you do: create table "MyTestDB"( varchar(10) without oids; Then the capitals stay and you must always reference the table like so: select * from "MyTestDB" -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration
> 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. What about updateable views? It could be the way for replication and name changing. I'm not sure it's only an idea. Regards, Luf
Ludek Finstrle wrote: > What about updateable views? It could be the way for replication and name > changing. I'm not sure it's only an idea. Yes, I'd loosely considered this but then figured it wouldn't work b/c I wouldn't be able to change the capitalization of the column identifiers as well, but I guess I can do that with 'AS's in my CREATE VIEW statement, eh? Also, at the moment, the tables are readonly on the PG side so I don't even need them to be updateable, but it's good to know that that feature exists. I suppose the is the best solution for now, though still not quite ideal of course. Thanks for suggesting it, Luf; I'll give it a shot. Any chance we might see some sort of feature in the ODBC driver (or the backend) in the future to e.g., check an option that says "Ignore double quotes on identifiers (and thus allow them to be folded to lower case)"? Obviously, that'd probably be the most ideal situation for me here and perhaps anyone else who is doing something where they're tightly integrated with MSSQL or another backend that handles capitalization differently. Thanks again, jl
> Ludek Finstrle wrote: > >What about updateable views? It could be the way for replication and name > >changing. I'm not sure it's only an idea. > > Yes, I'd loosely considered this but then figured it wouldn't work b/c I > wouldn't be able to change the capitalization of the column identifiers > as well, but I guess I can do that with 'AS's in my CREATE VIEW > statement, eh? I hope so. > Any chance we might see some sort of feature in the ODBC driver (or the > backend) in the future to e.g., check an option that says "Ignore double > quotes on identifiers (and thus allow them to be folded to lower case)"? I don't think so. There are much more important issues. I'm not sure if it's driver issue. I think it's backend issue. Regards, Luf