I am creating a Django application using PostgreSQL (PSQL) for my database and was nearly finished with the API when I discovered some strange behavior. After successfully testing the API in the Django app, I decided to run some basic queries on the database. I received the following error for nearly every field in the app:

    select MaxSceneKey from game_progress_gameplaykeys;
    ERROR:  column "maxscenekey" does not exist
    LINE 1: select MaxSceneKey from game_progress_gameplaykeys;
                   ^
    HINT:  Perhaps you meant to reference the column "game_progress_gameplaykeys.MaxSceneKey".

I was getting the same result for every field in the table that I tried (and when I try to include the table name as the hint suggests), except for ‘user_id’ and ‘objective'.

I confirmed that the fields existed using \d+ game_progress_gameplaykeys, tried changing some of their field types, and even upgraded from Postgres 9.5 to 10.5 (I was planning to do this anyway).

After a bunch of searching, I found the issue:

“All identifiers (including column names) that are not double-quoted are folded to lower case in PostgreSQL.” from https://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive

I created camelCase field names in my Django app based on what the field names were previously in my application (written in C#).

I decided to fix this (for now) by fixing my models to all use snake_case and using https://github.com/vbabiy/djangorestframework-camel-case to switch the keys from camelCase to snake_case when they come into the API. One issue solved!

While debugging that issue, I decided to update my laptop’s code + postgres version since I hadn’t worked on it in a while and wanted to see if the issue was just on my desktop. When I reinstalled PSQL, I couldn’t seem to log into it using the user I was creating. Using the postgres user was fine, though.

I finally figured out the issue was that PSQL was running on port 5433, not 5432 (the default). After that, I was puzzling over what could be running on 5432 since ‘netstat’ and ‘lsof’ revealed nothing else running on my WSL Ubuntu VM. As I was searching around, I saw someone mention that really only PSQL should be running on that port, and I realized I had installed PSQL on Windows on that machine before I moved over to WSL. I uninstalled that, switched back to 5432 in Linux, restarted PSQL, and boom, good to go.

While I was debugging that issue, I learned some good information about PSQL along the way:

/etc/postgresql/10/main/postgresql.conf allows you to set and check the port that PSQL is running on.

/etc/postgresql/10/main/pg_hba.conf allows you to set different security protocols for connections to PSQL. Notable for local development: set the local connection lines to ‘trust’ so you don’t have to enter a password when logging in.

Note: you need to restart the PSQL server for either of these changes to take effect. Note 2: MORE IMPORTANT NOTE: Don’t use trust anywhere other than a local version of PSQL. Ever.

These are the lines I had to change to get that to work (may be different in versions of PSQL other than 10.5):

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust