pythonpostgresqlpsycopg2

Schema names in psycopg2 identifiers

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

Solution

  • The construction

    sql.Identifier('myschema.mytable')

    is treated as a single quoted identifier, as can be seen from the produced query. Please see the answer by @gg for how to handle qualified identifiers in Psycopg since version 2.8.

    (For older versions) 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.