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')
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.