Sometimes it's helpful to create a column that holds integers that auto increment each time you add
a row onto the table.
For example, you might use an auto incrementing column to create a unique ID number, also known as
a primary key for each row in the table.
And each row then has its own ID that other tables in the database can reference and a concept I will
cover in next lectures and with PostgreSQL.
You have two ways to auto increment an integer column.
One is the serial data type and the PostgreSQL specific implementation of the ANSI SQL standard for
auto numbered identity columns, and the other is ANSI SQL SQL Standard Identity Keyword.
So let's start with a serial.
So now you will learn how to incrementing with Serial here.
So in previous lecture you made a teacher's table.
Let me actually show that to you.
We will edit data our rows.
So you made a teacher's table.
You created an ID column with the.
Uh, I think along with.
Oops, sorry.
Here.
So you created the ID column?
So with the declaration of Big Serial.
Big Integer, Big Serial.
So this is the its sibling, small serial and serial are not much true datatypes as special implementation
of the corresponding small integer integer and big integer types.
So when you add a column with a serial type PostgreSQL will auto increment the value each time you insert
a row, starting with one, of course, so up to the maximum of each integer.
So here, instead of small serial, we will we will have the same here, same types.
And instead of integer.
But we will change its name to serial data type.
Serial data types.
And we will have instead of small integer integer and big integer, we will have small serial, small
serial serial and big serial.
Big cereal.
So here we have small cereal, will have small cereal name and it will have.
It will have something like from one.
From one.
To 2 to 2767.
And for cereal.
We will have cereal we will have from.
One to this number.
And for big cereal.
Big cereal, we will have.
From this number from one to this number here.
So to use a serial type on a column, declare in the create table statement, as you would an integer
type.
So here.
Uh, for example, you could create a table called let's actually use the query to.
Uh, let's actually create a new table and query tool.
So here we will create some new table.
And we will name it.
Let's name it the.
Oxley students write.
So here.
Create table.
Create table Oxley students.
And here we will add ID, serial or student number.
Student number.
It's going to be serial.
And we will also have student name or student email.
Student email.
It's going to be text.
Here.
And we will also have the.
Or instead of text, we can also use the student email as a character and make it 100.
Yeah, that's it for now.
And here, let's execute this.
And as you can see, it returns successfully in 75 seconds.
If you if your table is not appeared here, you can use the here, right click on the tables, click
on refresh.
And here, as you can see here, we have the students and we have columns here, student number and
student email.
So every time a new row with a person name is added to the table, ID column will increment by one.
So we can also do, for example, as a version of ten Postgres PostgreSQL includes support for identity.
This is the standard SQL implementation for auto incrementing integers, and the identity syntax is
more verbose here, but some databases use users prefer it for its cross compatibility with other database
systems such as Oracle, and also because it has an option to prevent users from accidentally inserting
values into auto incrementing column which cereal types will permit.
So you can specify identity in two ways.
The first is generated always as identity generated like this.
Generated.
Always.
Always as identity.
This identity.
See.
So this tells the database to always fill the colon with an auto incremented value and the user cannot
insert a value into the ID column without manually overriding that setting.
So you see the overriding system values section of the PostgreSQL insert documentation at the SQL insert
link here and you can also generate it by default here.
So the general default as identity tells the database to fill the column with an auto incremented value
by default if the user does not supply one.
So this option allows for the possibility of duplicate values which can make use of it problematic for
creating columns.
And I will delve into that more in next lectures.
So for now, we will stick with the first option you will use always to create a table called org.
So the students that has an ID column populated with identity.
So you will do.
Firstly, let's actually delete the Oxford students.
Uh, that table.
And here, as you can see, we can we have truncate here.
Delete drop here.
Let's drop here.
That's it.
So now right click on the tables, click on Query Tool.
We will write new query here.
So now we will do create.
Create table auxiliary students.
And we will use ID integer and generate.
Here we will generate always oops sorry generated always as identity.
After that, we will use the email.
Right?
So an email is going to be the var character.
Var character and the 100 limit.
That's it.
So for the ID data type.
We use integer followed by the keywords generated always as identity.
Now every time we insert a person name value into the table, the database will fill the ID column with
an auto incremented value.
So given its compatibility with ANSI SQL standard, I will use identity for the remainder of this course.
So even though the value is an auto incrementing column increases each time a row is added, some scenarios
will create gaps in the sequence of numbers in the column.
If row is deleted, for example, the value in that row is never replaced.
So if a row insert is aborted, the sequence for the column will still be incremented.