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”.