Home Ask Login Register

Developers Planet

Your answer is one click away!

Brosef February 2016

Getting the primary key after row insertion using redshift

I'm using postgresql 8.0.2 with amazon redshift and I'm trying to set up a INSERT command that also returns the PRIMARY KEY.

I was originally trying to do the following:

with get_connection() as conn:
    with conn.cursor() as cur:
    cur.execute('INSERT INTO orders (my_id, my_amount) \
                 VALUES (%s, %s) RETURNING row_id;', (some_id, some_amount))
    conn.commit()

However, the RETURNING command only works on postgresql 8.2 and above.

I saw that currval might be a possible way to get this to work, but I read that it requires a sequence object.

I'm trying to insert the following schema

CREATE SEQUENCE order_seq;

CREATE TABLE IF NOT EXISTS orders
(
    order_id INTEGER IDENTITY(1,1) PRIMARY KEY DISTKEY, 
)

Then do:

with get_connection() as conn:
    with conn.cursor() as cur:
        cur.execute('INSERT INTO orders (my_id, my_amount) \
                     VALUES (%s, %s);', (some_id, some_amount))
        conn.commit()
        cur.execute('SELECT currval();')
        row_id = cursor.fetchone()[0]

UPDATE: Sequence objects are not supported by redshift either. I feel like this is a pretty basic procedure but there is no easy way to get a reference to the current row.

Answers


Jorge Campos February 2016

Just define your column as:

order_id INTEGER PRIMARY KEY DISTKEY

And with your sequence created order_seq use this as insert command:

cur.execute('INSERT INTO orders (order_id, my_id, my_amount) \
                 VALUES (nextval(''order_seq''), %s, %s);', (some_id, some_amount))

Since you are using a sequence you have to add the field on the insert command to use the nextval properly.

And to retrieve current sequence value do as follow:

cur.execute('SELECT currval(''order_seq'')')
row_id = cursor.fetchone()[0]

I'm not familirized with the language you are using so you may have to change the syntaxe to scape the double quotes I use.

The syntaxe of nextval and currval is like: nextval('sequenceName') and currval('sequenceName')

So if it does not support sequences the only way I see that it could solve your issue is following this steps:

  1. Open a transaction (so others wont get the same id)
  2. fetch max id of your table like select max(order_id) from orders into a variable
  3. use this value on the insert as it was the sequence.

Post Status

Asked in February 2016
Viewed 3,908 times
Voted 7
Answered 1 times

Search




Leave an answer


Quote of the day: live life