CODEWITHSUNDEEP
postgresqlpsycopg2amazon-redshift
Brosef
Brosef

Reputation: 3095

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.

Upvotes: 1

Views: 640

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23381

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.

Upvotes: 1

Related Questions