[postgresql] 마지막으로 삽입 된 ID에 대한 PostgreSQL 함수

( tl;dr: goto 옵션 3 : RETURNING과 함께 삽입)

postgresql에는 테이블에 대한 “id”개념이 없으며 시퀀스 (일반적으로 SERIAL 의사 유형 의 대리 기본 키의 기본값으로 사용되는 것은 아님 ) 만 있습니다.

새로 삽입 된 행의 ID를 얻는 데 관심이있는 경우 몇 가지 방법이 있습니다.

옵션 1 : CURRVAL(<sequence name>);.

예를 들면 다음과 같습니다.

  INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval('persons_id_seq');

시퀀스의 이름을 알고 있어야합니다. 실제로는 임의적입니다. 이 예에서는 테이블 에 의사 유형으로 작성된 열 persons이 있다고 가정합니다 . 이것에 의존하지 않고 더 깨끗하게 느끼려면 대신 다음을 사용할 수 있습니다 .idSERIALpg_get_serial_sequence

  INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval(pg_get_serial_sequence('persons','id'));

주의 사항 : 같은 세션에서 ( 실행 한 ) currval()후에 만 작동합니다 .INSERTnextval()

옵션 2 : LASTVAL();

이것은 이전과 비슷하지만 시퀀스 이름을 지정할 필요가 없습니다. 가장 최근에 수정 된 시퀀스를 찾습니다 (세션 내에서 항상 위와 동일한 경고).

모두 CURRVAL와 LASTVAL완전히 동시 안전합니다. PG의 시퀀스 동작은 다른 세션이 방해하지 않도록 설계되어 경쟁 조건의 위험이 없습니다 (다른 세션이 내 INSERT와 내 SELECT 사이에 다른 행을 삽입하면 여전히 올바른 값을 얻습니다).

그러나 미묘한 잠재적 인 문제가 있습니다. 데이터베이스에 TRIGGER (또는 RULE)가 있으면 persons테이블 에 삽입 할 때 다른 테이블에 추가로 삽입 LASTVAL하는 경우 잘못된 값을 줄 수 있습니다. CURRVAL여분의 삽입이 동일한 persons테이블에서 수행되면 문제는 심지어 발생할 수 있습니다 (이는 훨씬 덜 일반적이지만 위험은 여전히 ​​존재합니다).

옵션 3 : INSERT와RETURNING

INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John') RETURNING id;

이것은 ID를 얻는 가장 깨끗하고 효율적이며 안전한 방법입니다. 이전의 위험은 없습니다.

단점? 거의 없음 : INSERT 문을 호출하는 방식을 수정해야 할 수도 있습니다 (최악의 경우 API 또는 DB 계층에서 INSERT가 값을 반환하지 않을 것으로 예상 함). 표준 SQL이 아닙니다. Postgresql 8.2 (2006 년 12 월 …)부터 사용 가능합니다.

결론 : 가능하면 옵션 3으로 가십시오. 다른 곳에서는 1을 선호하십시오.

참고 : 마지막으로 삽입 된 ID를 전역 적으로 가져 오려는 경우 (세션이 반드시 필요하지는 않음) 이 모든 방법은 쓸모가 없습니다 . 이를 위해서는 의지해야합니다 SELECT max(id) FROM table(물론 다른 거래에서 커밋되지 않은 삽입물을 읽지 않습니다).

반대로, 당신이해야 결코 사용하지 SELECT max(id) FROM table당신의에 의해 생성 된 ID를 얻기 위해, 대신 위의 3 개 가지 옵션 중 하나를 INSERT(분리 성능에서)이 동시 안전이 아니기 때문에, 문을 : 당신의 사이 INSERT와 SELECT다른 세션 다른 레코드를 삽입 할 수도 있습니다.

답변

wgzhao=# create table foo(id int,name text);
CREATE TABLE
wgzhao=# insert into foo values(1,'wgzhao') returning id;
 id
----
  1
(1 row)

INSERT 0 1
wgzhao=# insert into foo values(3,'wgzhao') returning id;
 id
----
  3
(1 row)

INSERT 0 1

wgzhao=# create table bar(id serial,name text);
CREATE TABLE
wgzhao=# insert into bar(name) values('wgzhao') returning id;
 id
----
  1
(1 row)

INSERT 0 1
wgzhao=# insert into bar(name) values('wgzhao') returning id;
 id
----
  2
(1 row)

INSERT 0 

CREATE TABLE person(
   id serial,
   lastname character varying (50),
   firstname character varying (50),
   CONSTRAINT person_pk PRIMARY KEY (id)
);

CREATE TABLE client (
    id integer,
   CONSTRAINT client_pk PRIMARY KEY (id),
   CONSTRAINT fk_client_person FOREIGN KEY (id)
       REFERENCES person (id) MATCH SIMPLE
);

INSERT INTO person(lastname, firstname)
VALUES (lastn, firstn)
RETURNING id INTO [new_variable];

CREATE OR REPLACE FUNCTION new_client(lastn character varying, firstn character varying)
  RETURNS integer AS
$BODY$
DECLARE
   v_id integer;
BEGIN
   -- Inserts the new person record and retrieves the last inserted id
   INSERT INTO person(lastname, firstname)
   VALUES (lastn, firstn)
   RETURNING id INTO v_id;

   -- Inserts the new client and references the inserted person
   INSERT INTO client(id) VALUES (v_id);

   -- Return the new id so we can use it in a select clause or return the new id into the user application
    RETURN v_id;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

SELECT new_client('Smith', 'John');

SELECT * FROM new_client('Smith', 'John');

new_client
integer
----------
         1

returning *

CREATE TABLE users (
    -- make the "id" column a primary key; this also creates
    -- a UNIQUE constraint and a b+-tree index on the column
    id    SERIAL PRIMARY KEY,
    name  TEXT,
    age   INT4
);

INSERT INTO users (name, age) VALUES ('Mozart', 20);

SELECT currval(pg_get_serial_sequence('users', 'id'));

SELECT CURRVAL(pg_get_serial_sequence('my_tbl_name','id_col_name'))