Background I have a model I work on at work. Model output is a SQLite database; one database is created each run of the model. Currently there are about 30 tables and about 80 views. This exercise took maybe two days I wanted as few branches as possible in the implementing language: wherever possible, I want the same SQL that works for both. In the end, the only bit of SQL that isn't common to both is the primary key type in table declarations The same code in my system can now write to SQLite or Postgres databases. I have two methods, one to open a SQLite database and set some parameters, and one to open a Postgres database and run a couple bits of SQL. There's only about 20 lines difference, all in setup Finally, I liked the idea of being able to host the database on a server, and having folks write to that database. The current cycle goes: User develops inputs User runs model User calls me to figure out why it didn't do what they want Making it so that last step doesn't start with "ok, send me the database" seemed like it would be helpful. [The model solves what I heard one guy describe as "the hardest easy problem he knows of"; it sounds easy right up until the moment you try it] Why Postgres? There's a bunch of reasons that I went with Postgres for this exercise: Postgres Schemas. These were the big one; using schemas, I can segregate runs the same way I do with SQLite ["one SQLite .db for each run" maps neatly to "one schema for each run", and avoids the permission trainwreck in "one mysql database for each run"]

Postgres datatypes more closely match SQLite datatypes

MySQL angers me in new ways every time I use it, and I wanted to try something else

After fifteen minutes using it, I noticed postgres is full-blown anal about everything, which appeals to my inner OCD coder List of bullet points These are all the things that came up while I was doing this. These are the real reason I'm writing this up, for my own future reference SQLite Datatypes SQLite "INTEGER PRIMARY KEY" => Postgres "SERIAL"

SQLite has pretty weak typing. That's OK, and I enjoy the benefits it provides. But moving to Postgres, make sure you're putting the right types in the right columns.

I had a table of key-value pairs for "generic input parameters", of varying types. I made the values all "text", and do conversion in the model instead of expecting the database to do it



Use CAST; Postgres knows how to convert those text values to integers

Because SQLite's "boolean" is really an integer valued 0/1: SUM(boolCol) works in sqlite, not in Postgres: SUM(CAST(boolCol AS INTEGER)) works in both. I was using WHERE boolCol=1 and WHERE boolCol=0 in a bunch of places. Now it's just WHERE boolCol and WHERE NOT boolCol

SQLite-specific functions I was using a couple functions in SQLite that aren't available in Postgres. Luckily, Postgres lets the user create functions. So in the same DB setup code where I create the schema, I also now add a couple functions.

This way I don't have to create different versions of the view SQL code for different databases

GROUP_CONCAT : Implementation here

: Implementation here

DATETIME: I wrote a function that takes the same arguments as the SQLite one does, check that the last two are 'unixepoch' and 'localtime', and return Postgres' to_timestamp(epoch_seconds) GROUP BY General SQL thing: When using GROUP BY, *every* column you SELECT must be either an aggregate column or in the GROUP BY clause SQLite lets you SELECT columns that aren't one of these things; if you know of some relation where it doesn't matter which row it pulls the value from after aggregation, it'll always be the same, then it's a convenient shortcut Postgres doesn't like that

When using GROUP BY, postgres is more anal about ambiguity. Fully specify the column name [ie, with a "table." prefix] for columns in the GROUP BY clause Other stuff Postgres demands that every subselect be aliased to something, even if all you're doing is a single subselect, and then an aggregate function against it

SQLite lets you get away with using double-quotes for values. Not so Postgres

Postgres doesn't like "IF NOT EXISTS" on CREATE {TABLE,VIEW,INDEX}

SQLite lets you create tables with FK constraints to other tables that don't exist. FKs are enforced in SQLite at write-time. Postgres wants them all correct for DB correctness. Simple bit of table creation re-ordering

SQLite lets you use an ON clause in joins with a table further down the list of joins. Postgres doesn't

Triggers: Probably won't be portable. This incentivised me to look at my triggers in more detail, and in every case but one I found there was a better way to achieve the same goal. I'm calling this a plus [the one remaining ease actually only solves a problem that's specific to SQLite anyway] Afterthoughts It's still faster to have people run the SQLite version and mail me the db than it is to wait for the model to write to Postgres Postgres is so much slower, I think I'm probably doing something wrong This helped though I found a bunch of places where my code should never have worked; this is why porting stuff is always good. [Same with Linux/OSX/Windows porting, but perhaps that's a topic for another day] Overall, SQLite is just way more easygoing about everything. All kinds of stuff you can get away with in SQLite, Postgres won't stand for This was fun Addendum based on some feedback I posted this page to reddit/r/PostgreSQL and got some feedback. This should answer most of that Some of these things are better in newer versions of Postgres. I was specifically including 8.3 in my targets

Having the *exact same* SQL for different DBs was a primary goal. I didn't want to: Litter my code with if(dbType==pg) do_something; else do_something_else; Use the same PK type but then have to run an extra SQL query to create the sequence for each table Switch between GROUP_CONCAT and STRING_AGG based on database

Overall, everything here now works in both databases. There are lots of things that "could be done a different way with Postgres 9.1", but that'd be taking away the 8.3 ability. I always find it faintly annoying when people are willing to sacrifice backward compatability without actually gaining anything