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

Question

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 1

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.

Answer 2

At this moment, Redshift still doesn't support RETURNING syntax, and I couldn't find here a satisfying answer. So I'm posting a generic solution in case anyone needs it.

The only assumption for this solution is that you know how many records you've just inserted. Assuming x is the numbers of records inserted, you can run this query:

SELECT id 
FROM table 
ORDER BY id DESC
LIMIT {x}

Very important! you have to run this query together with the insert one in the same transaction. Otherwise, it won't work.