WEBVTT 00:00.900 --> 00:08.010 Sometimes it's helpful to create a column that holds integers that auto increment each time you add 00:08.010 --> 00:09.510 a row onto the table. 00:09.540 --> 00:15.060 For example, you might use an auto incrementing column to create a unique ID number, also known as 00:15.060 --> 00:17.940 a primary key for each row in the table. 00:17.940 --> 00:25.800 And each row then has its own ID that other tables in the database can reference and a concept I will 00:25.800 --> 00:29.550 cover in next lectures and with PostgreSQL. 00:29.580 --> 00:33.540 You have two ways to auto increment an integer column. 00:33.540 --> 00:41.130 One is the serial data type and the PostgreSQL specific implementation of the ANSI SQL standard for 00:41.130 --> 00:48.840 auto numbered identity columns, and the other is ANSI SQL SQL Standard Identity Keyword. 00:48.870 --> 00:50.980 So let's start with a serial. 00:51.000 --> 00:55.260 So now you will learn how to incrementing with Serial here. 00:55.260 --> 00:58.810 So in previous lecture you made a teacher's table. 00:58.830 --> 01:01.450 Let me actually show that to you. 01:01.480 --> 01:03.320 We will edit data our rows. 01:03.340 --> 01:05.590 So you made a teacher's table. 01:05.590 --> 01:09.010 You created an ID column with the. 01:10.360 --> 01:11.640 Uh, I think along with. 01:11.650 --> 01:12.640 Oops, sorry. 01:13.610 --> 01:13.840 Here. 01:14.060 --> 01:16.640 So you created the ID column? 01:18.250 --> 01:21.000 So with the declaration of Big Serial. 01:21.100 --> 01:22.390 Big Integer, Big Serial. 01:22.870 --> 01:29.680 So this is the its sibling, small serial and serial are not much true datatypes as special implementation 01:29.680 --> 01:33.700 of the corresponding small integer integer and big integer types. 01:33.700 --> 01:42.310 So when you add a column with a serial type PostgreSQL will auto increment the value each time you insert 01:42.340 --> 01:48.040 a row, starting with one, of course, so up to the maximum of each integer. 01:48.040 --> 01:55.230 So here, instead of small serial, we will we will have the same here, same types. 01:55.720 --> 01:58.300 And instead of integer. 01:58.300 --> 02:01.780 But we will change its name to serial data type. 02:03.280 --> 02:03.940 Serial data types. 02:03.940 --> 02:10.090 And we will have instead of small integer integer and big integer, we will have small serial, small 02:10.090 --> 02:14.770 serial serial and big serial. 02:15.790 --> 02:17.350 Big cereal. 02:18.740 --> 02:28.040 So here we have small cereal, will have small cereal name and it will have. 02:33.070 --> 02:38.200 It will have something like from one. 02:38.200 --> 02:39.220 From one. 02:42.590 --> 02:47.300 To 2 to 2767. 02:49.910 --> 02:51.620 And for cereal. 02:51.620 --> 02:55.670 We will have cereal we will have from. 02:58.390 --> 03:01.960 One to this number. 03:01.960 --> 03:03.670 And for big cereal. 03:03.700 --> 03:06.670 Big cereal, we will have. 03:08.260 --> 03:12.670 From this number from one to this number here. 03:12.670 --> 03:21.760 So to use a serial type on a column, declare in the create table statement, as you would an integer 03:21.760 --> 03:22.270 type. 03:23.000 --> 03:24.590 So here. 03:26.430 --> 03:31.370 Uh, for example, you could create a table called let's actually use the query to. 03:33.650 --> 03:36.500 Uh, let's actually create a new table and query tool. 03:36.530 --> 03:41.600 So here we will create some new table. 03:42.650 --> 03:44.300 And we will name it. 03:44.750 --> 03:46.070 Let's name it the. 03:48.300 --> 03:49.790 Oxley students write. 03:50.460 --> 03:51.570 So here. 03:51.600 --> 03:53.220 Create table. 03:53.760 --> 03:57.360 Create table Oxley students. 03:57.360 --> 04:03.510 And here we will add ID, serial or student number. 04:03.510 --> 04:05.520 Student number. 04:05.760 --> 04:07.860 It's going to be serial. 04:07.860 --> 04:13.620 And we will also have student name or student email. 04:13.680 --> 04:15.360 Student email. 04:15.870 --> 04:18.270 It's going to be text. 04:20.010 --> 04:20.550 Here. 04:20.550 --> 04:22.800 And we will also have the. 04:24.670 --> 04:30.730 Or instead of text, we can also use the student email as a character and make it 100. 04:32.640 --> 04:34.290 Yeah, that's it for now. 04:35.080 --> 04:36.840 And here, let's execute this. 04:36.850 --> 04:40.210 And as you can see, it returns successfully in 75 seconds. 04:40.210 --> 04:48.250 If you if your table is not appeared here, you can use the here, right click on the tables, click 04:48.250 --> 04:48.790 on refresh. 04:48.790 --> 04:55.990 And here, as you can see here, we have the students and we have columns here, student number and 04:55.990 --> 04:57.400 student email. 04:58.400 --> 05:09.020 So every time a new row with a person name is added to the table, ID column will increment by one. 05:09.840 --> 05:20.250 So we can also do, for example, as a version of ten Postgres PostgreSQL includes support for identity. 05:20.880 --> 05:27.870 This is the standard SQL implementation for auto incrementing integers, and the identity syntax is 05:27.870 --> 05:35.940 more verbose here, but some databases use users prefer it for its cross compatibility with other database 05:35.940 --> 05:41.880 systems such as Oracle, and also because it has an option to prevent users from accidentally inserting 05:41.880 --> 05:47.700 values into auto incrementing column which cereal types will permit. 05:49.030 --> 05:52.000 So you can specify identity in two ways. 05:52.150 --> 05:58.510 The first is generated always as identity generated like this. 05:58.810 --> 06:00.490 Generated. 06:00.520 --> 06:01.600 Always. 06:01.690 --> 06:04.630 Always as identity. 06:04.720 --> 06:07.450 This identity. 06:09.310 --> 06:09.610 See. 06:10.210 --> 06:17.200 So this tells the database to always fill the colon with an auto incremented value and the user cannot 06:17.200 --> 06:22.090 insert a value into the ID column without manually overriding that setting. 06:22.090 --> 06:31.150 So you see the overriding system values section of the PostgreSQL insert documentation at the SQL insert 06:31.150 --> 06:37.030 link here and you can also generate it by default here. 06:37.110 --> 06:45.730 So the general default as identity tells the database to fill the column with an auto incremented value 06:45.730 --> 06:47.800 by default if the user does not supply one. 06:48.010 --> 06:54.520 So this option allows for the possibility of duplicate values which can make use of it problematic for 06:54.520 --> 06:55.570 creating columns. 06:55.570 --> 06:58.660 And I will delve into that more in next lectures. 06:58.660 --> 07:06.030 So for now, we will stick with the first option you will use always to create a table called org. 07:06.040 --> 07:10.790 So the students that has an ID column populated with identity. 07:11.360 --> 07:12.460 So you will do. 07:12.620 --> 07:17.120 Firstly, let's actually delete the Oxford students. 07:17.850 --> 07:19.110 Uh, that table. 07:19.930 --> 07:24.040 And here, as you can see, we can we have truncate here. 07:24.040 --> 07:25.320 Delete drop here. 07:25.330 --> 07:26.560 Let's drop here. 07:27.010 --> 07:27.580 That's it. 07:27.880 --> 07:32.170 So now right click on the tables, click on Query Tool. 07:32.950 --> 07:35.650 We will write new query here. 07:36.810 --> 07:39.930 So now we will do create. 07:40.530 --> 07:45.060 Create table auxiliary students. 07:46.460 --> 07:52.610 And we will use ID integer and generate. 07:53.180 --> 08:04.910 Here we will generate always oops sorry generated always as identity. 08:06.020 --> 08:09.200 After that, we will use the email. 08:09.200 --> 08:09.710 Right? 08:09.710 --> 08:13.850 So an email is going to be the var character. 08:13.880 --> 08:16.370 Var character and the 100 limit. 08:17.120 --> 08:17.690 That's it. 08:20.530 --> 08:22.660 So for the ID data type. 08:23.940 --> 08:29.550 We use integer followed by the keywords generated always as identity. 08:29.580 --> 08:36.840 Now every time we insert a person name value into the table, the database will fill the ID column with 08:36.840 --> 08:39.240 an auto incremented value. 08:39.240 --> 08:47.310 So given its compatibility with ANSI SQL standard, I will use identity for the remainder of this course. 08:47.310 --> 08:54.090 So even though the value is an auto incrementing column increases each time a row is added, some scenarios 08:54.090 --> 08:57.090 will create gaps in the sequence of numbers in the column. 08:57.090 --> 09:01.620 If row is deleted, for example, the value in that row is never replaced. 09:01.620 --> 09:08.670 So if a row insert is aborted, the sequence for the column will still be incremented.