Thread: Tables/Columns in upper-case?
Greetings,
Using the ODBC drivers under Windows 2000 and Access I've managed to
'export' a table from Access to Postgres. That's all well and good,
however, when the table shows up in Postgres it's all *uppercase* for
the name, and for all of the columns. This is a rather large problem
when everything I do in psql ends up in lowercase. Shouldn't the ODBC
driver be changing it all to lowercase, or the Postgresql server doing
it or something?
This is a rather annoying problem, unfortuantely. Any suggestions?
It's the stable non-unicode driver.
Thanks,
Stephen
Attachment
Hello, I guess that it is an Access issue or its MS driver, because I use OpenOffice 1.1 connected to a PostgreSQL through odbc and can use upper or lower case. Actually, odbc adds double quotes in sql commands to encapsulate upper case names in order to work in pg. It will be not a good idea to incorporate specific programs "features" into a generic driver manager. Maybe into an Access odbc driver... Suggestion: After exporting to PostgreSQL, execute a dump to a text file. Then, edit it to use only lower case and execute the sql into pg to create new lowercase tables. Good luck. Andre Felipe http://www.andrefelipemachado.hpg.ig.com.br/linux/index.html Brazilian portuguese
* Andre Felipe Machado (andremachado@techforce.com.br) wrote:
> I guess that it is an Access issue or its MS driver, because I use OpenOffice
> 1.1 connected to a PostgreSQL through odbc and can use upper or lower case.
That just means openoffice must do the upper->lower conversion for you.
> Actually, odbc adds double quotes in sql commands to encapsulate upper case
> names in order to work in pg.
Hrm, so you can use ""'s around things in psql to maniuplate those
tables? That's kind of annoying, but might be manageable (at least, to
rename the tables).
> It will be not a good idea to incorporate specific programs "features" into a
> generic driver manager. Maybe into an Access odbc driver...
Eh.
> Suggestion:
> After exporting to PostgreSQL, execute a dump to a text file.
> Then, edit it to use only lower case and execute the sql into pg to create new
> lowercase tables.
That'd be rather silly when I can just rename them by putting the table
name in ""s. That's kind of annoying though, imv. :)
Thanks,
Stephen
Attachment
--- Stephen Frost <sfrost@snowman.net> wrote: > * Andre Felipe Machado > (andremachado@techforce.com.br) wrote: > > I guess that it is an Access issue or its MS > driver, because I use OpenOffice > > 1.1 connected to a PostgreSQL through odbc and can > use upper or lower case. > > That just means openoffice must do the upper->lower > conversion for you. > > > Actually, odbc adds double quotes in sql commands > to encapsulate upper case > > names in order to work in pg. > > Hrm, so you can use ""'s around things in psql to > maniuplate those > tables? That's kind of annoying, but might be > manageable (at least, to > rename the tables). > > > It will be not a good idea to incorporate specific > programs "features" into a > > generic driver manager. Maybe into an Access odbc > driver... > > Eh. > > > Suggestion: > > After exporting to PostgreSQL, execute a dump to a > text file. > > Then, edit it to use only lower case and execute > the sql into pg to create new > > lowercase tables. > > That'd be rather silly when I can just rename them > by putting the table > name in ""s. That's kind of annoying though, imv. > :) Take a look at the Database Migration Wizard that comes with PgAdminII (and PgAdminIII as well? I haven't checked that out). Amongst its many handy features, you can opt to lowercase all identifiers (e.g. table names). You should be able to find it via http://pgadmin.postgresql.org . __________________________________ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus
We used PgAdminIII to migrate an Access database to PostgreSQL, but I believe that we had to set all the table names to lowercase in Access before we migrated it. It did, however, automatically set our column names to lowercase for us. Consequently, if we didn't set the table names to lowercase in Access before migrating, then we had to place single quotes around the table names in all of our SQL statements (which continued to use the capital letters in them), but if we set the table names to lowercase before the migration, then we didn't have to put the single quotes around the table names in our SQL statements when they were hitting the Postgres DB (despite the fact that the names in the SQL statements retained the capitalization as defined in the Access table... weirdness). Jeff Eckermann wrote: > Take a look at the Database Migration Wizard that > comes with PgAdminII (and PgAdminIII as well? I > haven't checked that out). Amongst its many handy > features, you can opt to lowercase all identifiers > (e.g. table names). You should be able to find it via > http://pgadmin.postgresql.org .
> -----Original Message----- > From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com] > Sent: 22 January 2004 00:10 > To: Stephen Frost; Andre Felipe Machado > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] Tables/Columns in upper-case? > > Take a look at the Database Migration Wizard that comes with > PgAdminII (and PgAdminIII as well? I haven't checked that > out). Nope, no migration wizard in pga3. :-( If there are any bored C++ programmers out there though... As for the quotes, neither psqlodbc or PostgreSQL will fold them to uppercase - Access must be doing that. The rule is, if unquoted, an object name may contain a through z, 0 though 9 or an underscore. If you try to use upper case characters, they will be folded to lowercase by the server. To use uppercase, or any other characters in identifiers you must quote them. Regards, Dave.
--- Ben Ramsey <br@benandliz.com> wrote: > We used PgAdminIII to migrate an Access database to > PostgreSQL, but I > believe that we had to set all the table names to > lowercase in Access > before we migrated it. It did, however, > automatically set our column > names to lowercase for us. Consequently, if we > didn't set the table > names to lowercase in Access before migrating, then > we had to place > single quotes around the table names in all of our > SQL statements (which > continued to use the capital letters in them), but > if we set the table > names to lowercase before the migration, then we > didn't have to put the > single quotes around the table names in our SQL > statements when they > were hitting the Postgres DB (despite the fact that > the names in the SQL > statements retained the capitalization as defined in > the Access table... > weirdness). With PgAdminII's Migration Wizard, none of this is necessary. Dave Page confirms in a separate email that the Wizard has not yet been implemented in PgAdminIII, which is a pity, seeing that III is otherwise the "latest and greatest". > > Jeff Eckermann wrote: > > Take a look at the Database Migration Wizard that > > comes with PgAdminII (and PgAdminIII as well? I > > haven't checked that out). Amongst its many handy > > features, you can opt to lowercase all identifiers > > (e.g. table names). You should be able to find it > via > > http://pgadmin.postgresql.org . > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/
Hmm. I'll have to ask one of our other programmers how he converted the database, then. I could've sworn he used PgAdminIII to do it. -Ben Jeff Eckermann wrote: > With PgAdminII's Migration Wizard, none of this is > necessary. Dave Page confirms in a separate email > that the Wizard has not yet been implemented in > PgAdminIII, which is a pity, seeing that III is > otherwise the "latest and greatest".