Thread: Table names case sensitive?

Table names case sensitive?

From
Stephen Birch
Date:
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







Re: [GENERAL] Table names case sensitive?

From
"Kane Tao"
Date:
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)
>




Re: [GENERAL] Table names case sensitive?

From
Bruce Momjian
Date:
> 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

Re: [GENERAL] Table names case sensitive?

From
Stephen Birch
Date:
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.
>


Re: [GENERAL] Table names case sensitive?

From
David Warnock
Date:
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

Re: [GENERAL] Table names case sensitive?

From
Bruce Momjian
Date:
> 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

Re: [GENERAL] Table names case sensitive?

From
Bruce Momjian
Date:
> 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

Re: [GENERAL] Table names case sensitive?

From
Stephen Birch
Date:
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


Re: [GENERAL] Table names case sensitive?

From
David Warnock
Date:
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.