LAST_INSERT_ID

Returns the last value of a column whose value is automatically incremented through AUTO_INCREMENT or IDENTITY column constraints. If multiple sessions concurrently load the same table, the returned value is the last value generated for an AUTO_INCREMENT column by an insert in that session.

Behavior Type

Volatile

Syntax

LAST_INSERT_ID()

Privileges

Restrictions

Examples

  1. Create table customer4:
    => CREATE TABLE customer4(
         ID IDENTITY(2,2), 
         lname VARCHAR(25),
         fname VARCHAR(25), 
         membership_card INTEGER
       );
    => INSERT INTO customer4(lname, fname, membership_card) VALUES ('Gupta', 'Saleem', 475987);
    				

    The IDENTITY column has a seed of 2, which specifies the value for the first row loaded into the table, and an increment of 2, which specifies the value that is added to the IDENTITY value of the previous row.

  2. Query the table you just created:
    => SELECT * FROM customer4;
     ID | lname | fname  | membership_card
    ----+-------+--------+-----------------
      2 | Gupta | Saleem |          475987
    (1 row)
    
  3. Insert additional values:
    => INSERT INTO customer4(lname, fname, membership_card) VALUES ('Lee', 'Chen', 598742);
    
  4. Call LAST_INSERT_ID:
    => SELECT LAST_INSERT_ID();
     LAST_INSERT_ID
    ----------------
                   4
    (1 row)
    
  5. Query the table again:
    => SELECT * FROM customer4;
     ID | lname | fname  | membership_card
    ----+-------+--------+-----------------
      2 | Gupta | Saleem |          475987
      4 | Lee   | Chen   |          598742
    (2 rows)
    
  6. Add another row:
    => INSERT INTO customer4(lname, fname, membership_card) VALUES ('Davis', 'Bill', 469543);
    
  7. Call LAST_INSERT_ID:
    => SELECT LAST_INSERT_ID();
     LAST_INSERT_ID
    ----------------
                  6
    (1 row)
    
  8. Query the table again:
    => SELECT * FROM customer4;
     ID | lname | fname  | membership_card
    ----+-------+--------+-----------------
      2 | Gupta | Saleem |          475987
      4 | Lee   | Chen   |          598742
      6 | Davis | Bill   |          469543
    (3 rows)