마지막으로 삽입한 ID에 대한 PostgreSQL 함수

마지막으로 삽입한 ID에 대한 PostgreSQL 함수

포스트그레에서SQL, 테이블에 마지막 ID를 삽입하려면 어떻게 해야 합니까?

MS SQL에는 SCOPE_IDENTITY()가 있습니다.

다음과 같은 것을 사용하는 것을 권장하지 마십시오.

select max(id) from table

(tl;dr 3:RETURN )로 합니다. 3: INSERT with RETURNING )로 이동합니다.

postgresql에서는 테이블에 대한 "id" 개념이 없고 시퀀스(SERIAL pseudo-type으로 대체 기본 키의 기본값으로 일반적으로 사용되지만 반드시 사용되는 것은 아님)만 있습니다.

새로 삽입한 행의 ID를 가져오는 데 관심이 있는 경우 다음과 같은 몇 가지 방법이 있습니다.

---

옵션 1: .

예:

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

합니다.입니다. 이의 이름을 합니다. 정말 임의적입니다. 이 예제에서는 테이블이persons을 가지고 있습니다.id여 열된생으로 된 열SERIAL유사 활자의이 기능에 의존하지 않고 보다 청결하게 하려면 다음을 대신 사용할 수 있습니다.

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

주사항:currval()한 후에만 작동합니다.INSERT된 (으)로 표시)nextval()), 같은 세션에 있습니다.

---

옵션 2:

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

---

둘다요.CURRVAL그리고.LASTVAL완전히 동시에 안전합니다.PG에서 시퀀스의 동작은 다른 세션이 간섭하지 않도록 설계되어 레이스 상태의 위험이 없습니다(다른 세션이 내 INSERT와 내 SELECT 사이에 다른 행을 삽입하더라도 여전히 내 정확한 값을 얻습니다).

하지만 그들은 미묘한 잠재적인 문제를 가지고 있습니다.데이터베이스에 다음에 삽입할 때 트리거(또는 규칙)가 있는 경우persons테이블, 다른 테이블에 추가 삽입...그리고나서LASTVAL아마도 잘못된 값을 줄 것입니다문제가 발생할 수도 있습니다.CURRVAL동일한 것에 추가 삽입을 하는 경우.persons표(이것은 훨씬 덜 일반적이지만 위험은 여전히 존재함).

---

옵션 3: 사용RETURNING

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

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

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

---

결론:가능하다면, 3번 옵션을 선택하세요.다른 곳에서는 1을 선호합니다.

참고: 세션에서 마지막으로 삽입한 ID를 전체적으로 가져오려는 경우에는 이러한 모든 방법을 사용할 수 없습니다.이를 위해서는 다음과 같은 방법을 사용해야 합니다.SELECT max(id) FROM table(물론 다른 트랜잭션에서 커밋되지 않은 삽입을 읽지는 않습니다.)

반대로, 당신은 절대 사용해서는 안 됩니다.SELECT max(id) FROM table위의 중 이 옵션을 합니다.INSERT진술, 왜냐하면 (성능과는 별개로) 이것은 동시에 안전하지 않기 때문입니다.INSERT그리고 당신의SELECT다른 세션이 다른 레코드를 삽입했을 수 있습니다.

INSERT 문의 RETURNING 절을 참조하십시오.기본적으로 INSERT는 쿼리 역할을 하며 삽입된 값을 반환합니다.

레온블리의 대답은 완전합니다.OPTION 3이 정확히 맞지 않는 PL/pgSQL 함수 내에서 마지막으로 삽입된 값을 가져와야 하는 특별한 경우만 추가하겠습니다.

예를 들어, 다음 표가 있는 경우:

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

고객 레코드를 삽입해야 하는 경우에는 개인 레코드를 참조해야 합니다.그러나 클라이언트에 새 레코드를 삽입하는 동시에 새 사용자 레코드를 삽입하는 PL/pgSQL 기능을 고안하고 싶다고 가정해 보겠습니다.이를 위해 우리는 Leonbloy의 옵션 3을 약간 변형하여 사용해야 합니다.

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

