Fonction PostgreSQL pour le dernier ID inséré

Question

Dans PostgreSQL, comment obtenir le dernier identifiant inséré dans une table ?

En MS SQL, il existe la fonction SCOPE_IDENTITY().

S'il vous plaît, ne me conseillez pas d'utiliser quelque chose comme ça :

select max(id) from table

Answer 1

( tl;dr : passer à l'option 3 : INSERER avec RETOUR)

Rappelez-vous que dans postgresql, il n'y a pas de concept d'"id" pour les tables, simplement séquences (qui sont généralement, mais pas nécessairement, utilisées comme valeurs par défaut pour les clés primaires de substitution, avec l'attribut SERIAL pseudo-type).

Si vous souhaitez obtenir l'identifiant d'une ligne nouvellement insérée, il existe plusieurs moyens :

---

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

Par exemple :

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

Le nom de la séquence doit être connu, c'est vraiment arbitraire ; dans cet exemple, nous supposons que la table persons a un id créée avec l'option SERIAL pseudo-type. Pour éviter de dépendre de cela et pour être plus propre, vous pouvez utiliser à la place pg_get_serial_sequence :

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

Attention : currval() ne fonctionne qu'après un INSERT (qui a exécuté nextval() ), dans la même session .

---

Option 2 : LASTVAL();

Cette méthode est similaire à la précédente, sauf que vous n'avez pas besoin de spécifier le nom de la séquence : elle recherche la séquence modifiée la plus récente (toujours dans votre session, même mise en garde que ci-dessus).

---

Les deux sites CURRVAL y LASTVAL sont totalement sûrs pour les concurrents. Le comportement de la séquence dans PG est conçu de manière à ce que les différentes sessions n'interfèrent pas, il n'y a donc aucun risque de conditions de course (si une autre session insère une autre ligne entre mon INSERT et mon SELECT, j'obtiens toujours la valeur correcte).

Cependant ils ont un problème potentiel subtil. Si la base de données a TRIGGER (ou RÈGLE) qui, lors de son insertion dans persons fait quelques insertions supplémentaires dans d'autres tables... puis LASTVAL nous donnera probablement une valeur erronée. Le problème peut même se produire avec CURRVAL si les insertions supplémentaires sont faites dans le même persons (ce qui est beaucoup moins fréquent, mais le risque existe toujours).

---

Option 3 : INSERT con RETURNING

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

C'est la façon la plus propre, efficace et sûre d'obtenir l'identification. Elle ne présente aucun des risques des méthodes précédentes.

Des inconvénients ? Presque aucun : vous devrez peut-être modifier la façon dont vous appelez votre instruction INSERT (dans le pire des cas, peut-être que votre API ou votre couche de base de données ne s'attend pas à ce qu'un INSERT renvoie une valeur) ; ce n'est pas du SQL standard (qui s'en soucie) ; il est disponible depuis Postgresql 8.2 (décembre 2006...).

---

Conclusion : Si vous le pouvez, optez pour l'option 3. Ailleurs, préférez la 1.

Note : toutes ces méthodes sont inutiles si vous avez l'intention d'obtenir le dernier identifiant inséré globalement (pas nécessairement par votre session). Pour cela, vous devez recourir à SELECT max(id) FROM table (bien sûr, cela ne lira pas les insertions non engagées des autres transactions).

A l'inverse, vous devez jamais utiliser SELECT max(id) FROM table à la place de l'une des 3 options ci-dessus, pour obtenir l'identifiant qui vient d'être généré par votre INSERT car (en dehors de la performance) ce n'est pas une sécurité concurrente : entre vos INSERT et votre SELECT une autre session pourrait avoir inséré un autre enregistrement.

Answer 2

Voir la clause RETURNING de la méthode INSERT déclaration. En fait, l'INSERT fait office de requête et vous rend la valeur qui a été insérée.

Answer 3

Vous pouvez utiliser la clause RETURNING dans la déclaration INSERT, comme suit

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

Answer 4

Voir l'exemple ci-dessous

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

Ensuite, pour obtenir le dernier id inséré, utilisez ceci pour la table "user" seq nom de colonne "id".

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

Answer 5

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

Vous devez bien sûr fournir le nom de la table et le nom de la colonne.

Ce sera pour la session / connexion en cours http://www.postgresql.org/docs/8.3/static/functions-sequence.html