Published: Tue 16 October 2018
By Andrew Mshar
In Django .
tags: PostgreSQL Django
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