WEBVTT 1 00:00:00.290 --> 00:00:01.640 Hi guys and welcome back. 2 00:00:01.640 --> 00:00:02.890 In this video we're looking at 3 00:00:02.890 --> 00:00:05.600 autoincrementing columns in Postgres 4 00:00:05.600 --> 00:00:08.693 and the types sequence and serial. 5 00:00:09.660 --> 00:00:11.600 What is a sequence? 6 00:00:11.600 --> 00:00:14.140 In Postgres, a sequence is a number generator. 7 00:00:14.140 --> 00:00:17.070 It's really a table with one column and one row, 8 00:00:17.070 --> 00:00:18.880 so just one cell of data, 9 00:00:18.880 --> 00:00:22.620 and the value in that cell starts off as the number one 10 00:00:22.620 --> 00:00:25.630 and Postgres has a bunch of operations to increase the value 11 00:00:25.630 --> 00:00:27.950 or to get the current value, and so on. 12 00:00:27.950 --> 00:00:29.830 So for example, we've got these here, 13 00:00:29.830 --> 00:00:31.500 CREATE SEQUENCE my_sequence. 14 00:00:31.500 --> 00:00:34.350 That creates a new table with that value. 15 00:00:34.350 --> 00:00:37.310 SELECT currval of my_sequence would give you 16 00:00:37.310 --> 00:00:39.670 the current value in that table. 17 00:00:39.670 --> 00:00:41.220 SELECT nextval of my_sequence 18 00:00:41.220 --> 00:00:45.060 would increase the value by one and give you the next value. 19 00:00:45.060 --> 00:00:47.820 And so these operations here are used to operate 20 00:00:47.820 --> 00:00:51.260 on sequences instead of changing the value yourself. 21 00:00:51.260 --> 00:00:54.930 That way you always know that the value you're getting 22 00:00:54.930 --> 00:00:57.900 is unique because it always increases by one 23 00:00:57.900 --> 00:00:59.470 and it's always in order. 24 00:00:59.470 --> 00:01:00.650 You know, one by one. 25 00:01:00.650 --> 00:01:02.030 And so, when you're creating a sequence 26 00:01:02.030 --> 00:01:04.590 you want to always use these functions 27 00:01:04.590 --> 00:01:06.520 instead of doing it yourself. 28 00:01:06.520 --> 00:01:08.139 So what is serial? 29 00:01:08.139 --> 00:01:09.600 Well, it's a shortcut. 30 00:01:09.600 --> 00:01:11.890 You can use it as a data type when creating a table 31 00:01:11.890 --> 00:01:14.190 instead of the integer data type 32 00:01:14.190 --> 00:01:16.400 for your primary key, for example. 33 00:01:16.400 --> 00:01:19.430 And it'll automatically create the sequence table for you 34 00:01:19.430 --> 00:01:20.870 and increase it's value every time 35 00:01:20.870 --> 00:01:23.850 a new row is added on this table. 36 00:01:23.850 --> 00:01:25.550 That new value will be used by default 37 00:01:25.550 --> 00:01:27.200 when adding new rows. 38 00:01:27.200 --> 00:01:29.650 So just as we did in SQLite, you know, 39 00:01:29.650 --> 00:01:32.490 user ID integer primary key, for example, 40 00:01:32.490 --> 00:01:36.360 we're now gonna do user ID serial primary key 41 00:01:36.360 --> 00:01:39.000 and it's gonna work in basically the same way. 42 00:01:39.000 --> 00:01:41.100 Some difference though is that the sequence 43 00:01:41.100 --> 00:01:42.580 always increases one by one. 44 00:01:42.580 --> 00:01:44.260 And remember, that in SQLite 45 00:01:44.260 --> 00:01:47.150 row IDs could use repeated values, 46 00:01:47.150 --> 00:01:49.920 and that won't happen in Postgres. 47 00:01:49.920 --> 00:01:51.880 All right, thank you guys for joining me for this video. 48 00:01:51.880 --> 00:01:52.810 Thanks for watching 49 00:01:52.810 --> 00:01:54.513 and I'll see you in the next one.