1 00:00:00,900 --> 00:00:08,010 Sometimes it's helpful to create a column that holds integers that auto increment each time you add 2 00:00:08,010 --> 00:00:09,510 a row onto the table. 3 00:00:09,540 --> 00:00:15,060 For example, you might use an auto incrementing column to create a unique ID number, also known as 4 00:00:15,060 --> 00:00:17,940 a primary key for each row in the table. 5 00:00:17,940 --> 00:00:25,800 And each row then has its own ID that other tables in the database can reference and a concept I will 6 00:00:25,800 --> 00:00:29,550 cover in next lectures and with PostgreSQL. 7 00:00:29,580 --> 00:00:33,540 You have two ways to auto increment an integer column. 8 00:00:33,540 --> 00:00:41,130 One is the serial data type and the PostgreSQL specific implementation of the ANSI SQL standard for 9 00:00:41,130 --> 00:00:48,840 auto numbered identity columns, and the other is ANSI SQL SQL Standard Identity Keyword. 10 00:00:48,870 --> 00:00:50,980 So let's start with a serial. 11 00:00:51,000 --> 00:00:55,260 So now you will learn how to incrementing with Serial here. 12 00:00:55,260 --> 00:00:58,810 So in previous lecture you made a teacher's table. 13 00:00:58,830 --> 00:01:01,450 Let me actually show that to you. 14 00:01:01,480 --> 00:01:03,320 We will edit data our rows. 15 00:01:03,340 --> 00:01:05,590 So you made a teacher's table. 16 00:01:05,590 --> 00:01:09,010 You created an ID column with the. 17 00:01:10,360 --> 00:01:11,640 Uh, I think along with. 18 00:01:11,650 --> 00:01:12,640 Oops, sorry. 19 00:01:13,610 --> 00:01:13,840 Here. 20 00:01:14,060 --> 00:01:16,640 So you created the ID column? 21 00:01:18,250 --> 00:01:21,000 So with the declaration of Big Serial. 22 00:01:21,100 --> 00:01:22,390 Big Integer, Big Serial. 23 00:01:22,870 --> 00:01:29,680 So this is the its sibling, small serial and serial are not much true datatypes as special implementation 24 00:01:29,680 --> 00:01:33,700 of the corresponding small integer integer and big integer types. 25 00:01:33,700 --> 00:01:42,310 So when you add a column with a serial type PostgreSQL will auto increment the value each time you insert 26 00:01:42,340 --> 00:01:48,040 a row, starting with one, of course, so up to the maximum of each integer. 27 00:01:48,040 --> 00:01:55,230 So here, instead of small serial, we will we will have the same here, same types. 28 00:01:55,720 --> 00:01:58,300 And instead of integer. 29 00:01:58,300 --> 00:02:01,780 But we will change its name to serial data type. 30 00:02:03,280 --> 00:02:03,940 Serial data types. 31 00:02:03,940 --> 00:02:10,090 And we will have instead of small integer integer and big integer, we will have small serial, small 32 00:02:10,090 --> 00:02:14,770 serial serial and big serial. 33 00:02:15,790 --> 00:02:17,350 Big cereal. 34 00:02:18,740 --> 00:02:28,040 So here we have small cereal, will have small cereal name and it will have. 35 00:02:33,070 --> 00:02:38,200 It will have something like from one. 36 00:02:38,200 --> 00:02:39,220 From one. 37 00:02:42,590 --> 00:02:47,300 To 2 to 2767. 38 00:02:49,910 --> 00:02:51,620 And for cereal. 39 00:02:51,620 --> 00:02:55,670 We will have cereal we will have from. 40 00:02:58,390 --> 00:03:01,960 One to this number. 41 00:03:01,960 --> 00:03:03,670 And for big cereal. 42 00:03:03,700 --> 00:03:06,670 Big cereal, we will have. 43 00:03:08,260 --> 00:03:12,670 From this number from one to this number here. 44 00:03:12,670 --> 00:03:21,760 So to use a serial type on a column, declare in the create table statement, as you would an integer 45 00:03:21,760 --> 00:03:22,270 type. 46 00:03:23,000 --> 00:03:24,590 So here. 47 00:03:26,430 --> 00:03:31,370 Uh, for example, you could create a table called let's actually use the query to. 48 00:03:33,650 --> 00:03:36,500 Uh, let's actually create a new table and query tool. 49 00:03:36,530 --> 00:03:41,600 So here we will create some new table. 50 00:03:42,650 --> 00:03:44,300 And we will name it. 51 00:03:44,750 --> 00:03:46,070 Let's name it the. 52 00:03:48,300 --> 00:03:49,790 Oxley students write. 53 00:03:50,460 --> 00:03:51,570 So here. 54 00:03:51,600 --> 00:03:53,220 Create table. 55 00:03:53,760 --> 00:03:57,360 Create table Oxley students. 56 00:03:57,360 --> 00:04:03,510 And here we will add ID, serial or student number. 57 00:04:03,510 --> 00:04:05,520 Student number. 58 00:04:05,760 --> 00:04:07,860 It's going to be serial. 59 00:04:07,860 --> 00:04:13,620 And we will also have student name or student email. 60 00:04:13,680 --> 00:04:15,360 Student email. 61 00:04:15,870 --> 00:04:18,270 It's going to be text. 62 00:04:20,010 --> 00:04:20,550 Here. 63 00:04:20,550 --> 00:04:22,800 And we will also have the. 64 00:04:24,670 --> 00:04:30,730 Or instead of text, we can also use the student email as a character and make it 100. 65 00:04:32,640 --> 00:04:34,290 Yeah, that's it for now. 66 00:04:35,080 --> 00:04:36,840 And here, let's execute this. 67 00:04:36,850 --> 00:04:40,210 And as you can see, it returns successfully in 75 seconds. 68 00:04:40,210 --> 00:04:48,250 If you if your table is not appeared here, you can use the here, right click on the tables, click 69 00:04:48,250 --> 00:04:48,790 on refresh. 70 00:04:48,790 --> 00:04:55,990 And here, as you can see here, we have the students and we have columns here, student number and 71 00:04:55,990 --> 00:04:57,400 student email. 72 00:04:58,400 --> 00:05:09,020 So every time a new row with a person name is added to the table, ID column will increment by one. 73 00:05:09,840 --> 00:05:20,250 So we can also do, for example, as a version of ten Postgres PostgreSQL includes support for identity. 74 00:05:20,880 --> 00:05:27,870 This is the standard SQL implementation for auto incrementing integers, and the identity syntax is 75 00:05:27,870 --> 00:05:35,940 more verbose here, but some databases use users prefer it for its cross compatibility with other database 76 00:05:35,940 --> 00:05:41,880 systems such as Oracle, and also because it has an option to prevent users from accidentally inserting 77 00:05:41,880 --> 00:05:47,700 values into auto incrementing column which cereal types will permit. 78 00:05:49,030 --> 00:05:52,000 So you can specify identity in two ways. 79 00:05:52,150 --> 00:05:58,510 The first is generated always as identity generated like this. 80 00:05:58,810 --> 00:06:00,490 Generated. 81 00:06:00,520 --> 00:06:01,600 Always. 82 00:06:01,690 --> 00:06:04,630 Always as identity. 83 00:06:04,720 --> 00:06:07,450 This identity. 84 00:06:09,310 --> 00:06:09,610 See. 85 00:06:10,210 --> 00:06:17,200 So this tells the database to always fill the colon with an auto incremented value and the user cannot 86 00:06:17,200 --> 00:06:22,090 insert a value into the ID column without manually overriding that setting. 87 00:06:22,090 --> 00:06:31,150 So you see the overriding system values section of the PostgreSQL insert documentation at the SQL insert 88 00:06:31,150 --> 00:06:37,030 link here and you can also generate it by default here. 89 00:06:37,110 --> 00:06:45,730 So the general default as identity tells the database to fill the column with an auto incremented value 90 00:06:45,730 --> 00:06:47,800 by default if the user does not supply one. 91 00:06:48,010 --> 00:06:54,520 So this option allows for the possibility of duplicate values which can make use of it problematic for 92 00:06:54,520 --> 00:06:55,570 creating columns. 93 00:06:55,570 --> 00:06:58,660 And I will delve into that more in next lectures. 94 00:06:58,660 --> 00:07:06,030 So for now, we will stick with the first option you will use always to create a table called org. 95 00:07:06,040 --> 00:07:10,790 So the students that has an ID column populated with identity. 96 00:07:11,360 --> 00:07:12,460 So you will do. 97 00:07:12,620 --> 00:07:17,120 Firstly, let's actually delete the Oxford students. 98 00:07:17,850 --> 00:07:19,110 Uh, that table. 99 00:07:19,930 --> 00:07:24,040 And here, as you can see, we can we have truncate here. 100 00:07:24,040 --> 00:07:25,320 Delete drop here. 101 00:07:25,330 --> 00:07:26,560 Let's drop here. 102 00:07:27,010 --> 00:07:27,580 That's it. 103 00:07:27,880 --> 00:07:32,170 So now right click on the tables, click on Query Tool. 104 00:07:32,950 --> 00:07:35,650 We will write new query here. 105 00:07:36,810 --> 00:07:39,930 So now we will do create. 106 00:07:40,530 --> 00:07:45,060 Create table auxiliary students. 107 00:07:46,460 --> 00:07:52,610 And we will use ID integer and generate. 108 00:07:53,180 --> 00:08:04,910 Here we will generate always oops sorry generated always as identity. 109 00:08:06,020 --> 00:08:09,200 After that, we will use the email. 110 00:08:09,200 --> 00:08:09,710 Right? 111 00:08:09,710 --> 00:08:13,850 So an email is going to be the var character. 112 00:08:13,880 --> 00:08:16,370 Var character and the 100 limit. 113 00:08:17,120 --> 00:08:17,690 That's it. 114 00:08:20,530 --> 00:08:22,660 So for the ID data type. 115 00:08:23,940 --> 00:08:29,550 We use integer followed by the keywords generated always as identity. 116 00:08:29,580 --> 00:08:36,840 Now every time we insert a person name value into the table, the database will fill the ID column with 117 00:08:36,840 --> 00:08:39,240 an auto incremented value. 118 00:08:39,240 --> 00:08:47,310 So given its compatibility with ANSI SQL standard, I will use identity for the remainder of this course. 119 00:08:47,310 --> 00:08:54,090 So even though the value is an auto incrementing column increases each time a row is added, some scenarios 120 00:08:54,090 --> 00:08:57,090 will create gaps in the sequence of numbers in the column. 121 00:08:57,090 --> 00:09:01,620 If row is deleted, for example, the value in that row is never replaced. 122 00:09:01,620 --> 00:09:08,670 So if a row insert is aborted, the sequence for the column will still be incremented.