I’m in the midst of moving a [http://www.bacula.org/ Bacula] system from [http://www.sqlite.org Sqlite] to [http://www.postgresql.org Postgres], and I’ve got to say it’s such a mess it’s actually hard to believe things are this bad; almost to the degree that you have to wonder if things were planned to be this bad. Here is the scenario: * Problem #1: The Bacula sql file for Sqlite uses unquoted [http://en.wikipedia.org/wiki/StudlyCaps StudlyCaps] for table names This is bad because it produces different behavior in different databases. In Sqlite, you end up with MiXeD cAsE identifiers. If you try to load it in Postgres, you get all lowercase identifiers. Not good. * Problem #2: The Bacula sql file for Postgres uses lowercase for table names So, loading this into Postgres gives you lowercase table names, which means you get something different than what users on Sqlite get. Also not good. * Problem #3: The Sqlite .dump command produces dumps with unquoted identifiers in the table name, but quoted identifiers in the INSERT statements! Really? This is a bug right? This couldn’t really be the desired behavior, could it? Clearly either quoting consistently or not quoting anything would both produce more consistent results, the only thing this buys you is incompatibility with other database products, which I don’t think is the goal. What I find awesome about all this is it’s one of those cases where if any of the above steps had been done a little differently, there wouldn’t be as big a problem. The Bacula Sqlite could quote it’s identifiers, making it much easier to convert to another database. The Postgres Bacula file could follow in the Sqlite files footsteps and set itself up for mixed case tables (Which normally I hate, but in this case it would be an improvement). Or possibly Sqlite could fix it’s dump to provide more consistent handling of identifiers. Or heck, just handle them inconsistently in the other direction, so you quote the create statements but not the INSERT ones, in this scenario even that would have been better. I also have a hard time believing I am the first person to notice this. I think someone must have solved this problem before no? I’m using the Centos5 rpms for all of this, so maybe there is something in one of these upstream packages I’m not aware that fixes this? Do I need to submit a patch to Bacula synchronizing all of the schemas across databases? Anyone? Bueller??