New post
Question
· Nov 4, 2022

Get the primary id of inserted line in SQL

#Key Question #Python #SQL #InterSystems IRIS

I have a table, with autoincremented id

CREATE TABLE users (
    id SERIAL NOT NULL,
    name VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
)
SQL
SQL

I can add a new item there with an explicit id 

INSERT INTO users (id, name) VALUES (2, 'fred')
SQL
SQL

And while my id is autoincremented, I can omit it

INSERT INTO users (name) VALUES ('ed')
SQL
SQL

So, this time, I don't know the id, and I want to somehow get it.

I could do it with LAST_IDENTITY() function, but it just uses %RowID, and have no relation to the primary id

SELECT LAST_IDENTITY()
SQL
SQL

Gives 2, instead of 3

Is there any way, to make LAST_IDENTITY() match to the primary id key, or I just forget about it and use something else?

Product version: IRIS 2022.1
Discussion (11)3
Log in or sign up to continue
Dmitry Maslennikov · Nov 4, 2022

Found this way with IDENTITY and ALLOWIDENTITYINSERT=1

CREATE TABLE users (
	id identity NOT NULL,
	name VARCHAR(30) NOT NULL,
	PRIMARY KEY (id)
)
WITH %CLASSPARAMETER ALLOWIDENTITYINSERT = 1;

INSERT INTO users (id, name) VALUES (2, 'fred');
SELECT LAST_IDENTITY();

INSERT INTO users (name) VALUES ('ed');
SELECT LAST_IDENTITY();

SQL
SQL

Not sure if actually a good way to solve the issue

0 0
Benjamin De Boe · Nov 7, 2022

In the case of JDBC, there's also the getGeneratedKeys() method that you may find useful.

We're looking into support for a RETURNING clause for the INSERT command as a possible enhancement (Postgres' flavour of this nonstandard clause is very nice), but don't hold your breath as we have a long list of such candidate enhancements and a much shorter list of people who can work on the surprising amount of places in our codebase that would need to be updated to accommodate such a syntax extension.

0 0