postgresql ProgrammingError: function identifier(unknown) does not exist

Question

I'm trying to use psycopg2.sql.SQL to compose my query. I've referred to the docs but I'm unable to get this to execute. I keep getting the above programming error

Here's the example code:

import psycopg2.sql as sql

query = sql.SQL("SELECT id from {} where country={}".format(sql.Identifier('country_sector'), sql.Identifier('UK')))

cur = dbobj.conn.cursor()
cur.execute(query)
data = cur.fetchall()

And here is the error:

ProgrammingError: function identifier(unknown) does not exist
LINE 1: SELECT id from Identifier('country_sector') where country=Id...
                       ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

This suggests to me that I need to install some extension in postgres but a lot of googling has not helped.

Any advice much appreciated.

Could you please try to print the content to the terminal of Identifier('country_sector')? What do you get? — Kata
– Kata, Commented Mar 23, 2019 at 7:11
I get the same as in the error part at LINE 1... SELECT id from Identifier('country_sector') where country=Identifier('UK') — James Edwards
– James Edwards, Commented Mar 23, 2019 at 7:17
Try to change import psycopg2.sql as sql to import psycopg2 as sql — Kata
– Kata, Commented Mar 23, 2019 at 7:19
I’m trying to use the sql module within psycopg2: initd.org/psycopg/docs/sql.html — James Edwards
– James Edwards, Commented Mar 23, 2019 at 7:32
I can see that, but the documentation says from psycopg2 import sql and you are using import psycopg2.sql as sql That is not the same Try to rewrite that part exactly as the documentation says. — Kata
– Kata, Commented Mar 23, 2019 at 7:39

Kaushik Nayak · Accepted Answer · 2019-03-23 07:55:21Z

3

You're using the format() wrongly. This works

import psycopg2
import psycopg2.sql as sql
conn=psycopg2.connect("dbname='mydb' user='myuser' ")
cur = conn.cursor()
cur.execute(
sql.SQL("SELECT id from {} 
        where country=%s").format(sql.Identifier('country_sector')),
['UK']
)
data = cur.fetchall()

answered Mar 23, 2019 at 7:55

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jonathan Over a year ago

I'm having the same issue. I believe that the issue happens when trying to pass date literal values. you can see that mogrify() works... but execute yields this error: ``` target = sql.Identifier('dwh.order_fact') template = "SELECT count(*) FROM {target} WHERE order_date = %s" query = sql.SQL(template.format(target=target)) cur.execute(query,['2019-01-01']) ProgrammingError: function identifier("unknown") does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. ```

Justin S Over a year ago

This was the problem for me, too. I was using the format() method of my string, but it should be the format() method for the sql.SQL class. Just have to move the closing parenthesis from: query = sql.SQL("SELECT id from {} where country={}".format(sql.Identifier('country_sector'), sql.Identifier('UK'))) to: query = sql.SQL("SELECT id from {} where country={}").format(sql.Identifier('country_sector'), sql.Identifier('UK'))

Jonathan · Accepted Answer · 2019-11-08 19:08:49Z

I had the same issue, this solution might help.

The main takeaway is that you should use the psycopg2.sql.format() for the formatting of all named literals.

For table identifiers, note that in version 2.7.7, sql.Identifier() only accepts a single argument (multiple string args was added in 2.8). If you are using 2.7.7, you should format you sql template like so:

template = "SELECT * FROM {schema}.{table} WHERE date BETWEEN {ds} and {de}"
params = {
    "schema": sql.Identifier("myschema"),
    "table": sql.Identifier("mytable"),
    "ds": sql.Literal("2019-09-01"),
    "de": sql.Literal("2019-10-01")
}
query = sql.SQL(template).format(**params)

If you do it correctly, type(query) should be <class 'psycopg2.sql.Composed'>

My understanding is that the vars param in execute is mainly intended to pass in values for INSERT, etc. Hope this helps!

Collectives™ on Stack Overflow

postgresql ProgrammingError: function identifier(unknown) does not exist

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related