Tuesday, November 9, 2010

Gotcha: case sensitivity

In this short post we'll cover an aspect that catches out many new PostgreSQL users, that of case sensitivty.

Let's create a database called SuperFoods:

postgres=# create database SuperFoods;
CREATE DATABASE

From the last post we saw that we can use psql's \l meta-command to list the databases:

postgres=# \l
                                 List of databases
    Name    |    Owner     | Encoding  | Collation | Ctype |   Access privileges  
------------+--------------+-----------+-----------+-------+-----------------------
 postgres   | postgres     | SQL_ASCII | C         | C     |
 superfoods | postgresuser | SQL_ASCII | C         | C     |
 template0  | postgres     | SQL_ASCII | C         | C     | =c/postgres          +
            |              |           |           |       | postgres=CTc/postgres
 template1  | postgres     | SQL_ASCII | C         | C     | =c/postgres          +
            |              |           |           |       | postgres=CTc/postgres
(4 rows)

Oh, it is listed as superfoods. What about if we try to connect to it:

postgres=# \c SuperFoods;
FATAL:  database "SuperFoods" does not exist
Previous connection kept

This is not what we wanted. PostgreSQL always switches everything to lower case unless we are very explicit in what we mean. We can do that with double quotes:

postgres=# create database "SuperFoods";
CREATE DATABASE
postgres=# \l
                                 List of databases
    Name    |    Owner     | Encoding  | Collation | Ctype |   Access privileges  
------------+--------------+-----------+-----------+-------+-----------------------
 SuperFoods | postgresuser | SQL_ASCII | C         | C     |
 postgres   | postgres     | SQL_ASCII | C         | C     |
 template0  | postgres     | SQL_ASCII | C         | C     | =c/postgres          +
            |              |           |           |       | postgres=CTc/postgres
 template1  | postgres     | SQL_ASCII | C         | C     | =c/postgres          +
            |              |           |           |       | postgres=CTc/postgres
(4 rows)

postgres=# \c "SuperFoods";
You are now connected to database "SuperFoods".
SuperFoods=#

The same is true of tables and other relations:

SuperFoods=# create table MyCamelCaseTable(name varchar, value int);
CREATE TABLE
SuperFoods=# \d
                List of relations
 Schema |       Name       | Type  |    Owner    
--------+------------------+-------+--------------
 public | mycamelcasetable | table | postgresuser
(1 row)

Many PostgreSQL users use lower case only in database and table names to avoid any confusion. However, there are times where a mixed case is justifiable and the most sensible approach for ones's particular situation. Just be aware of this common gotcha.

1 comment: