psycopg2 - 'Relation does not exist' in Postgresql database

Question

I'm trying to figure out why I can't access a particular table in a PostgreSQL database using psycopg2. I am running PostgreSQL 11.5

If I do this, I can connect to the database in question and read all the tables in it:

import psycopg2

try:
connection = psycopg2.connect(user = "postgres",                #psycopg2.connect() creates connection to PostgreSQL database instance
                              password = "battlebot",
                              host = "127.0.0.1",
                              port = "5432",
                              database = "BRE_2019")

cursor = connection.cursor()                                #creates a cursor object which allows us to execute PostgreSQL commands through python source

#Print PostgreSQL version
cursor.execute("""SELECT table_name FROM information_schema.tables
   WHERE table_schema = 'public'""")

for table in cursor.fetchall():
    print(table)

The results look like this :

('geography_columns',)
('geometry_columns',)
('spatial_ref_sys',)
('raster_columns',)
('raster_overviews',)
('nc_avery_parcels_poly',)
('Zone5e',)
('AllResidential2019',)
#....etc....

The table I am interested in is the last one, 'AllResidential2019'

So I try to connect to it and print the contents by doing the following:

try:
    connection = psycopg2.connect(user = "postgres",                
    #psycopg2.connect() creates connection to PostgreSQL database instance
                              password = "battlebot",
                              host = "127.0.0.1",
                              port = "5432",
                              database = "BRE_2019")

    cursor = connection.cursor()                                #creates a cursor object which allows us to execute PostgreSQL commands through python source

    cursor.execute("SELECT * FROM AllResidential2019;")           #Executes a database operation or query. Execute method takes SQL query as a parameter. Returns list of result
    record = cursor.fetchall()

    print(record)


except (Exception, psycopg2.Error) as error:
    print("Error while connecting to PostgreSQL: ", error)

And I get the following error:

Error while connecting to PostgreSQL:  relation "allresidential2019" does not exist
LINE 1: SELECT * FROM AllResidential2019;

However, I can successfully connect and get results when attempting to connect to another table in another database I have (this works! and the results are the data in this table):

try:
    connection = psycopg2.connect(user = "postgres",                #psycopg2.connect() creates connection to PostgreSQL database instance
                              password = "battlebot",
                              host = "127.0.0.1",
                              port = "5432",
                              database = "ClimbingWeatherApp") .  #different database name

    cursor = connection.cursor()                                


    cursor.execute("SELECT * FROM climbing_area_info ;")          
    record = cursor.fetchall()

    print(record)


except (Exception, psycopg2.Error) as error:
    print("Error while connecting to PostgreSQL: ", error)

I can't figure out why I can retrieve information from one table but not another, using exactly the same code (except names are changes). And I am also not sure how to troubleshoot this. Can anyone offer suggestions?

Answer 1

Your table name is case-sensitive and you have to close it in double quotes:

SELECT * FROM "AllResidential2019";

In Python program it may look like this:

cursor.execute('SELECT * FROM "AllResidential2019"')

or you can use the specialized module SQL string composition:

from psycopg2 import sql

# ...

cursor.execute(sql.SQL("SELECT * FROM {}").format(sql.Identifier('AllResidential2019')))

Note that case-sensitive Postgres identifiers (i.e. names of a table, column, view, function, etc) unnecessarily complicate simple matters. I would advise you not to use them.

Answer 2

Likely, the reason for your issue is Postgres' quoting rules which adheres to the ANSI SQL standard regarding double quoting identifiers. In your table creation, you likely quoted the table:

CREATE TABLE "AllResidential2019" (
   ...
)

Due to case sensitivity of at least one capital letter, this requires you to always quote the table when referencing the table. Do remember: single and double quotes have different meanings in SQL as opposed to being mostly interchangeable in Python.

SELECT * FROM "AllResidential2019"
DELETE FROM "AllResidential2019" ...
ALTER TABLE "AllResidential2019" ...

It is often recommended, if your table, column, or other identifier does not contain special characters, spaces, or reserved words, to always use lower case or no quotes:

CREATE TABLE "allresidential2019" (
   ...
)

CREATE TABLE AllResidential2019 (
   ...
)

Doing so, any combination of capital letters will work

SELECT * FROM ALLRESIDENTIAL2019
SELECT * FROM aLlrEsIdEnTiAl2019
SELECT * FROM "allresidential2019"

See further readings on the subject:

• Omitting the double quote to do query on PostgreSQL
• PostgreSQL naming conventions
• Postgres Docs - 4.1.1. Identifiers and Key Words
• Don’t use double quotes in PostgreSQL
• What is the difference between single and double quotes in SQL?

Answer 3

I was facing the same error in Ubuntu. But in my case, I accidentally added the tables to the wrong database, which was in turn owned by the root postgres user instead of the new postgres user that I had created for my flask app.

I'm using a SQL file to create and populate the tables. This is the command that I used to be able to create these tables using a .sql file. This allows you to specify the owner of the tables as well as the database in which they should be created:

sudo -u postgres psql -U my_user -d my_database -f file.sql -h localhost

You will then be prompted for my_users's password.

sudo -u postgres is only necessary if you are running this from a terminal as a the root user. It basically runs the psql ... command as the postgres user.