Get the auto id for inserted row into Redshift table using psycopg2 in Python

I am inserting a record into a Amazon Redshift table from Python 2.7 using psycopg2 library and I would like to get back the auto generate primary id for the inserted row.

I have tried the usual ways I can find here or in other websites using google search, eg:

conn=psycopg2.connect(conn_str)
conn.autocommit = True

sql = "INSERT INTO schema.table (col1, col2) VALUES (%s, %s) RETURNING id;"

cur = conn.cursor()
cur.execute(sql,(val1,val2))
id = cur.fetchone()[0]

I receive an error on cur.execute line :

ProgrammingError: syntax error at or near "RETURNING"

Does anybody know how to fix this or accomplish the same thing?

I have to use psycopg2 in my code

Answer

Currently not possible with Redshift, as it doesn’t support returning the last insert id via the RETURNING syntax. What you might need to do is use a SELECT MAX(id) FROM schema.table; inside a transaction, which probably not quite what you wanted to hear but appears to be the best you can do with the current state of Redshift.