두 개의 INTO 절이 있습니다.따라서 PL/pgSQL 함수는 다음과 같이 정의됩니다.

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

그리고 새로 생성된 ID를 얻습니다.

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

다음과 같이 INSERT 문에서 RETURNING 절을 사용할 수 있습니다.

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 

다른 답변은 반환된 값을 사용하는 방법을 보여주지 않습니다.RETURNING반환된 값이 다른 테이블에 삽입되는 예를 다음에 제시하겠습니다.

WITH inserted_id AS (
  INSERT INTO tbl1 (col1)
  VALUES ('foo') RETURNING id
)

INSERT INTO tbl2 (other_id) 
VALUES ((select id from inserted_id));

아래 예를 참조

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

그런 다음 마지막으로 삽입된 ID를 가져오려면 테이블 "user" seq 열 이름 "id"에 대해 이 ID를 사용합니다.

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

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

테이블 이름과 열 이름을 입력해야 합니다.

이는 현재 세션/연결 http://www.postgresql.org/docs/8.3/static/functions-sequence.html 에 대한 것입니다.

모든 데이터 레코드를 가져와야 하는 사용자는 다음을 추가할 수 있습니다.

returning *

ID를 포함한 모든 개체를 가져오려면 쿼리 끝으로 이동합니다.

쿼리 삽입 후 RETURNING id를 사용할 수 있습니다.

INSERT INTO distributors (id, name) VALUES (DEFAULT, 'ALI') RETURNING id;

결과:

 id 
----
  1

위의 예에서 id는 자동 증분 파일입니다.

더 나은 방법은 반환할 때 삽입을 사용하는 것입니다.이미 동일한 답변이 있지만, 추가하고 싶은 것은 이것을 변수에 저장하려면 이렇게 할 수 있습니다.

insert into my_table(name) returning id into _my_id;

Postgres에는 동일한 메커니즘이 내장되어 있으며, 동일한 쿼리에서 ID 또는 쿼리가 반환하기를 원하는 모든 것을 반환합니다.여기 예가 있습니다.열 1과 열 2가 있는 테이블이 생성되어 있고 삽입할 때마다 열 1이 반환됩니다.

# create table users_table(id serial not null primary key, name character varying);
CREATE TABLE
#insert into users_table(name) VALUES ('Jon Snow') RETURNING id;
 id 
----
  1
(1 row)

# insert into users_table(name) VALUES ('Arya Stark') RETURNING id;
 id 
----
  2
(1 row)

사용해 보십시오.

select nextval('my_seq_name');  // Returns next value

이 값이 1을 반환하는 경우(또는 시퀀스의 start_value인 경우), 잘못된 플래그를 전달하여 시퀀스를 원래 값으로 재설정합니다.

select setval('my_seq_name', 1, false);

그렇지않으면,

select setval('my_seq_name', nextValue - 1, true);

그러면 시퀀스 값이 원래 상태로 복원되고 "setval"이 찾고 있는 시퀀스 값과 함께 반환됩니다.

저는 자바와 Postgres에서 이 문제를 겪었습니다.저는 Connector-J 버전을 새로 업데이트하여 수정했습니다.

postgresql-9.2-1002.jdbc4.jar

https://jdbc.postgresql.org/download.html : 버전 42.2.12

https://jdbc.postgresql.org/download/postgresql-42.2.12.jar

위의 @ooZman의 답변에 따르면, 이것은 Postgre에 효과가 있는 것 같습니다.테이블 카운터에서 아무것도 조작하지 않고 "sequence"(auto_increment와 유사)의 다음 값을 삽입해야 하는 경우 SQL v12.(참고: 더 복잡한 DB 클러스터 구성에서는 테스트하지 않았습니다...)

Psuedo 코드

$insert_next_id = $return_result->query("select (setval('"your_id_seq"', (select nextval('"your_id_seq"')) - 1, true)) +1");

언급URL : https://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id