PostgreSQL Advice

So in my ongoing travels down technological rabbit holes I’ve never visited before, I’m learning PostgreSQL which is an open source database with a lot of the power of SQL Server minus the fun licensing fees. If I ever run my own shop, I’d prefer not to pay the Microsoft Tax so it pays to learn something new now. I chose PostgreSQL over MySQL for lots of really good reasons, most of them random.

One of the biggest things I’ve discovered as a semi-gotcha is that as a general best practice, you really ought to make all your table names lower case. This isn’t mandatory but if you don’t, your queries are a much bigger pain in the ass to write. For example, let’s say I have a table that I want to call Team. If I capitalize it, my queries all have to look like this:
SELECT * FROM "Team";
If I don’t capitalize it, my queries look like this:
SELECT * FROM team
It may seem like a really tiny difference but when you start having to put all your table names in quotes, it’s a massive pain in the ass. I have no idea why it’s implemented this way though I’m sure there must be some reason. I’m trying to decide whether or not to go back in and redo the entire database I’ve got set up.

Overall, I’m happy with PostgreSQL but this little gotcha is one to look out for. I’m sure it’s in the docs somewhere and if I had only read them before I started up, I might not be writing this.

Leave a Reply

Your email address will not be published. Required fields are marked *