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.