Schema names in psycopg2 identifiers

Question

I want to use the sql submodule of psycopg2 to write clean dynamic SQL:

from psycopg2 import sql
...
cursor.execute(sql.SQL("SELECT * FROM {}").format(sql.Identifier('myschema.mytable'))

This creates the following query:

SELECT * FROM "myschema.mytable"

Here I get an Relation "myschema.mytable" not found. exception.

How do I handle the schema name properly? The following statements would work, but how do I create them with psycopg2?

SELECT * FROM myschema.mytable
SELECT * FROM myschema."mytable"
SELECT * FROM "myschema"."mytable"

edit: clarified schema prefix

Portable

I just tried it in DBeaver: SELECT * FROM mytable; works fine while SELECT * FROM "mytable"; gives an error. I have to add that mytable contains an schema prefix, which may cause the problem. – Portable

Answer 1

The construction

sql.Identifier('myschema.mytable')

is treated as a single quoted identifier, as can be seen from the produced query. You should pass the schema and table name as separate identifiers to format:

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

Note that the schema and table name must match exactly, case and all, since psycopg2's SQL string composition tools produce quoted identifiers, and quoted identifiers are case-sensitive.

Proposition

Answer 2

But the table in my PostgreSQL database is unquoted on purpose. This means mytable exists, but "mytable" does not.

You misunderstand what quotes do. In your case (i.e. a case without special characters in table name) the only thing double quotes do is they make the name case sensitive. If you have table with name MyTable then

SELECT * FROM mytable;

works because it is case insensitive while

SELECT * FROM "mytable";

does not because it is case sensitive. However

SELECT * FROM "MyTable";

will work and this is what you are looking for.

---

The other problem (as noted by @IljaEverilä in comments) is this:

SELECT * FROM "myschema.mytable"

which postgres treats as a table with name myschema.mytable because you've quoted the whole thing. I assume this is what you are looking for:

SELECT * FROM "myschema"."mytable"

i.e. you need a separate identifier for schema and seperate for table joined by ..

Early

Thanks for the explanation! – Portable

Answer 3

Since version 2.8 (released in Apr 4, 2019), you can pass multiple strings to sql.Identifier to represent a qualified identifier (e.g. a schema name + a table name).

cursor.execute(
    sql.SQL("SELECT * FROM {table}").format(
        table=sql.Identifier("myschema", "mytable")
    )
)

# SELECT * FROM "myschema"."mytable"

See: https://www.psycopg.org/docs/sql.html#psycopg2.sql.Identifier

Teacher

This is so much better, keeps the actual query little cleaner. – Creel