时间戳

首页 > 解决方案 > "INSERT INTO t(x) .. SELECT MAX(t.x) + 1 .." sometimes returns "DUPLICATE KEY"

sql - "INSERT INTO t(x) .. SELECT MAX(t.x) + 1 .." sometimes returns "DUPLICATE KEY"

问题描述

Good evening,

I've a problem with a simple "INSERT" sql satement that i was assuming that it's "bomb safe" but sometimes i face the error "duplicate key".

Well, i'll give you a simple sql code

-- instruction 1: table create

CREATE TABLE tab_1 (
    code integer,
    field_1 integer,
    PRIMARY KEY (code)
) WITHOUT OIDS;

-- instruction2: the insert

INSERT INTO tab_1 (code)
SELECT 
    (SELECT COALESCE(MAX(code), 0) + 1 FROM tab_1
    )
;

Well, the problem i'm facing is that "sometimes" without any (IMHO) reason, the instruction 2 returns "duplicate key" on the insert for the field "code". Obviously happens randomly, and I cannot explain "why" it happens or "how" i can reproduce the problem at my desk. The only solution that seems to work is to catch the error and retry the insert until it goes fine.

Ok, so "the solution is served", but the main problem is that this way to use the "insert into" is widely used in a HUGE application with A LOT of tables and rearrange all the code where the same operation is called (declined for different tables) is something i want avoid.

Thanks for the help

标签： sqlpostgresqlsql-insert

解决方案

Use SERIAL instead, which is something like AUTOINCREMENT.

CREATE TABLE table_name(
    id SERIAL
);

Never take COUNT+1 as two users at the same time could get the same ID, or mixed-up ID (if you use that ID further).

For good order: in a programming language like java, after inserting (without the ID) you can use getGeneratedKeys to obtain the generated primary key.

推荐阅读