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/8 7-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#SQ L-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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: case sensitivity on table & column names / workaround?

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: case sensitivity on table & column names / workaround?

> 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: case sensitivity on table & column names / workaround?

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: case sensitivity on table & column names / workaround?

> 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster