Is there a "SELECT LAST_SERIAL()"?
When using "IDENTITY" as my primary key, I can select the last inserted ID with
SELECT LAST_IDENTITY() FROM %TSQL_sys.snf;
Actually this is how Hibernate + Iris' Driver acquires the inserted ID when mapping with
@GeneratedValue(strategy = GenerationType.IDENTITY)
Now, considering that I am using the type "SERIAL" as my primary key instead, how can I get the last inserted ID?
Note that with "SERIAL" I can forcefully insert any value for this ID, from which Iris will continue generating values...
CREATE TABLE test (
test_id serial NOT NULL,
name varchar(100) NULL,
CONSTRAINT pk_test PRIMARY KEY (test_id)
);
INSERT INTO test (test_id, name) values (10, 'test 10'); --inserted "test_id" value: 10
SELECT LAST_IDENTITY() FROM %TSQL_sys.snf; --returned value: 1
INSERT INTO test (name) values ('test 11'); --generated "test_id" value: 11
SELECT LAST_IDENTITY() FROM %TSQL_sys.snf; --returned value: 2
Also, how could I map this kind of ID using Hibernate? I was thinking about implementeing an "IdentifierGenerator", but is there a simpler solution?
#1 there is no "SELECT LAST_SERIAL()"
#2 depending what your expectation on LAST is I see 2 possible workarounds
#2A LAST = max: "
SELECT MAX(test_id) FROM test
"#2B LAST = latest: "
SELECT test_id, MAX(ID) FROM test GROUP BY 2
"#2B is based on the fact that you always have an auto-incremented ID and it's a shortcut of
"
SELECT test_id FROM test where id = (SELECT max(ID) from junior.test)
"or
"
SELECT TOP 1 test_id FROM test WHERE ID < 999999999999999999 ORDER BY ID DESC
"