Is there a way to insert a new record to a table which doesn't have an auto-increment ID without specifically entering the ID. I just want the ID to be lastId+1.
INSERT INTO lists VALUES (id,'KO','SPH', '5') //new id
-
5So you want the behavior to be like a sequence ("auto-increment"), without using a sequence. Why not? These things exist to solve problems which you would otherwise easily get into with concurrent inserts.deceze– deceze ♦2013-09-04 12:52:08 +00:00Commented Sep 4, 2013 at 12:52
-
I just want to insert a new record without changing the DB structure...Jacob– Jacob2013-09-04 12:59:14 +00:00Commented Sep 4, 2013 at 12:59
-
If this is an exceptional case, alright... If this is the normal modus operandi, maybe you should be changing your database structure.deceze– deceze ♦2013-09-04 13:00:08 +00:00Commented Sep 4, 2013 at 13:00
-
The db design is not up to me :)Jacob– Jacob2013-09-04 13:02:32 +00:00Commented Sep 4, 2013 at 13:02
-
2Then you may have to talk to the person who it is up to. :)deceze– deceze ♦2013-09-04 13:03:58 +00:00Commented Sep 4, 2013 at 13:03
|
Show 1 more comment
2 Answers
Don't do that! EVER! Don't even think about doing that!
This WRONG solution may seems (it doesn't) to work for you:
INSERT INTO lists VALUES ((SELECT max(id)+1 FROM lists),'KO','SPH', '5');
BUT, if someone try to insert at the same time as you, you both would get the same id, which will cause an invalid result. You really should use a sequence or some more reliable mechanism (an auxiliary table is common when you can't have holes in the sequence, but it has some drawbacks [it will lock]). You can even use serial data type to make it easier (it creates a sequence underneath):
CREATE TABLE lists(id serial, col2 text, col3 text, ...);
-- If you don't specify "id", it will autogenerate for you:
INSERT INTO lists(col2, col3, ...) VALUES('KO','SPH', ...);
-- You can also specify using DEFAULT (the same as above):
INSERT INTO lists(id, col2, col3, ...) VALUES(DEFAULT, 'KO','SPH', ...);
If you really, really, REALLY, can't create and use a sequence, you can do as the above, but you will have to handle the exception (assuming the id field is PK or UK, and using a read committed transaction), something like that (in PL/pgSQL):
DECLARE
inserted bool = false;
BEGIN
WHILE NOT inserted LOOP;
BEGIN
INSERT INTO lists
VALUES ((SELECT coalesce(max(id),0)+1 FROM lists),'KO','SPH', '5');
inserted = true;
EXCEPTION
WHEN unique_violation THEN
NULL; -- do nothing, just try again
END;
END LOOP;
END;
But again, I highly recommend you to avoid it: use a sequence and be happy... =D
Also, I know it is a example, but use explicit columns list on INSERT INTO clause.
4 Comments
maazza
what happens if there is no data and you do max(id) + 1 ?
MatheusOl
@maazza: then
max would return NULL (and NULL+1 returns NULL), you can simple handle that easily with COALESCE, I'll update the answer to do that
beldaz
To be fair, the warning's rather melodramatic. Providing there's a key constraint on the id attribute, the worst that will happen is that one of the inserts will fail.
arminrosu
It seems to be non-obvious why this is a really bad choice. E.g. if there are unique constraints on any other column, and you try to insert a duplicate, you'll be creating an infinite loop.
I found this solution for myself, without using additional functions. First, I moved the last table ID to the CTE, then made an increment with a window. Visually, this is a slightly more complex solution, but overall quite effective. Example:
;with table_id as (
select max(id) as id
from table
)
insert into table
select table_id.id + row_number() over(),
tab.val1,
val2,
val3
from table_id
cross join(
values
('KO', 'SPH', 5),
('PQ', 'RTO', 10)
) as tab(val1, val2, val3);
