Обсуждение: Table names case sensitive?
Having been suitable impressed by the "PostgreSQL ready for mission critical apps" thread, I decided to bite the bullet and convert our database from Interbase to PostgreSQL. Almost immediately I encountered a puzzling problem. To make things easy, I am using the Borland "data pump" on an NT machine to copy our data from Interbase to PostgreSQL. Although the PostgreSQL tables are created correctly, the write operations are failing. It looks like the ODBC driver is attempting to create tables with upper case names but PostgreSQL is converting the names to lower case. When the data pump tries to write data into PostgreSQL, the writes fail because the tables are not found since their names are in now lower case. It looks like PostgreSQL is not consistent with table case conversions. For the heck of it, I hacked postres.c to convert the the SQL strings to lower case on the fly - this kluge made the import work correctly. I have searched the lists and FAQs to see if the table name case disparity is a common problem, but drew a blank. The attempted data flow is as follows: Interbase 4.0 (SuSE 6.1 Linux) ---> Data Pump (NT) ---> PostgreSQL(SuSE 6.1 Linux) Any suggestions? Yeah, I know this is a convoluted way to copy data over - if you have any better ideas, I'm all ears! Steve
Try exporting the tables (including table creation stmts) to a text file and then ASCII ftping the file to the Unix Server to convert the CRLF and import using pgsql...The documentation has the commands to import files... ----- Original Message ----- From: Stephen Birch <sbirch@ironmountainsystems.com> To: <pgsql-general@postgreSQL.org> Sent: Wednesday, November 24, 1999 11:47 PM Subject: [GENERAL] Table names case sensitive? > Although the PostgreSQL tables are created correctly, the write > operations are failing. It looks like the ODBC driver is attempting to > create tables with upper case names but PostgreSQL is converting the > names to lower case. When the data pump tries to write data into > PostgreSQL, the writes fail because the tables are not found since their > names are in now lower case. > > It looks like PostgreSQL is not consistent with table case conversions. > For the heck of it, I hacked postres.c to convert the the SQL strings to > lower case on the fly - this kluge made the import work correctly. > > I have searched the lists and FAQs to see if the table name case > disparity is a common problem, but drew a blank. > > The attempted data flow is as follows: > > Interbase 4.0 (SuSE 6.1 Linux) ---> Data Pump (NT) ---> PostgreSQL(SuSE > 6.1 Linux) >
> Try exporting the tables (including table creation stmts) to a text file and > then ASCII ftping the file to the Unix Server to convert the CRLF and import > using pgsql...The documentation has the commands to import files... > All identifiers are changed to lower case unless they are in double-quotes. If they use double-quotes in the creates, but not in the queries, you will have problems. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
That explains it. In fact, the creates do not use double quotes, but the queries do. I'll have to find a way to fix that. Steve > > All identifiers are changed to lower case unless they are in > double-quotes. If they use double-quotes in the creates, but not in the > queries, you will have problems. >
Stephen, Interesting that you are converting from Interbase. We have now reached the point where we only use 2 commercial pieces of software. Interbase and Visual SlickEdit. I would like to drop Interbase but currently we ship quite a few single user systems running on Win 9x. We have very good experience so far with Postgresql on internal projects. We use MySql too but only on systems that are pretty much read only. My goal is to migrate onto Postgresql as our only dbms, the developments over the last year or so have been great and I fully expect to achieve my goal in the next year or so. As the customers for our product that uses Interbase are charities we think that Linux and Postgresql (plus Apache and Jserv later Jakarta) make a great fit. For our importing we have found that is is sooo much faster to dump the data into the native format that the postgresql copy expects and then do the load on the server. By dumping a table at a time in the text format (new line for each row, tab between columns, \N for null, \\ for \, and \tab for tab) from your old system you can be loading in one table while dumping the next. We have found that copy loads the data soo much faster then anything else that this is definately worthwhile. Fortunately this text format is common to mysql and postgresql. On mysql for example we had an import that used a java program and prepared statements. It took several days to do the import. Using the copy method we got this down to a few hours. We had chosen interbase for 5 reasons - excellent jdbc support (but the postgresql jdbc support is also excellent) - support for unicode (but postgresql can now do this although we have not yet tried it) - multi-generational engine (postgresql has this since v6.5) - platform support - Linux, NT, Win 9x and netware (in fact netware is no longer really a requirement and so win9x is our only problem and I expect that we will persuade many off that in the next couple of years) - pricing for VAR's, interbase is good value (postgresql can't be beat though :-) We have recently had a few problems with Interbase on Linux so the pressure is on them (and they are defiantely not moving ahead as fast as postgresql is, all the recent versions have been very late and some big bugs have been left in the field for a long time). Dave
> We have recently had a few problems with Interbase on Linux so the > pressure is on them (and they are defiantely not moving ahead as fast as > postgresql is, all the recent versions have been very late and some big > bugs have been left in the field for a long time). Yes, we seem to move at lightning speed compared to commercial databases and MySQL. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> That explains it. In fact, the creates do not use double quotes, > but the queries do. I'll have to find a way to fix that. I have seen that before. Quotations either preserve case, or they don't, but it seems some db's don't honor that. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
We have started porting our application suite away from Interbase and over to PostgreSQL. Our main concern with Interbase was not money, but the inconvenience of dealing with user licenses. A great deal of our database interaction is machine to machine and does not fit the standard model of users. Porting our customers' data was fairly easy once we understood why the initial attempts were failing. To facilitate the conversion, we created a temporary modification to the PostgreSQL back end which strips out the problem quotes. Then the conversion using Borland's data pump ran like a champ. Ok - it isn't fast, but speed is not an issue for us. Rather, we want to be able to convert data with the least amount of [human] effort. Our plan is to keep using Interbase until all of our applications are ported, grabbing updates periodically. Finally, we will do a last conversion and shut down Interbase - and kiss irritating license files good-bye forever. Converting our Win32 front end (built with BCB4 C/S) from IB to PG only took a couple of days. We snapped the PG ODBC driver in place on the NT box, fired up the Linux/PG postmaster with -d 3 and then corrected minor incompatibilities as they popped up. From the little experience we have with PG so far, it looks damn good. Gone are the black-box GUIs of windows, to be replaced with super cool psql - which we love (far more than isql). The PG man pages and on-line help are also fantastic. We are looking forward to the day when we can pull the plug from our last Windows machine and the repercussions of our terrible decision to trust NT will finally come to and end. As a digression - I will never, ever forget the disbelief on our programmers faces when they realized that the Microsoft "flagship server" we had just committed to (NT) really did not include the basic services such as telnetd, nfsd, or UUCP. What a joke. I just wish PostgreSQL had foreign keys (referential integ) - oh well, real soon now! Steve PS What sort of problems did you have with IB - we only had one (growing log files) and have a solution to that. From every other respect IB has served us well. In fact, if Interbase hadn't broken user SuSE 6.2, we may have stuck with it. David Warnock wrote: > Stephen, > > Interesting that you are converting from Interbase. We have now reached > the point where we only use 2 commercial pieces of software. Interbase > and Visual SlickEdit. I would like to drop Interbase but currently we > ship quite a few single user systems running on Win 9x. > > We have very good experience so far with Postgresql on internal > projects. We use MySql too but only on systems that are pretty much read > only. > > My goal is to migrate onto Postgresql as our only dbms, the developments > over the last year or so have been great and I fully expect to achieve > my goal in the next year or so. As the customers for our product that > uses Interbase are charities we think that Linux and Postgresql (plus > Apache and Jserv later Jakarta) make a great fit. > > For our importing we have found that is is sooo much faster to dump the > data into the native format that the postgresql copy expects and then do > the load on the server. > > By dumping a table at a time in the text format (new line for each row, > tab between columns, \N for null, \\ for \, and \tab for tab) from your > old system you can be loading in one table while dumping the next. We > have found that copy loads the data soo much faster then anything else > that this is definately worthwhile. > > Fortunately this text format is common to mysql and postgresql. On mysql > for example we had an import that used a java program and prepared > statements. It took several days to do the import. Using the copy > method we got this down to a few hours. > > We had chosen interbase for 5 reasons > > - excellent jdbc support (but the postgresql jdbc support is also > excellent) > - support for unicode (but postgresql can now do this although we have > not yet tried it) > - multi-generational engine (postgresql has this since v6.5) > - platform support - Linux, NT, Win 9x and netware (in fact netware is > no longer really a requirement and so win9x is our only problem and I > expect that we will persuade many off that in the next couple of years) > - pricing for VAR's, interbase is good value (postgresql can't be beat > though :-) > > We have recently had a few problems with Interbase on Linux so the > pressure is on them (and they are defiantely not moving ahead as fast as > postgresql is, all the recent versions have been very late and some big > bugs have been left in the field for a long time). > > Dave
Stephen, > >From the little experience we have with PG so far, it looks damn good. > Gone are the black-box GUIs of windows, to be replaced with super cool psql > - which we love (far more than isql). The PG man pages and on-line help > are also fantastic. Postgresql is certainly an easier db to work with than interbase with that absolutely terrible isql also as you say the docs are a more accessible. > We are looking forward to the day when we can pull the plug from our last > Windows machine and the repercussions of our terrible decision to trust NT > will finally come to and end. As a digression - I will never, ever forget > the disbelief on our programmers faces when they realized that the > Microsoft "flagship server" we had just committed to (NT) really did not > include the basic services such as telnetd, nfsd, or UUCP. What a joke. The advantage of running my own company is that we have managed to keep right away from MS rubbish. Dave > PS What sort of problems did you have with IB - we only had one (growing > log files) and have a solution to that. From every other respect IB has > served us well. In fact, if Interbase hadn't broken user SuSE 6.2, we may > have stuck with it. Our biggest problems are with installation of the jdbc stuff on newer versions of Debian.