Insert into postgres SQL

Question

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

Answer 1

Try this:

INSERT INTO lists VALUES
(
    (select coalesce(count(*),0) from lists) + 1,
    'KO','SPH', '5'
)

Alternatively:

INSERT INTO lists VALUES
(
    (select coalesce(max(id),0) from lists) + 1,
    'KO','SPH', '5'
)

If you want not specify ID your field must be autoincrement.

If you use guid as id (sequence) you must specify the field but the value is calculated by DBMS

Answer 2

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.