Introduction
Identity columns
Sequences
Instruction
Good! Now let's put everything together. Suppose we have the following table:
CREATE TABLE movie ( id integer PRIMARY KEY, title varchar(64), year int, genre varchar(20) );
Note that the column id is NOT auto-generated here. We've also got our first_seq sequence object. Now, whenever we insert a new row, we can retrieve a number from the sequence:
INSERT INTO movie (id, title, year, genre) VALUES (nextval('first_seq'), 'Kill Me', 1992, 'thriller');
As you can see, instead of providing an explicit value for the column id, we used nextval('first_seq').
Exercise
We've created a table named director with two columns: id (integer) and full_name (varchar). We've also created a sequence named director_seq. Your task is to insert two directors into the table: Adam Smith and Kate Adams. Both of them should take their id column value from the sequence.
Stuck? Here's a hint!
Use a nextval() function.
