PostgreSQL-Funktion für die zuletzt eingefügte ID

Question

Wie erhalte ich in PostgreSQL die letzte in eine Tabelle eingefügte ID?

In MS SQL gibt es SCOPE_IDENTITY().

Bitte raten Sie mir nicht, so etwas zu verwenden:

select max(id) from table

Answer 1

( tl;dr Option 3: INSERT mit RETURNING )

Erinnern Sie sich, dass es in Postgresql kein "id"-Konzept für Tabellen gibt, sondern nur Sequenzen (die typischerweise, aber nicht unbedingt, als Standardwerte für Surrogat-Primärschlüssel verwendet werden, wobei die SERIAL Pseudo-Typ).

Wenn Sie an der ID einer neu eingefügten Zeile interessiert sind, gibt es mehrere Möglichkeiten:

---

Option 1: CURRVAL(<sequence name>); .

Zum Beispiel:

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

Der Name der Sequenz muss bekannt sein, er ist wirklich willkürlich; in diesem Beispiel nehmen wir an, dass die Tabelle persons hat eine id Spalte, die mit der SERIAL Pseudo-Typ. Um sich nicht darauf zu verlassen und sich sauberer zu fühlen, können Sie stattdessen verwenden pg_get_serial_sequence :

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

Vorbehalt: currval() funktioniert nur nach einer INSERT (die ausgeführt hat nextval() ), in derselben Sitzung .

---

Option 2: LASTVAL();

Diese Funktion ähnelt der vorhergehenden, nur dass Sie den Namen der Sequenz nicht angeben müssen: Es wird nach der zuletzt geänderten Sequenz gesucht (immer innerhalb Ihrer Sitzung, gleiche Einschränkung wie oben).

---

Beide CURRVAL y LASTVAL sind völlig konkurrenzlos sicher. Das Verhalten der Sequenz in PG ist so konzipiert, dass sich verschiedene Sitzungen nicht gegenseitig behindern, so dass keine Gefahr von Wettlaufsituationen besteht (wenn eine andere Sitzung zwischen meinem INSERT und meinem SELECT eine weitere Zeile einfügt, erhalte ich immer noch meinen korrekten Wert).

Allerdings sie haben ein subtiles potenzielles Problem. Wenn die Datenbank über einige TRIGGER (oder RULE), die bei der Einfügung in persons Tabelle, macht einige zusätzliche Einfügungen in anderen Tabellen... dann LASTVAL wird uns wahrscheinlich den falschen Wert liefern. Das Problem kann auch bei CURRVAL wenn die zusätzlichen Einfügungen in dieselbe persons Tabelle (dies ist weit weniger üblich, aber das Risiko besteht immer noch).

---

Option 3: INSERT con RETURNING

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

Dies ist der sauberste, effizienteste und sicherste Weg, den Ausweis zu erhalten. Sie birgt keine der Risiken der vorherigen Methoden.

Nachteile? Fast keine: Sie müssen möglicherweise die Art und Weise ändern, wie Sie Ihre INSERT-Anweisung aufrufen (im schlimmsten Fall erwartet Ihre API- oder DB-Schicht vielleicht nicht, dass eine INSERT-Anweisung einen Wert zurückgibt); es handelt sich nicht um Standard-SQL (wen interessiert das schon); es ist seit Postgresql 8.2 (Dez. 2006...) verfügbar

---

Schlussfolgerung: Wenn Sie können, wählen Sie Option 3. Andernfalls bevorzugen Sie 1.

Hinweis: Alle diese Methoden sind nutzlos, wenn Sie beabsichtigen, eine die zuletzt eingefügte ID global (nicht unbedingt durch Ihre Sitzung). Hierfür müssen Sie auf Folgendes zurückgreifen SELECT max(id) FROM table (natürlich werden dabei keine unbestätigten Einfügungen aus anderen Transaktionen gelesen).

Umgekehrt sollten Sie niemals verwenden. SELECT max(id) FROM table stattdessen eine der 3 oben genannten Optionen, um die soeben von Ihrem INSERT Anweisung, da dies (abgesehen von der Leistung) nicht gleichzeitig sicher ist: zwischen Ihrer INSERT und Ihr SELECT eine andere Sitzung einen anderen Datensatz eingefügt haben könnte.

Answer 2

Siehe die RETURNING-Klausel der Anweisung INSERT Erklärung. Im Grunde genommen dient die INSERT-Anweisung als Abfrage und gibt Ihnen den eingefügten Wert zurück.

Answer 3

Leonbloy's Antwort ist ziemlich vollständig. Ich würde nur den speziellen Fall hinzufügen, in dem man den letzten eingefügten Wert aus einer PL/pgSQL-Funktion erhalten muss, wo OPTION 3 nicht genau passt.

Zum Beispiel, wenn wir die folgenden Tabellen haben:

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

Wenn wir einen Kundendatensatz einfügen wollen, müssen wir uns auf einen Personendatensatz beziehen. Aber nehmen wir an, wir wollen eine PL/pgSQL-Funktion entwickeln, die einen neuen Datensatz in den Mandanten einfügt, aber auch den neuen Personendatensatz einfügt. Hierfür müssen wir eine leichte Abwandlung von leonbloy's OPTION 3 verwenden:

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

Beachten Sie, dass es zwei INTO-Klauseln gibt. Daher würde die PL/pgSQL-Funktion wie folgt definiert werden:

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;

Jetzt können wir die neuen Daten mit einfügen:

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

o

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

Und wir erhalten die neu erstellte ID.

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

Answer 4

Die anderen Antworten zeigen nicht, wie man den/die Wert(e) verwenden könnte, die von RETURNING . Hier ist ein Beispiel, bei dem der zurückgegebene Wert in eine andere Tabelle eingefügt wird.

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

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

Answer 5

Sie können die RETURNING-Klausel in der INSERT-Anweisung verwenden, wie im Folgenden beschrieben

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