插入Postgres SQL [英] Insert into postgres SQL

问题描述

是否有一种方法可以在没有专门输入ID的情况下将新记录插入不具有自动递增ID的表中.我只希望ID为lastId + 1.

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')//新ID

推荐答案

请勿这样做！ 永远！ 甚至都不要考虑这样做！

此错误解决方案似乎(不)适合您:

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

但是，如果有人尝试与您同时插入，则您都会得到相同的id，这将导致无效的结果.您确实应该使用sequence或更可靠的机制(当序列中没有空洞时，通常使用辅助表，但是它有一些缺点[会锁定]).您甚至可以使用serial数据类型来简化操作(它会在下面创建一个序列):

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

如果您确实不能创建和使用序列，则可以执行上述操作，但是您必须处理异常(假设id字段为PK或UK，并使用读取已提交的事务)，诸如此类(在PL/pgSQL中):

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;

但是，我再次强烈建议您避免这种情况:使用序列并感到高兴... = D

But again, I highly recommend you to avoid it: use a sequence and be happy... =D

我也知道这是一个示例，但是在INSERT INTO子句上使用显式列列表.

Also, I know it is a example, but use explicit columns list on INSERT INTO clause.

这篇关于插入Postgres SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！