Handling objects with quoted names

PostgreSQL object names can contain spaces and mixed case characters if we enclose the table names in double quotes. This can cause some difficulties, so this recipe is designed to help you if you get stuck with this kind of problem.

Case sensitivity issues can often be a problem for people more used to working with other database systems, such as MySQL, or for people who are facing the challenge of migrating code away from MySQL.

Getting ready

First, let's create a table that uses a quoted name with mixed case, such as the following:

CREATE TABLE "MyCust" 
AS
SELECT * FROM cust;

How to do it…

If we try to access these tables without the proper case, we get this error:

postgres=# SELECT count(*) FROM mycust;
ERROR:  relation "mycust" does not exist
LINE 1: SELECT * FROM mycust;

So, we write it in the correct case:

postgres=# SELECT count(*) FROM MyCust;
ERROR:  relation "mycust" does not exist
LINE 1: SELECT * FROM mycust;

This still fails, and in fact gives...