Fonction PostgreSQL pour le dernier ID inséré

Dans PostgreSQL, comment puis-je insérer le dernier identifiant dans une table?

Dans MS SQL, il y a SCOPE_IDENTITY ().

S’il vous plaît ne me conseillez pas d’utiliser quelque chose comme ceci:

select max(id) from table 

( tl;dr : option 3: INSERT avec RETURNING)

Rappelons que dans postgresql il n’y a pas de concept “id” pour les tables, juste des 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 le pseudo-type SERIAL ).

Si vous souhaitez obtenir l’identifiant d’une nouvelle ligne, il existe plusieurs manières:


Option 1: CURRVAL(); .

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 une colonne id créée avec le pseudo-type SERIAL . Pour éviter de vous pg_get_serial_sequence sur cela et vous sentir plus propre, vous pouvez utiliser pg_get_serial_sequence place:

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

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


Option 2: LASTVAL();

Ceci est similaire au précédent, mais vous n’avez pas besoin de spécifier le nom de la séquence: il recherche la séquence modifiée la plus récente (toujours dans votre session, même mise en garde que ci-dessus).


CURRVAL et LASTVAL sont tous deux totalement sécurisés. Le comportement de la séquence dans PG est conçu de telle sorte que 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 firebase database contient des TRIGGER (ou RULE) qui, lors de l’insertion dans la table LASTVAL , effectuent des insertions supplémentaires dans d’autres tables … alors LASTVAL nous donnera probablement une valeur incorrecte. Le problème peut même survenir avec CURRVAL , si les insertions supplémentaires sont effectuées dans la même table de persons (c’est beaucoup moins courant, mais le risque existe toujours).


Option 3: INSERT avec RETURNING

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

C’est le moyen le plus propre, le plus efficace et le plus sûr d’obtenir l’identifiant. Il ne présente aucun des risques de la précédente.

Désavantages? Presque aucun: vous devrez peut-être modifier la façon dont vous appelez votre instruction INSERT (dans le pire des cas, votre couche API ou DB ne s’attend pas à ce qu’une INSERT renvoie une valeur); ce n’est pas du SQL standard (on s’en fout); il est disponible depuis Postgresql 8.2 (déc 2006 …)


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

Note: toutes ces méthodes sont inutiles si vous avez l’intention d’obtenir le dernier identifiant global inséré (pas nécessairement dans votre session). Pour cela, vous devez select max(id) from table de select max(id) from table (bien sûr, cela ne lira pas les insertions non validées des autres transactions).

Voir la clause RETURNING de l’instruction INSERT . Fondamentalement, l’INSERT se double d’une requête et vous renvoie la valeur insérée.

vous pouvez utiliser la clause RETURNING dans l’instruction INSERT, comme ci-dessous

 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 

La réponse de Leonbloy est assez complète. Je voudrais seulement append le cas particulier dans lequel il faut obtenir la dernière valeur insérée depuis une fonction PL / pgSQL où OPTION 3 ne correspond pas exactement.

Par exemple, si nous avons les tableaux suivants:

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

Si nous devons insérer un enregistrement de client, nous devons nous référer à un enregistrement de personne. Mais disons que nous voulons concevoir une fonction PL / pgSQL qui insère un nouvel enregistrement dans le client, mais s’occupe également d’insérer le nouvel enregistrement de personne. Pour cela, nous devons utiliser une légère variation de l’OPTION 3 de leonbloy:

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

Notez qu’il existe deux clauses INTO. Par conséquent, la fonction PL / pgSQL serait définie comme suit:

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

Maintenant, nous pouvons insérer les nouvelles données en utilisant:

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

ou

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

Et nous obtenons l’identifiant nouvellement créé.

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

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 identifiant inséré, utilisez ceci pour la table “utilisateur” seq nom de colonne “id”

 SELECT currval(pg_get_serial_sequence('users', 'id')); 
 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

Pour ceux qui ont besoin de tous les enregistrements de données, vous pouvez append

 returning * 

à la fin de votre requête pour obtenir l’object tout en incluant l’identifiant.

Essaye ça:

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

Si ceci retourne 1 (ou quelle que soit la valeur start_value pour votre séquence), alors réinitialisez la séquence à la valeur d’origine, en passant le drapeau false:

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

Autrement,

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

Cela restaurera la valeur de la séquence à l’état d’origine et “setval” retournera avec la valeur de séquence que vous recherchez.

Postgres a un mécanisme intégré pour la même chose, qui dans la même requête renvoie l’identifiant ou tout ce que vous voulez que la requête renvoie. Voici un exemple. Considérez que vous avez une table créée avec 2 colonnes column1 et column2 et que vous voulez que column1 soit renvoyé après chaque insertion.

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

Si vous avez juste besoin de récupérer le dernier identifiant inséré sans rien insérer, vous pouvez utiliser l’exemple suivant

 SELECT id FROM users ORDER BY id DESC LIMIT 1; 

L’espoir peut aider.