Adventures in POSTGRES: Lowercase Everything

So I’ve been trying to transition from using MSSQL to POSTGRES, and I’ve encountered a few bumps here and there.

One of the tutorials I was following recommended using the Chinook database (a handy test database which has set ups for multiple db providers) as a sandbox. The database uses the naming convention I’m familiar with which is UpperCamelCase for some db objects like tables and its columns. For other things like stored procedures or functions, it’s in LowerCamelCase. Imagine my shock when I ran a simple query to the table “Albums” in the default schema “public”.

select title from album

I got served a

relation “album” does not exist

It turns out that if objects like table names are in UpperCamelCase, then you have to wrap them in quotes before POSTGRES understands what you’re actually trying to do, like so

select "Title" from "Album"

So if you want to avoid all the pain and frustration of having to type in those extra double quotes every single time, keep your objects in lowercase.

create table artist(
id serial primary key,
title varchar(160) not null
);

The POSTGRES docs give good guidelines on this. I guess the convention is good in that it’s easier to type things everywhere, but I can just imagine how painful it would be to attempt to migrate an MSSQL database to POSTGRES later on.

As a bonus, not only did I learn about the casing problem, but I also learned that relations in “relational databases” had nothing to do with the lines drawn between tables we call “relationships”.

Book Review: Dreaming in Code

Dreaming in Code: Two Dozen Programmers, Three Years, 4,732 Bugs, and One Quest for Transcendent SoftwareDreaming in Code: Two Dozen Programmers, Three Years, 4,732 Bugs, and One Quest for Transcendent Software by Scott Rosenberg
My rating: 5 of 5 stars

“Nobody should start to undertake a large project. You start with a small trivial project, and you should never expect it to get large… If it doesn’t solve some fairly immediate need, it’s almost certainly overdesigned.” – Linus Torvalds, Linux Times, 2004

Life is complex. Trying to model even one real-life process into an ethereal tool will no doubt embody part of life’s complexity. But this isn’t immediately obvious to the optimistic programmer. A grand vision of building the-next-big-thing is enough to fuel one’s optimism and blind them to the fact that the-next-big-thing is like really, really big. This isn’t uncommon in the Software Industry where building things (small things) seem to not take as much time as other industries. Open an editor, write a few lines of code and you’ve got something on your screen unlike say a bridge where the first may take a few weeks or months after someone draws up the first draft (I have limited knowledge on bridge building so don’t quote me on this comparison). And when a programmer finds out that building software isn’t as easy as they thought it would be, they can’t help but wonder if there’s something they’re missing. And then they get a book like this and try to read about how other people are doing.

Your average pretentious software engineer might read the title of the book and think that “These must be some enterprise software engineers unlucky to be in one of those process heavy companies where they have to do some archaic process like waterfall or RUP. If only they’d done agile they wouldn’t be in this mess.” Surprisingly though, your average pretentious software engineer would be proven wrong as they find themselves in the shoes of the Chandler team. The team involved a lot of the brightest minds working on an open-source project with a huge budget that’s just enough to keep them going for more than 5 years. Depressingly enough they did go for more than 5 years. I’m very grateful to Mitch Kapor for giving the world a window into one of his most challenging projects. His openness and willingness to have this documented and shared just shows what kind of person he is.

The book itself is a fun read. It isn’t your computer science or management textbook nor is it your boring documentation of the Chandler team where the narrator jumps from one event to the next. Scott Rosenberg gives his readers a view of the conversations, meetings and other happenings inside the Chandler team’s office. From a programmer’s perspective, some events may even sound eerily familiar to their own encounters and conversations. I do appreciate his attempt at providing the occasional explanation of a principle or idea to the reader, sometimes even giving a short backstory at how that came to be. Some find that distracting, but I do find it helpful. This can even serve as a gateway to newbie software engineers trying to understand the world they work in and introducing them to some very helpful engineering principes while they’re at it. Though there are some concepts that he does seem hard pressed to explain in layman’s terms, but hey who doesn’t have to go through that challenge every once in a while? Concerning the book’s theme, one might get the impression that this book is one big what-not-to-do-when-building-software. There is much of that yes. We can benefit from other people’s hindsight, examples of which are when in early chapters/years they rebuilt much of what they could have reused from elsewhere. But some of those simply reflect the difficulty of building software. Where collaboration and figuring out how the team that builds the software works together and the direction they should take is a great deal important as actually building the software itself. There are other points in the project where taking a calculated risk does not mean that it stops being a risk such as the team’s investment against the web technologies of their time. There’s a lot to learn from every team. The team’s members themselves would no doubt have learned much from the experience. And readers of this book will find much to learn from them as well.

Great read. Learned a lot. Would recommend. 5 stars.

View all my reviews

This review was first posted on Goodreads