I use ENUM - Vindicating the Underdog of Data Types
CC Attribution 2.0 Belgium:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Mejias, Boriss
The ENUM data type is extremely good to define constraints to column values. It adds descriptiveness to your database schema. In this talk you'll learn the advantages and disadvantages of the ENUM data type, and how to use it in your database schema design. The enumerated data type, ENUM, is rarely considered in the design of database schemas. This is either because ENUM is unknown, or because one or two drawbacks have created a wrong impression of it, hiding is major advantages. I use ENUM, because I consider it extremely good to define constraints to column values, providing a very descriptive design to match the business logic. This presentation attempts to vindicate the value of ENUM, the underdog of the data types. On this talk you'll learn: - Advantages and disadvantages of the ENUM types - How to use ENUM in your database schema design - Using ordering of ENUM values - Casting ENUM data types - Manipulating ENUM data types
 Okay, hello everybody, this is Boris Mejias. I'm a holistic system software engineer, more formally known as a solution architect working for EDB. I'm also an air guitar player, I like a lot of music, and you can find me on Twitter so you can exchange some ideas about this presentation, about Post-Lexuel and music in general. And the reason I'm presenting this is because I use Enum, the enumerated data types, which is, I think, the most underrated data type in Post-Lexuel. And I'm going to explain you why I like it a lot, and maybe you got convinced and then you are going to use it also in your application. This is basically a presentation meant for developers at the front end level, when they have to design their databases. So I'm giving you more tools in order to build more like better data integrity on the level of your database. So I think it fits well with Post-Lexuel because it's a presentation for developers. So let's say that we have to implement this application that is going to collect feedback from the Post-Lexuel visitors, taking great talks, speakers, different roles, Post-Lexuel and whatever, and they have to provide information between being awful, bad average, or good, greater, awesome. So these are all the possibilities that they have. So you have to put this information on your database. I'm not going to present you about front end because I'm terrible, but about database. Let's see how you register this feedback of the debt rooms. So first direct approach, you create a table with debt room feedback. So each feedback has to have its own ID, that's the debt feedback ID. And then you have a debt room ID, which is a foreign key pointing to a list of debt rooms. So you don't want to have like ghost debt room that gets feedback. So you have a moment in time when the people get clicked on the application to get the feedback and also the feedback itself, which you're going to store in a text value because we just described them, they're all just text. So this fits very well. So that's your feedback. So you insert the value, you say, I want to insert the person 1984 and room 42, which is actually debt room Post-Lexuel. And I'm going to register now and I'm going to say that the debt room is awesome. Yeah, it's quite a possible, quite a realistic test. So that inserts very well. And then the next one says that is awesome. So for some reason, type it wrongly, the application, different application, you have to open this database to many people. And it enters as well, because just a text and text, they all get valid. So you get all this information there. We don't want that. We want to guarantee valid feedback information. So what we are going to do exactly the same table, we're going to add a constraint. A good friend of mine, he said that a constraint is not, it's a bad word. It's actually a guarantee. It's guaranteeing you that this array here that you're saying, so for the feedback, it's going to be any value on this list. Yep. So those are the possible values and you only want to have those. So what is going to happen now is that if you insert, well, awesome, without Z, so wrong, then it's going to run text. It's going to tell you, sorry, your value violates this check constraint, which says that it's have to be these values on the, on the array. So please put it again. So this is going to guarantee valid values. This is very good. So we are going to apply this constraint, not only for that one feedback, but also for this other table that we have here, talk feedback, you see, it's just exactly the same. And we have to create again, exactly the same constraint. So as you can see, this is not going to scale. This is every time you have a four speakers and for all the other people that is all the other items that you want to get feedback is going to be terrible. You don't want to have this. So that's why you have normalization. This is the concept in database that is very important, very known. Sometimes there are some ways to denormalize and I agree. But in this case, you really want to have normalization. So what you do, you create a lookup table. So you have a table where you have all your feedbacks. This is not a language word, but it. The plural feedback and you have your ID and the name of the feedback. And you're going to insert all the possible values on this table. And then you can refer to this table from the feedback, the from feedback or talk feedback or speed speaker feedback. So this is the previous definition of the table. You see that you have their feedback as a text. Now you're going to have a feedback ID, which is going to be an integer referring to this lookup table. And now it's normalized. And it's impossible that you're going to introduce data that is not valid because it has to be first on the feedback table. Yeah. And you can reuse it. So now we have the talk table. So you see the previous one was the debt room. Sorry, the debt room feedback. And now you have the ID and I have the talk feedback and then you have the same ID and then you can reuse this table. So this is why normalization works. So now you insert the values. The only thing that I don't like about the solution is that now you have a number six here for saying that it's awesome if you don't remember which number it was. It is complicated. So you have to probably do and select and nested select there. So it is, it is okay. I mean, it's not that complicated, but you see how this starts making your queries a little bit more complicated. A little bit now that we have larger ones, it's going to be even more complicated. So now you got in the enumerated data type. And this is what I want to talk to you about. So let's see what in them can do for you. Inam is actually a type. Yeah, so we're going to define a type for feedback. So we're going to create a type. Does it create type? It's called feedback as an inam. And then we're going to give all the possible values for this type. Yeah, so we have six types there. And let's see what we can do with this inam. We create a table that from feedback. This is exactly the same one. I'm showing you the previous version with the ID as a foreign key. You can see there. Now, feedback ID. Now we're going to show you the one that has the text. When you start having the text, because this one has to have this constraint, it's too complicated. Now we just put feedback there. So it's our type. It says the same thing that your integer, timestamp, date, whatever. This is a feedback. It's a type. So you have created, you have extended your database with this type. So and you can reuse it then for all the tables. But let's see how it works. You just insert into different feedback exactly as you would do with text. So by the way, this is case sensitive. So it works exactly the same as the text. So it's more intuitive. And it is even better in the sense that you're not saying that this is text. This is a type of feedback. Only certain values are being accepted. So if you try to put some wrong values on it, like awesome there, it gives you an error. Not the same error as with the text, but it says you're even better. This is like invalid input value for inam feedback. It even tells you with an arrow there. Is it like, here's where you got it wrong on line two. Yeah. So this is my line one, line two. And it tells me where I have to fix it. Very good error reporting in postgres. So again, you have your feedback type. And now you have your bedroom feedback table with the type here of the column being feedback. And you can just reuse it now for the talk feedback, you just use feedback feedback. So it is reusable and it guarantees you valid data. So what about performance? One of the things is, oh, okay. So normalization is important for performance and everything. Let's see with concrete cases. So we have a comparison of a table with only text. Let's go to see what is the cost of having a check constraint there. Now let's see with feedback ID in a foreign key and the data type inam. Okay. So for this test, I just generated random entries for 2,267,709. Just a number as any other. Pro the valid of any number, not just 2 million or 3 million. Why not this one? And coincidentally, this is the telephone number of, or was the telephone number of Douglas Adams when he was writing the Hitchhiker's Guide to the Galaxy. There you go. So this one took me on my laptop. So it's not the best laptop. So I need anyone. 36 seconds and something for the first case. So the check constraint actually didn't change the performance. It's also 36 seconds. Look, this first two number. And you have another table involved. And you need to start making links between two tables. It takes more time. So normalization is good, but you pay a little bit of a performant. So inam, I put it there in white just because this is the moment of suspense is like, is it really bad? Well, not actually. It's just as good as text. So I think that's great. Good news. So we have a good type there. And I'm just going to concentrate on this tree, not on the first one, because if the first one doesn't give you any guarantee about the data, you should discard it. You need to work with databases that give you these guarantees and all the properties that you have for your data. Your data is important. So please use valid data. So during this presentation, I'm going to do an approach comparison. Not a performance comparison. It's an approach in general. So not just how fast it runs, but also how easy it is to write the code because as a from a developer point of view, it is important that your code is comprehensible and this is going to reduce the amount of bugs that you have in your code. At least it does for me. So the clearer the code, the less bugs that I have. In this case, I'm going to give a little bit better points to text check and inam feedback just because they run faster, but also because there's shorter code in order to do inserts. Okay, it's clearer. So let's see now, retrieve data. We saw how to import data. Let's retrieve data. This is for the text check. So we are going to count how many different feedback we get from a dev room. So we are going to select the feedback. This is text. So we are going to see the value and how many entries per value from the dev room feedback. And we need to do a dev room join here with the dev room table because you want to see the feedback from the Python dev room. I also tend presentations of the Python dev room. I think it's a very nice community as well. So and I go to group by feedback here. Now if you want to run this with the inam is exactly the same code. So it is very trivial to use it. It matches your way of thinking, at least my way of thinking, and it gives you feedback as a data type. It matches your way of business model, let's say. Now if you do it with a foreign key, it's not that extra complicated, but first of all, you need to change here from the name of the order table, which is going to be interpreted as feedback here. And you need to have an extra join here. So let me put it more clear here. You have a conversion here in terms of naming. And then you have to have this extra join there. Joins are expensive. And this is a small join, but in general, if you start to put it in a different query, you're going to see that joins actually cost you. So it is a little bit more complicated to retrieve data with foreign key, with a lookup table. Yeah. So again, my personal opinion, and I'm sorry, but this is my presentation, so I can give the point and you can evaluate whether I'm being partial or not. So I think the text and enum for retrieving data is much easier than with a foreign key. Yeah, there you go. Now what about date integrity? So this is the query for text check. I'm going to select everything from that room for all the dev rooms where the feedback is being great. And I get in 94 milliseconds, zero rows. Okay. Well, nobody thinks this any dev room is great. Let's see with the foreign key. So again, I need to add the join. So you see again, things start getting a little bit more verbose in terms of code. And again, I get zero rows this time in 100 milliseconds. Now is it that nobody thinks that the dev rooms are great? No, it is not that I made a mistake. This is a bug. I have a typo there. It's not great. It's great. So that's wrong, but I didn't notice. So I thought there were zero values. So I would believe that nobody thought the dev room was great. Just because I made a bug. So let's see what happened if you do exactly the same for Inam. So look at the code, the first line here. This is for text. And it's exactly the same code for Inam. Inam is going to tell me that this is wrong input. So it tells that this value that I'm trying to use here, because this is a type of feedback, not just text, it's going to validate the type when I'm using it in the query. And it's going to tell me an error in less than a millisecond. So the other two queries return zero values after scanning the table or using indexes, whatever. And then they tell me that it's zero rows. It depends on the size of your table. This one depends on the size of your Inam. So this is more or less like constant time. It's always going to be as fast as possible to tell me that this is wrong input. So you may say, I'm a good developer, I will never make the mistake. This is because you're typing there and you made the mistake. It's true. I understand that and I agree with you. But possibly you have to open, because this is false, then you have to open this database to all the applications. And the other developers are not going to be as good as you, and they're going to make the mistakes. Or you have to open this as an API and everything. So think about it. I mean, this is data integrity from the retrieving as well. So the other one guarantees us that we are going to insert valid data. This one is guaranteeing us that we are going to retrieve valid data. I think this is core another point. And it's very fast as well. It crashes immediately, error fast. So in the approach comparison, we said this before, get is good for text. And in Amp, let me add here another point because of data integration. And let me try to make it clear. Data integration, it is very important. So I hope that I convinced you about that. And let's continue with the rest of the presentation. So why normalization is relevant? So until now, we have seen that normalization is good with Inam, but with foreign keys kind of not paying us off or anything. So why do we use it? Ah, here's the point. Update. Let's say that average is a word that is ancient and nobody says average when you say like, how was it there from? And people say, meh. Because that's kind of the new word that people are using. I'm all I still say like, average for whatever. But it doesn't mean, but people want to say meh. So let's update the different feedback. And we're going to set everything that was average. We're going to say that as meh. This is not a valid query because you have a check constraint here. I guarantee a may is not a valid yet. So what you need to do is actually a full transaction that first is going to remove the constraint so that we can introduce the value of meh freely as it didn't exist any constraint at all. And then we need to read the constraint and check that the entire table validates this array here, which is the new value here with meh instead of average. And imagine that you want to do this for all the tables, talk feedback, speaker feedback and everything. So this is going to be very costly. This is bad. This is terrible. So that's why normalization is good. So this is the code for updating the table, the lookup table with feedbacks instead of saying average, we say meh. Is that all the code? Yes, it is. There's nothing else. Yeah. That's why normalization is good. So feedback with text check. Terrible. We put the three X there. We don't like it at all. And this is when this approach kind of went wrong. And the foreign key is good and is fast. And this is perfect. This is the why you need normalization. What about enums? So this is the moment that you say like, maybe this is not so good. This is a possibility of doing update with an enum. You alter the type. Yeah. So this is really DDL. So you alter the type and you say like, rename the value average to meh. Would that be enough? Yes. That's everything that you need to do. So we can also put a check there with in on feedback is as good and as fast and as efficient as with a foreign key. Yeah. There you go. Good. Now let's say that we want to delete data. So awesome. What is that? I mean, you ask somebody, what do you have for lunch? They say, whoa, burger in a lager. Oh, awesome. That's not awesome. Especially if it's a lager. So people say too fast. Awesome. So let's say that we don't want to have awesome anymore. And we go again with the same thing. And we're going to say that, okay, if somebody said the awesome, just put it a great is the best closest value. So we need to get a new array here. You know the story. Very expensive. Not so good. But I mean, there's nothing else that you can do. Is it better with normalization? While you say delete from feedbacks, where name is awesome. This should be enough, but when you run this is going to say like, oh, sorry, first you need to clean up all your tables. So you still need to do all these updates in all the other tables before, in order to be able to remove one value here. So it is a lot of work to remove, remove this thing, even if he is a normalized data. Yeah. So this is going to complain that you still have pointers from all the tables to this lookup table. Now for inam, unfortunately, there is no alter table drop value that doesn't exist. So stripe that. And there's some possibilities of creating a different type. You still go through all the cleanup in all the tables and then you change the column type. I'm not going to go into the details of this one, but I want to show you something similar about alternative data types. So that's it. I mean, deleting this kind of value is not so good for anyone. It's possible. It's possible. Yeah. That's why I put warnings there. So nobody scores well here. So fair approach. So an extra feature. So we saw insert. We saw retrieving data. We saw updating data and delete. Yeah. So let's see this extra feature that inam has. Might not be that important, but I think it's cool. So let's see. Inams are order. So when you create the data type, you give it in an order. So it goes from awful to awesome. Yeah. Then when you want to say, for instance, give me all the data rooms that has a feedback larger than good. So that would be scores that were great and awesome and how many times it happened this. I also want to have the name of the data rooms. So I have this join here. Yeah. So look, if I do it with text, this doesn't work because this is going to give me alphanumeric order. So great and good, well, we great, but awesome wouldn't be there. So it doesn't work. That's makes sense actually. So but you can do it with data types, sorry, with inam types, enumerated types. And it works. So you can try that. Now let's say that, I don't want to use always the same order. Let's say that I put them in a wrong order. You can create another inam type. Let's call it feedback alt, this one, where I put whatever order I want because people say awesome too quickly. So the first three are okay, but I want to put awesome just before average, after average and before good. So the real good one is great. Yeah. So my query is going to change now. So only going to give me the grid. So this is the first query when I say like feedback is larger than good. If I want to use this dynamic order change, I'm going to do the cast here. So you see I have the feedback. I'm going to cast it to text and from text I'm going to cast it to the feedback alternative. I could define it a little bit more to avoid this text intermediate stuff. But with this one is clear. I don't have to add extra code. I'm not hiding any code. This is exactly the code that you need to do for getting an alternative order. So this is strongly type with some dynamic order. I think it's powerful. Yeah. Well, anyway, you can also add values that are older into your inam. Let's say, well, in the beginning, we saw that we changed average to me. So we just say rename value average to me. And then we're going to add new things. Let's say like somebody was not bad, but it was just unlucky. So we insert unlucky before me and we insert presentation was fine after me. And we can do this in the type just like this. This is all the code that you need. And if you want to know what's going on in your type, you can always use this describe type capital T and with a plus and it's going to give you all the values in order. So now you know what's the order of your data types. Now, you can also investigate your numeric type and enumerated types. Sorry, not numeric enumerated by using this views from the catalog Pg underscore inam and Pg underscore type. Remember that this is a type. So the type name is feedback. And then you can say in order by inam sort order. And now you can see a little bit of the implementation of it. So this is the order. You get the label and it has some real numbers there. And that's how you can put things in between. Yeah. So it works. There you have the inam. And you can say like, oh, I can also do this on lookup table. Come on. I just use the order of the of the ID and then I get the same order. I just need to do some joint tables and everything. But then if you want to add some things or you want to use this dynamic order, you cannot well, okay, but let's say this is use a rank. And you can use like alternative ranking and everything. So here I have a rank. But not to use an integer here because you might want to have new values and they need a real number or reorder all these values. It's complicated. So let's say that you have unlucky and fine and because they were before and after may you have to put this 2.5 and 3.5. So now it looks a little bit or odd. They have here an order by numbers, which is different than this one. I'm a little bit picky because I want to promote inam, but let's order it by rank and then you see the integers and from the other side. Well, but this works. Yeah. So you need to add an extra column to your lookup table just because of the order. But it works. So let's get back to better than good. This is the first query that I show you with inam. This is a query with a lookup table and a ranking. So again, you add an extra join to your table and you need to add an INER select just to get the value of the ranking of good in order to do this larger than. So it's a very short example. It can get more complicated. So that's what I want to show you that inam is straightforward. Yeah. Okay. So this extra feature, the approach comparison, we can put it ordering with text check. You cannot have any order because it's going to give you alphabetic, alphanometric order, nothing to do with the real order. With foreign key, you can do it, but you need to add another column and then some other extra stuff with feedback. You get it for free, the inam type. Yeah. So you might use it. Okay. So I'm closing words. So now you know better, inam in Postgres. So you might want to give it a try if you really are convinced and you have already your values that are in text and select away, I have my text, I have my array. How do I move it to inam? Well you just create a type inam and you do a type cast from text to your new type and it's going to work. Now, what I feel is that when you're modeling your database, it matches better your way of thinking. So I create a type called feedback. I can have countries because they don't vary that much. I can have types of beer, for instance. You don't discover a new type of beer so often. It can be a variation, things like that, but you have a core type of beer. So you can use inam for this. It matches the way of your thinking of your applications. When you read the table, say, okay, of course, this is a beer type. Oh, this is feedback. Instead of saying text or integer, what does it mean? And as you saw already, this is very efficiently normalized. A lot of things that are implemented for you and you don't need to go through all these difficult changes in order to modify the values. Everything is for you. Except for delete, of course. I understand this is a draw, but it is not as easy with the others as well. And just remember that this presentation, it is specific to PostgreSQL. Inam is not a SQL standard, so it's not implemented in all the databases. And those databases that are implemented, they don't do it exactly the same way as PostgreSQL. So this is specific to PostgreSQL. This is the PostgreSQL step room and post them. And I hope that you can see the value of this if you're using inam in your using PostgreSQL. So contact me, ask me questions. I will be glad to answer your questions on Twitter as well if you want to know more about inams. Yeah. And I think that's all from me. Thank you very much. And unfortunately, we cannot go for a beer after this presentation, but it's early in the morning. But also we are not in person. But next time we see each other, we can have a chat and a beer. So thank you very much. Bye. I recorded that in the evening, but it's okay. I think you can start in any moment now. I see nothing happening. So, okay, we are online. Gio, would you like to start? Fantastic. Yes, that was a lovely talk. I know so much about Inam and we have gotten quite some questions. I will start with the very first one. When should I not use Enum? Yeah. Thanks, Giorgio. So, what I explained, one of the main issues, one of the main benefits of Inam is to use it for data integrity. And as somebody says, it's kind of a built-in way of having a normalized lookup table. But it works even more efficient because the implementation works pretty well. It is very cheap to do updates of the values. And the reordering is also possible to add things in between. So that's the one more use case, data integrity. And when you don't want to use Inam, it's when your values are going to change a lot. When your values are going to be deleted, when your values are going to be reordered in a different way. But I did show you a little bit of some tricks for doing some casts instead of having alternative ordering for the Inam. So you can have a catalog of ordering. That would be a possibility. And actually, deleting values is not only a problem for Inam. It's actually a problem of having actually a lookup table as well because the value that you want to delete is populated in all the tables. So you still have to clean up that value. But when things are kind of like statics and you want to have data integrity, I think that's the best use case. So if you don't need that, then you suggest use text, something that is more valuable. OK, so the next question is by Alit Sekuharcik. I remember from many years back that changing the Nam caused a huge log. Is it still present? Now, renaming the value, it is what you want to have. And the value is not stored on the tables. It's only the reference to the Inam. So if you look at the size, it is four bytes to the pointer to the Inam. So when you do the update of the values, it's just going to be on the table. So all the locking, probably if you are running queries that are dependent on the value at that time, those are going to be niche. But I think it still gives you a different guarantee that when you're looking with a lookup table. In terms of changing the value, it's the same cost as a lookup table, actually. Yeah. Right. The next question is from Publux. So is it still impossible to change a new order except update the system catalogs? No, so you can add things in between. No, the only way would be to create an alternative apparel Inam with the same values in a different order and then you can do the cost. And then you would have to do the cost of the column if you want to do that. So that's going to be a little bit expensive. Yeah. So that's one of the use cases that you don't want to have Inam if you have to change the order in too much.
 Okay, hello everybody, this is Boris Mejias.  I'm a holistic system software engineer, more formally known as a solution architect working  for EDB.  I'm also an air guitar player, I like a lot of music, and you can find me on Twitter  so you can exchange some ideas about this presentation, about Post-Lexuel and music in general.  And the reason I'm presenting this is because I use Enum, the enumerated data types, which  is, I think, the most underrated data type in Post-Lexuel.  And I'm going to explain you why I like it a lot, and maybe you got convinced and then  you are going to use it also in your application.  This is basically a presentation meant for developers at the front end level, when they  have to design their databases.  So I'm giving you more tools in order to build more like better data integrity on the level  of your database.  So I think it fits well with Post-Lexuel because it's a presentation for developers.  So let's say that we have to implement this application that is going to collect feedback  from the Post-Lexuel visitors, taking great talks, speakers, different roles, Post-Lexuel  and whatever, and they have to provide information between being awful, bad average, or good,  greater, awesome.  So these are all the possibilities that they have.  So you have to put this information on your database.  I'm not going to present you about front end because I'm terrible, but about database.  Let's see how you register this feedback of the debt rooms.  So first direct approach, you create a table with debt room feedback.  So each feedback has to have its own ID, that's the debt feedback ID.  And then you have a debt room ID, which is a foreign key pointing to a list of debt  rooms.  So you don't want to have like ghost debt room that gets feedback.  So you have a moment in time when the people get clicked on the application to get the  feedback and also the feedback itself, which you're going to store in a text value because  we just described them, they're all just text.  So this fits very well.  So that's your feedback.  So you insert the value, you say, I want to insert the person 1984 and room 42, which  is actually debt room Post-Lexuel.  And I'm going to register now and I'm going to say that the debt room is awesome.  Yeah, it's quite a possible, quite a realistic test.  So that inserts very well.  And then the next one says that is awesome.  So for some reason, type it wrongly, the application, different application, you have to open this  database to many people.  And it enters as well, because just a text and text, they all get valid.  So you get all this information there.  We don't want that.  We want to guarantee valid feedback information.  So what we are going to do exactly the same table, we're going to add a constraint.  A good friend of mine, he said that a constraint is not, it's a bad word.  It's actually a guarantee.  It's guaranteeing you that this array here that you're saying, so for the feedback, it's  going to be any value on this list.  Yep.  So those are the possible values and you only want to have those.  So what is going to happen now is that if you insert, well, awesome, without Z, so wrong,  then it's going to run text.  It's going to tell you, sorry, your value violates this check constraint, which says  that it's have to be these values on the, on the array.  So please put it again.  So this is going to guarantee valid values.  This is very good.  So we are going to apply this constraint, not only for that one feedback, but also for  this other table that we have here, talk feedback, you see, it's just exactly the same.  And we have to create again, exactly the same constraint.  So as you can see, this is not going to scale.  This is every time you have a four speakers and for all the other people that is all the  other items that you want to get feedback is going to be terrible.  You don't want to have this.  So that's why you have normalization.  This is the concept in database that is very important, very known.  Sometimes there are some ways to denormalize and I agree.  But in this case, you really want to have normalization.  So what you do, you create a lookup table.  So you have a table where you have all your feedbacks.  This is not a language word, but it.  The plural feedback and you have your ID and the name of the feedback.  And you're going to insert all the possible values on this table.  And then you can refer to this table from the feedback, the from feedback or talk feedback  or speed speaker feedback.  So this is the previous definition of the table.  You see that you have their feedback as a text.  Now you're going to have a feedback ID, which is going to be an integer referring to this  lookup table.  And now it's normalized.  And it's impossible that you're going to introduce data that is not valid because it has to be  first on the feedback table.  Yeah.  And you can reuse it.  So now we have the talk table.  So you see the previous one was the debt room.  Sorry, the debt room feedback.  And now you have the ID and I have the talk feedback and then you have the same ID and  then you can reuse this table.  So this is why normalization works.  So now you insert the values.  The only thing that I don't like about the solution is that now you have a number six  here for saying that it's awesome if you don't remember which number it was.  It is complicated.  So you have to probably do and select and nested select there.  So it is, it is okay.  I mean, it's not that complicated, but you see how this starts making your queries a  little bit more complicated.  A little bit now that we have larger ones, it's going to be even more complicated.  So now you got in the enumerated data type.  And this is what I want to talk to you about.  So let's see what in them can do for you.  Inam is actually a type.  Yeah, so we're going to define a type for feedback.  So we're going to create a type.  Does it create type?  It's called feedback as an inam.  And then we're going to give all the possible values for this type.  Yeah, so we have six types there.  And let's see what we can do with this inam.  We create a table that from feedback.  This is exactly the same one.  I'm showing you the previous version with the ID as a foreign key.  You can see there.  Now, feedback ID.  Now we're going to show you the one that has the text.  When you start having the text, because this one has to have this constraint, it's too  complicated.  Now we just put feedback there.  So it's our type.  It says the same thing that your integer, timestamp, date, whatever.  This is a feedback.  It's a type.  So you have created, you have extended your database with this type.  So and you can reuse it then for all the tables.  But let's see how it works.  You just insert into different feedback exactly as you would do with text.  So by the way, this is case sensitive.  So it works exactly the same as the text.  So it's more intuitive.  And it is even better in the sense that you're not saying that this is text.  This is a type of feedback.  Only certain values are being accepted.  So if you try to put some wrong values on it, like awesome there, it gives you an error.  Not the same error as with the text, but it says you're even better.  This is like invalid input value for inam feedback.  It even tells you with an arrow there.  Is it like, here's where you got it wrong on line two.  Yeah.  So this is my line one, line two.  And it tells me where I have to fix it.  Very good error reporting in postgres.  So again, you have your feedback type.  And now you have your bedroom feedback table with the type here of the column being feedback.  And you can just reuse it now for the talk feedback, you just use feedback feedback.  So it is reusable and it guarantees you valid data.  So what about performance?  One of the things is, oh, okay.  So normalization is important for performance and everything.  Let's see with concrete cases.  So we have a comparison of a table with only text.  Let's go to see what is the cost of having a check constraint there.  Now let's see with feedback ID in a foreign key and the data type inam.  Okay.  So for this test, I just generated random entries for 2,267,709.  Just a number as any other.  Pro the valid of any number, not just 2 million or 3 million.  Why not this one?  And coincidentally, this is the telephone number of, or was the telephone number of  Douglas Adams when he was writing the Hitchhiker's Guide to the Galaxy.  There you go.  So this one took me on my laptop.  So it's not the best laptop.  So I need anyone.  36 seconds and something for the first case.  So the check constraint actually didn't change the performance.  It's also 36 seconds.  Look, this first two number.  And you have another table involved.  And you need to start making links between two tables.  It takes more time.  So normalization is good, but you pay a little bit of a performant.  So inam, I put it there in white just because this is the moment of suspense is like, is  it really bad?  Well, not actually.  It's just as good as text.  So I think that's great.  Good news.  So we have a good type there.  And I'm just going to concentrate on this tree, not on the first one, because if the  first one doesn't give you any guarantee about the data, you should discard it.  You need to work with databases that give you these guarantees and all the properties  that you have for your data.  Your data is important.  So please use valid data.  So during this presentation, I'm going to do an approach comparison.  Not a performance comparison.  It's an approach in general.  So not just how fast it runs, but also how easy it is to write the code because as a  from a developer point of view, it is important that your code is comprehensible and this  is going to reduce the amount of bugs that you have in your code.  At least it does for me.  So the clearer the code, the less bugs that I have.  In this case, I'm going to give a little bit better points to text check and inam feedback  just because they run faster, but also because there's shorter code in order to do inserts.  Okay, it's clearer.  So let's see now, retrieve data.  We saw how to import data.  Let's retrieve data.  This is for the text check.  So we are going to count how many different feedback we get from a dev room.  So we are going to select the feedback.  This is text.  So we are going to see the value and how many entries per value from the dev room feedback.  And we need to do a dev room join here with the dev room table because you want to see  the feedback from the Python dev room.  I also tend presentations of the Python dev room.  I think it's a very nice community as well.  So and I go to group by feedback here.  Now if you want to run this with the inam is exactly the same code.  So it is very trivial to use it.  It matches your way of thinking, at least my way of thinking, and it gives you feedback  as a data type.  It matches your way of business model, let's say.  Now if you do it with a foreign key, it's not that extra complicated, but first of all,  you need to change here from the name of the order table, which is going to be interpreted  as feedback here.  And you need to have an extra join here.  So let me put it more clear here.  You have a conversion here in terms of naming.  And then you have to have this extra join there.  Joins are expensive.  And this is a small join, but in general, if you start to put it in a different query,  you're going to see that joins actually cost you.  So it is a little bit more complicated to retrieve data with foreign key, with a lookup  table.  Yeah.  So again, my personal opinion, and I'm sorry, but this is my presentation, so I can give  the point and you can evaluate whether I'm being partial or not.  So I think the text and enum for retrieving data is much easier than with a foreign key.  Yeah, there you go.  Now what about date integrity?  So this is the query for text check.  I'm going to select everything from that room for all the dev rooms where the feedback  is being great.  And I get in 94 milliseconds, zero rows.  Okay.  Well, nobody thinks this any dev room is great.  Let's see with the foreign key.  So again, I need to add the join.  So you see again, things start getting a little bit more verbose in terms of code.  And again, I get zero rows this time in 100 milliseconds.  Now is it that nobody thinks that the dev rooms are great?  No, it is not that I made a mistake.  This is a bug.  I have a typo there.  It's not great.  It's great.  So that's wrong, but I didn't notice.  So I thought there were zero values.  So I would believe that nobody thought the dev room was great.  Just because I made a bug.  So let's see what happened if you do exactly the same for Inam.  So look at the code, the first line here.  This is for text.  And it's exactly the same code for Inam.  Inam is going to tell me that this is wrong input.  So it tells that this value that I'm trying to use here, because this is a type of feedback,  not just text, it's going to validate the type when I'm using it in the query.  And it's going to tell me an error in less than a millisecond.  So the other two queries return zero values after scanning the table or using indexes,  whatever.  And then they tell me that it's zero rows.  It depends on the size of your table.  This one depends on the size of your Inam.  So this is more or less like constant time.  It's always going to be as fast as possible to tell me that this is wrong input.  So you may say, I'm a good developer, I will never make the mistake.  This is because you're typing there and you made the mistake.  It's true.  I understand that and I agree with you.  But possibly you have to open, because this is false, then you have to open this database  to all the applications.  And the other developers are not going to be as good as you, and they're going to make  the mistakes.  Or you have to open this as an API and everything.  So think about it.  I mean, this is data integrity from the retrieving as well.  So the other one guarantees us that we are going to insert valid data.  This one is guaranteeing us that we are going to retrieve valid data.  I think this is core another point.  And it's very fast as well.  It crashes immediately, error fast.  So in the approach comparison, we said this before, get is good for text.  And in Amp, let me add here another point because of data integration.  And let me try to make it clear.  Data integration, it is very important.  So I hope that I convinced you about that.  And let's continue with the rest of the presentation.  So why normalization is relevant?  So until now, we have seen that normalization is good with Inam, but with foreign keys kind  of not paying us off or anything.  So why do we use it?  Ah, here's the point.  Update.  Let's say that average is a word that is ancient and nobody says average when you say like,  how was it there from?  And people say, meh.  Because that's kind of the new word that people are using.  I'm all I still say like, average for whatever.  But it doesn't mean, but people want to say meh.  So let's update the different feedback.  And we're going to set everything that was average.  We're going to say that as meh.  This is not a valid query because you have a check constraint here.  I guarantee a may is not a valid yet.  So what you need to do is actually a full transaction that first is going to remove  the constraint so that we can introduce the value of meh freely as it didn't exist any  constraint at all.  And then we need to read the constraint and check that the entire table validates this  array here, which is the new value here with meh instead of average.  And imagine that you want to do this for all the tables, talk feedback, speaker feedback  and everything.  So this is going to be very costly.  This is bad.  This is terrible.  So that's why normalization is good.  So this is the code for updating the table, the lookup table with feedbacks instead of  saying average, we say meh.  Is that all the code?  Yes, it is.  There's nothing else.  Yeah.  That's why normalization is good.  So feedback with text check.  Terrible.  We put the three X there.  We don't like it at all.  And this is when this approach kind of went wrong.  And the foreign key is good and is fast.  And this is perfect.  This is the why you need normalization.  What about enums?  So this is the moment that you say like, maybe this is not so good.  This is a possibility of doing update with an enum.  You alter the type.  Yeah.  So this is really DDL.  So you alter the type and you say like, rename the value average to meh.  Would that be enough?  Yes.  That's everything that you need to do.  So we can also put a check there with in on feedback is as good and as fast and as efficient  as with a foreign key.  Yeah.  There you go.  Good.  Now let's say that we want to delete data.  So awesome.  What is that?  I mean, you ask somebody, what do you have for lunch?  They say, whoa, burger in a lager.  Oh, awesome.  That's not awesome.  Especially if it's a lager.  So people say too fast.  Awesome.  So let's say that we don't want to have awesome anymore.  And we go again with the same thing.  And we're going to say that, okay, if somebody said the awesome, just put it a great is the  best closest value.  So we need to get a new array here.  You know the story.  Very expensive.  Not so good.  But I mean, there's nothing else that you can do.  Is it better with normalization?  While you say delete from feedbacks, where name is awesome.  This should be enough, but when you run this is going to say like, oh, sorry, first you  need to clean up all your tables.  So you still need to do all these updates in all the other tables before, in order to  be able to remove one value here.  So it is a lot of work to remove, remove this thing, even if he is a normalized data.  Yeah.  So this is going to complain that you still have pointers from all the tables to this  lookup table.  Now for inam, unfortunately, there is no alter table drop value that doesn't exist.  So stripe that.  And there's some possibilities of creating a different type.  You still go through all the cleanup in all the tables and then you change the column  type.  I'm not going to go into the details of this one, but I want to show you something similar  about alternative data types.  So that's it.  I mean, deleting this kind of value is not so good for anyone.  It's possible.  It's possible.  Yeah.  That's why I put warnings there.  So nobody scores well here.  So fair approach.  So an extra feature.  So we saw insert.  We saw retrieving data.  We saw updating data and delete.  Yeah.  So let's see this extra feature that inam has.  Might not be that important, but I think it's cool.  So let's see.  Inams are order.  So when you create the data type, you give it in an order.  So it goes from awful to awesome.  Yeah.  Then when you want to say, for instance, give me all the data rooms that has a feedback  larger than good.  So that would be scores that were great and awesome and how many times it happened this.  I also want to have the name of the data rooms.  So I have this join here.  Yeah.  So look, if I do it with text, this doesn't work because this is going to give me alphanumeric  order.  So great and good, well, we great, but awesome wouldn't be there.  So it doesn't work.  That's makes sense actually.  So but you can do it with data types, sorry, with inam types, enumerated types.  And it works.  So you can try that.  Now let's say that, I don't want to use always the same order.  Let's say that I put them in a wrong order.  You can create another inam type.  Let's call it feedback alt, this one, where I put whatever order I want because people  say awesome too quickly.  So the first three are okay, but I want to put awesome just before average, after average  and before good.  So the real good one is great.  Yeah.  So my query is going to change now.  So only going to give me the grid.  So this is the first query when I say like feedback is larger than good.  If I want to use this dynamic order change, I'm going to do the cast here.  So you see I have the feedback.  I'm going to cast it to text and from text I'm going to cast it to the feedback alternative.  I could define it a little bit more to avoid this text intermediate stuff.  But with this one is clear.  I don't have to add extra code.  I'm not hiding any code.  This is exactly the code that you need to do for getting an alternative order.  So this is strongly type with some dynamic order.  I think it's powerful.  Yeah.  Well, anyway, you can also add values that are older into your inam.  Let's say, well, in the beginning, we saw that we changed average to me.  So we just say rename value average to me.  And then we're going to add new things.  Let's say like somebody was not bad, but it was just unlucky.  So we insert unlucky before me and we insert presentation was fine after me.  And we can do this in the type just like this.  This is all the code that you need.  And if you want to know what's going on in your type, you can always use this describe  type capital T and with a plus and it's going to give you all the values in order.  So now you know what's the order of your data types.  Now, you can also investigate your numeric type and enumerated types.  Sorry, not numeric enumerated by using this views from the catalog Pg underscore inam  and Pg underscore type.  Remember that this is a type.  So the type name is feedback.  And then you can say in order by inam sort order.  And now you can see a little bit of the implementation of it.  So this is the order.  You get the label and it has some real numbers there.  And that's how you can put things in between.  Yeah.  So it works.  There you have the inam.  And you can say like, oh, I can also do this on lookup table.  Come on.  I just use the order of the of the ID and then I get the same order.  I just need to do some joint tables and everything.  But then if you want to add some things or you want to use this dynamic order, you cannot  well, okay, but let's say this is use a rank.  And you can use like alternative ranking and everything.  So here I have a rank.  But not to use an integer here because you might want to have new values and they need  a real number or reorder all these values.  It's complicated.  So let's say that you have unlucky and fine and because they were before and after may  you have to put this 2.5 and 3.5.  So now it looks a little bit or odd.  They have here an order by numbers, which is different than this one.  I'm a little bit picky because I want to promote inam, but let's order it by rank and then you  see the integers and from the other side.  Well, but this works.  Yeah.  So you need to add an extra column to your lookup table just because of the order.  But it works.  So let's get back to better than good.  This is the first query that I show you with inam.  This is a query with a lookup table and a ranking.  So again, you add an extra join to your table and you need to add an INER select just to  get the value of the ranking of good in order to do this larger than.  So it's a very short example.  It can get more complicated.  So that's what I want to show you that inam is straightforward.  Yeah.  Okay.  So this extra feature, the approach comparison, we can put it ordering with text check.  You cannot have any order because it's going to give you alphabetic, alphanometric order,  nothing to do with the real order.  With foreign key, you can do it, but you need to add another column and then some other  extra stuff with feedback.  You get it for free, the inam type.  Yeah.  So you might use it.  Okay.  So I'm closing words.  So now you know better, inam in Postgres.  So you might want to give it a try if you really are convinced and you have already your values  that are in text and select away, I have my text, I have my array.  How do I move it to inam?  Well you just create a type inam and you do a type cast from text to your new type and  it's going to work.  Now, what I feel is that when you're modeling your database, it matches better your way of  thinking.  So I create a type called feedback.  I can have countries because they don't vary that much.  I can have types of beer, for instance.  You don't discover a new type of beer so often.  It can be a variation, things like that, but you have a core type of beer.  So you can use inam for this.  It matches the way of your thinking of your applications.  When you read the table, say, okay, of course, this is a beer type.  Oh, this is feedback.  Instead of saying text or integer, what does it mean?  And as you saw already, this is very efficiently normalized.  A lot of things that are implemented for you and you don't need to go through all these  difficult changes in order to modify the values.  Everything is for you.  Except for delete, of course.  I understand this is a draw, but it is not as easy with the others as well.  And just remember that this presentation, it is specific to PostgreSQL.  Inam is not a SQL standard, so it's not implemented in all the databases.  And those databases that are implemented, they don't do it exactly the same way as PostgreSQL.  So this is specific to PostgreSQL.  This is the PostgreSQL step room and post them.  And I hope that you can see the value of this if you're using inam in your using PostgreSQL.  So contact me, ask me questions.  I will be glad to answer your questions on Twitter as well if you want to know more about  inams.  Yeah.  And I think that's all from me.  Thank you very much.  And unfortunately, we cannot go for a beer after this presentation, but it's early in  the morning.  But also we are not in person.  But next time we see each other, we can have a chat and a beer.  So thank you very much.  Bye.  I recorded that in the evening, but it's okay.  I think you can start in any moment now.  I see nothing happening.  So, okay, we are online.  Gio, would you like to start?  Fantastic.  Yes, that was a lovely talk.  I know so much about Inam and we have gotten quite some questions.  I will start with the very first one.  When should I not use Enum?  Yeah.  Thanks, Giorgio.  So, what I explained, one of the main issues, one of the main benefits of Inam is to use  it for data integrity.  And as somebody says, it's kind of a built-in way of having a normalized lookup table.  But it works even more efficient because the implementation works pretty well.  It is very cheap to do updates of the values.  And the reordering is also possible to add things in between.  So that's the one more use case, data integrity.  And when you don't want to use Inam, it's when your values are going to change a lot.  When your values are going to be deleted, when your values are going to be reordered  in a different way.  But I did show you a little bit of some tricks for doing some casts instead of having alternative  ordering for the Inam.  So you can have a catalog of ordering.  That would be a possibility.  And actually, deleting values is not only a problem for Inam.  It's actually a problem of having actually a lookup table as well because the value that  you want to delete is populated in all the tables.  So you still have to clean up that value.  But when things are kind of like statics and you want to have data integrity, I think  that's the best use case.  So if you don't need that, then you suggest use text, something that is more valuable.  OK, so the next question is by Alit Sekuharcik.  I remember from many years back that changing the Nam caused a huge log.  Is it still present?  Now, renaming the value, it is what you want to have.  And the value is not stored on the tables.  It's only the reference to the Inam.  So if you look at the size, it is four bytes to the pointer to the Inam.  So when you do the update of the values, it's just going to be on the table.  So all the locking, probably if you are running queries that are dependent on the value at  that time, those are going to be niche.  But I think it still gives you a different guarantee that when you're looking with a  lookup table.  In terms of changing the value, it's the same cost as a lookup table, actually.  Yeah.  Right.  The next question is from Publux.  So is it still impossible to change a new order except update the system catalogs?  No, so you can add things in between.  No, the only way would be to create an alternative apparel Inam with the same values in a different  order and then you can do the cost.  And then you would have to do the cost of the column if you want to do that.  So that's going to be a little bit expensive.  Yeah.  So that's one of the use cases that you don't want to have Inam if you have to change the  order in too much.
postgresql_i_use_enum_vindicating_the_underdog_of_data_types_0_179.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_180_1523.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_1524_2171.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_2172_4643.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_4644_5075.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_5076_5735.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_5736_6023.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_6024_6311.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_6312_8111.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_8112_8351.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_8352_8819.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_8820_9563.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_9564_10583.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_10584_11075.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_11076_11219.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_11220_14627.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_14628_17447.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_17448_18851.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_18852_18935.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_18936_19043.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_19044_21047.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_21048_21587.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_21588_21863.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_21864_23795.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_23796_24287.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_24288_24863.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_24864_25763.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_25764_26291.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_26292_26459.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_26460_26891.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_26892_27191.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_27192_27563.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_27564_27815.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_27816_28835.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_28836_31355.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_31356_33479.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_33480_34739.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_34740_37511.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_37512_38003.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_38004_38279.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_38280_40871.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_40872_41279.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_41280_41411.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_41412_43343.png postgresql_i_use_enum_vindicating_the_underdog_of_data_types_43344_43468.png