In a prior tutorial, we looked at creating tables using the SERIAL data type. The SERIAL data type is an auto-incrementing data type set to a column that is often used for the primary key. In PostgreSQL, this is done through a sequence that by default starts at 0 and increments by 1 each time the nextval function is called for the sequence. Within an INSERT statement, the column does not need to be included in the list, although it certainly could.
Let's explore a contact table:
CREATE TABLE contact( contact_id SERIAL, username VARCHAR(50), password VARCHAR(50) );
As a reminder, behind the scenes, a few steps are done to create the sequence for the contact_id:
CREATE SEQUENCE contact_contact_id_seq;
CREATE TABLE contact( contact_id integer NOT NULL DEFAULT nextval(contact_contact_id_seq), username VARCHAR(50), password VARCHAR(50) );
ALTER SEQUENCE contact_contact_id_seq
OWNED BY contact.contact_id;
Notice that the sequence table has the table name, an underscore, the column name, an underscore, and then seq as the sequence name. Once the table is created, this sequence is automatically created.
Using the method we explored in the prior tutorial, we can insert into this table using the following:
INSERT INTO contact (contact_id,username,password)
VALUES (nextval('contact_contact_id_seq'),'sophia','Password1');
Notice that the value for the contact_id uses the function nextval and passes in the parameter of the sequence.
If we query the table, we should see:
However, it can be quite cumbersome to always write out the entire function to get the next value. Simply by inserting a row into the table without specifying the contact_id, the nextval function is automatically called. For example, if we ran the following insert statement:
INSERT INTO contact (username, password)
VALUES ('Mustang','Password3');
INSERT INTO contact (username,password)
VALUES ('roda','Password99');
You should see that the contact_id automatically increments:
Specific to PostgreSQL, you can also use the keyword DEFAULT for the value if you wanted to pass in the contact_id as well:
INSERT INTO contact (contact_id,username,password)
VALUES (DEFAULT,'Caloric','Password77');
[MUSIC PLAYING] When we're adding data into a table that has a serial which is an [INAUDIBLE] number, we don't have to pass that as part of the values, and will automatically be created. So let's go ahead and create the table as a starting point. The contact table has been created. So what we can go ahead now is try to insert data into the table. So we can just do a standard insert.
Passing in the username, the password. But we're not going to pass in the contact ID. If we go ahead and run that, if we tried to actually query the data. What we'll see is a contact ID is automatically generated for us that simplifies a lot of these different components. So let's go ahead and actually insert another row into our table.
And then what we should see in our query of the data is that it actually inserts a second one and automatically creates a second item. Now, there's other options that we can also do if we want to pass in this directly as part of the serial sequence.
If we wanted to, could also utilize the nextval function, which we've actually identified the specific item in this case here based on the table name, then the ID with underscore between them, and then the sequence. And if we run that, we can go ahead and select it, and we'll see it at the third one has actually been created.
However, it's a lot simpler to just actually insert without including in the contact ID or the serial so that it simplifies the query itself.
[MUSIC PLAYING]
Source: Authored by Vincent Tran
