1 00:00:00,049 --> 00:00:04,949 Michael: Hello, and welcome to Postgres FM, a weekly show about all things PostgreSQL. 2 00:00:05,270 --> 00:00:09,590 I am Michael I'm, founder of pgMustard, and this is my co-host Nikolay founder of Postgres AI. 3 00:00:10,100 --> 00:00:11,720 Hey Nikolay what are we talking about today? 4 00:00:11,990 --> 00:00:12,650 Nikolay: Hi, Michael. 5 00:00:12,655 --> 00:00:19,115 So it's time to talk about various mistakes and I've noticed that many people from my conference experience and so on, I noticed 6 00:00:19,115 --> 00:00:23,674 that, uh, many people love to hear about other people mistake. 7 00:00:24,545 --> 00:00:29,795 I don't know like why they love it so much, but I I've noticed it's very popular. 8 00:00:29,795 --> 00:00:40,385 When you talk about, we made this mistake, it was so painful and people enjoy listening or watching, uh, maybe they want to learn something. 9 00:00:40,385 --> 00:00:40,684 I don't know. 10 00:00:41,464 --> 00:00:42,305 Michael: yeah, I love it. 11 00:00:42,535 --> 00:00:44,044 And there are two main reasons. 12 00:00:44,044 --> 00:00:47,705 One is the hope that by hearing about their mistake. 13 00:00:48,006 --> 00:00:53,056 I hopefully can avoid making the exact same one, avoid having to make that myself, uh, it's extra painful. 14 00:00:53,056 --> 00:00:58,816 And then the second reason I love it is it just makes you feel better when you make mistakes, knowing that these 15 00:00:58,816 --> 00:01:01,731 amazing other people make mistakes all the time too. 16 00:01:02,001 --> 00:01:04,311 So I think that's for me, there's that part of it as well. 17 00:01:04,431 --> 00:01:04,731 Nikolay: Right. 18 00:01:04,811 --> 00:01:12,391 And, and, it's already fifth episode of our podcast and I I've noticed we have quite good numbers so far, and I wanted 19 00:01:12,391 --> 00:01:16,447 to thank everyone who is sharing and liking and so on. 20 00:01:16,727 --> 00:01:17,477 Please continue. 21 00:01:17,747 --> 00:01:23,507 It's it helps we, we see this and we also seek comments in Twitter feedback. 22 00:01:23,512 --> 00:01:24,497 It it's so great to see. 23 00:01:24,742 --> 00:01:24,982 Michael: Yeah. 24 00:01:24,982 --> 00:01:25,702 And thank you to everyone. 25 00:01:25,702 --> 00:01:28,822 Who's suggested ways we can make it better as well, really appreciated. 26 00:01:28,882 --> 00:01:32,482 Keep topic suggestions coming to, we'll try to get to quite a few of them over time. 27 00:01:32,781 --> 00:01:39,238 Nikolay: Oh, and I also also want to say big hello to all those who are currently running or riding bicycle. 28 00:01:39,238 --> 00:01:40,498 And so it's good. 29 00:01:40,708 --> 00:01:41,978 I, I also need to do it. 30 00:01:43,558 --> 00:01:43,888 Okay. 31 00:01:43,948 --> 00:01:51,355 Uh, so, mistakes, if we talk about mistakes, uh, related to sequel and specifically Pogs, I think the biggest, the most 32 00:01:51,360 --> 00:01:55,555 painful, at least in my career, it it's always related to NULLs. 33 00:01:55,825 --> 00:01:57,715 Now it, this is the most. 34 00:01:58,140 --> 00:02:06,540 Uh, the, the, the thing I hate most in SQL, uh, in theory, in, in, in like SQL model, if you think about SQL model in standard 35 00:02:06,760 --> 00:02:10,770 in, in various database systems and specifically in Pogo. 36 00:02:11,700 --> 00:02:13,410 So let's talk about NULLs today. 37 00:02:13,515 --> 00:02:13,965 Michael: Awesome. 38 00:02:14,055 --> 00:02:19,815 And one of the reasons they're so painful is because the mistakes don't come back right away. 39 00:02:19,815 --> 00:02:20,055 Right. 40 00:02:20,060 --> 00:02:24,525 You don't necessarily hit an error message or you don't, uh, you know, there's yeah. 41 00:02:24,555 --> 00:02:27,585 That feels like they kind of, they aren't invisible for a while. 42 00:02:27,675 --> 00:02:29,355 Um, so that, 43 00:02:29,385 --> 00:02:29,655 Yeah. 44 00:02:30,405 --> 00:02:31,365 that's a better word for it. 45 00:02:31,365 --> 00:02:31,575 Yeah. 46 00:02:32,295 --> 00:02:34,305 Nikolay: mm-hmm mm-hmm yeah, hidden, silent. 47 00:02:34,305 --> 00:02:34,755 And so on. 48 00:02:34,755 --> 00:02:38,685 This is a like, if, if of course, if you see the error, you can, you can react. 49 00:02:38,685 --> 00:02:41,955 But if you don't see error and NULLs are very, very tricky. 50 00:02:42,638 --> 00:02:46,327 Let's start with maybe some news about Pogs 15. 51 00:02:46,365 --> 00:02:54,725 There will be new capability that will allow people to say that unique constraints should not distinguish NULLs. 52 00:02:54,740 --> 00:02:58,065 So there is only one in universe for some unique constraint. 53 00:02:58,335 --> 00:03:03,325 By default, all NULLs are distinguishable like they are all different. 54 00:03:03,715 --> 00:03:11,595 So we cannot say this now is the same as that now, because now it means unknown in sequel logic, sequel logic is based on three valued logic. 55 00:03:12,135 --> 00:03:15,360 It's, it's worth understanding what three valued logic is. 56 00:03:15,365 --> 00:03:20,470 And again, if you don't know, please check out, Wikipedia or other basic articles. 57 00:03:20,470 --> 00:03:22,720 It's very fundamental knowledge. 58 00:03:23,190 --> 00:03:24,250 Everyone should possess. 59 00:03:24,730 --> 00:03:31,450 So if you compare to NAS, uh, the result is always unknown, which actually translates to now, if we like 60 00:03:31,660 --> 00:03:35,050 we don't have unknown values, we have only now values. 61 00:03:35,050 --> 00:03:43,110 So like NULL, compared to NULL, Kind of also NULL, however, strictly speaking, it's unknown. 62 00:03:43,500 --> 00:03:50,826 And that's why if you define a unique constraint, you can insert as many nails in this column as you want. 63 00:03:51,286 --> 00:04:00,401 This is by the way, uh, Big difference between unique constraint and primary key is because primary key is, uh, completely forbid nows in, in columns that 64 00:04:00,401 --> 00:04:04,871 are, that participate in primary key, but unique constraint allow, allow nows. 65 00:04:04,871 --> 00:04:13,981 So, if it's, a one column unique constraint, you can insert as many values of now as, as you want as a mineral having now in this 66 00:04:13,981 --> 00:04:23,280 column, as you want, and this in some cases is not convenient to some developers so that's why in Pogo 15, a longer waited feature appeared. 67 00:04:23,537 --> 00:04:25,882 can So default defaulted behavior is the same. 68 00:04:26,092 --> 00:04:34,102 You can sort as many notes as you want, but with new option, I don't remember this option, but this option allows you to say. 69 00:04:34,787 --> 00:04:36,887 They can be only one now here. 70 00:04:37,757 --> 00:04:37,967 Right? 71 00:04:38,537 --> 00:04:44,507 And this is like kind of, I, I like that this is explicit option, not a, a setting. 72 00:04:44,567 --> 00:04:45,887 You, you change like you change. 73 00:04:45,917 --> 00:04:47,987 Of course it'll be, it'll be nightmare. 74 00:04:47,992 --> 00:04:53,267 And it'll break all, all logic that standard dictates and so on. 75 00:04:53,597 --> 00:04:54,977 It's good that it's explicit. 76 00:04:54,977 --> 00:04:58,967 And, and, uh, for only for those who need it will have it, 77 00:05:00,457 --> 00:05:02,137 Michael: Yeah, I saw it. 78 00:05:02,227 --> 00:05:02,677 Nikolay: I guess. 79 00:05:02,917 --> 00:05:03,207 Yeah. 80 00:05:03,267 --> 00:05:03,487 if 81 00:05:03,697 --> 00:05:04,087 Michael: Yeah. 82 00:05:04,177 --> 00:05:04,807 I saw it. 83 00:05:06,112 --> 00:05:06,532 Sorry. 84 00:05:06,532 --> 00:05:09,742 I saw a good blog post on this by Ryan Lambert. 85 00:05:09,832 --> 00:05:13,162 Um, maybe a month or so ago, I'll share it in the show notes. 86 00:05:13,852 --> 00:05:18,562 I thought it was super interesting, but I was also struggling to come up with a use case for it. 87 00:05:18,562 --> 00:05:27,922 I didn't really understand why you would want to be able to determine that there was exactly or sorry at most one, NULL value in a column. 88 00:05:28,252 --> 00:05:29,692 Did you, did you ever come across it? 89 00:05:29,722 --> 00:05:31,312 Nikolay: Well, uh, yeah, if, if you. 90 00:05:33,902 --> 00:05:37,322 I remember some, somewhere in some articles probably or some discussions. 91 00:05:37,322 --> 00:05:44,252 So we compared, uh, like when you start understanding three value logic, you are kind of infected, you always think about, okay. 92 00:05:44,282 --> 00:05:44,432 Okay. 93 00:05:44,432 --> 00:05:45,632 We have no unknowns. 94 00:05:45,842 --> 00:05:46,322 You start. 95 00:05:46,352 --> 00:05:51,452 If you, if you are more, that waste guy, you start teaching your, uh, backend and front end. 96 00:05:51,512 --> 00:06:00,157 Uh, developers, colleagues, especially front end colleagues who write a lot of JavaScript code that, you know, unknown is unknown. 97 00:06:00,162 --> 00:06:08,947 You cannot return like, think it's, it's about it as an, as an zero or about it as a, uh, empty string. 98 00:06:09,247 --> 00:06:17,917 So, so, and at some point you probably want to create some table and, uh, use a NULL as like unknown. 99 00:06:18,217 --> 00:06:19,957 And you can imagine, for example, we. 100 00:06:20,887 --> 00:06:28,027 Uh, for example, um, true, false and unknown, and we want to restrict only three options. 101 00:06:28,387 --> 00:06:38,117 It can be for example, two column, uh constrainted for each person, we can say the, you can, for example, it's it can be a poll results. 102 00:06:39,037 --> 00:06:42,607 So each person can say, I agree, I disagree. 103 00:06:42,607 --> 00:06:45,637 Or I don't vote in this question, for example. 104 00:06:45,637 --> 00:06:45,907 Right. 105 00:06:46,207 --> 00:06:49,177 And, uh, each person can say only once. 106 00:06:49,777 --> 00:06:52,837 So we can have personal ID and result, right. 107 00:06:53,647 --> 00:06:54,907 And unknown result. 108 00:06:54,907 --> 00:07:05,437 We can use nails to, to record, uh, uh, that, uh, the vote is like unknown, but it's actually, it's breaking logic because in this case, the third 109 00:07:05,437 --> 00:07:11,857 option, I, I, I, by purpose, I don't want vote is, is kind of known result. 110 00:07:12,157 --> 00:07:13,987 So strictly speaking, I wouldn't do this. 111 00:07:13,987 --> 00:07:16,057 I would use, for example, minus one or. 112 00:07:16,708 --> 00:07:24,628 Some people may choose mouse and then they want to, uh, restrict, uh, and say only one result for each person is possible. 113 00:07:24,658 --> 00:07:31,733 In this case, you want unique constraint and in this case, you would want to have only one now possible for one person, 114 00:07:31,733 --> 00:07:38,113 something like this is what popped up in to my head initially, like I can imagine this can be useful, but I would avoid it. 115 00:07:38,638 --> 00:07:39,448 Michael: That makes sense. 116 00:07:39,498 --> 00:07:47,313 I actually used to work at a company that did survey software and we did have more than one negative value because we had, well, for 117 00:07:47,318 --> 00:07:53,103 example, you might have seen the question and skipped it versus you might never have been presented the question and those are different. 118 00:07:53,943 --> 00:07:55,353 Um, so yeah, very interesting. 119 00:07:56,075 --> 00:07:58,673 Nikolay: Various flavors of various meanings. 120 00:07:58,673 --> 00:08:05,520 I, I I understand some, some people need it and Pogo this is probably not the biggest problem with no sexually. 121 00:08:05,520 --> 00:08:07,700 Let's about, uh, other issues. 122 00:08:07,700 --> 00:08:16,207 For example, I had a bad situation like roughly 10 years ago, it was my third one already social media. 123 00:08:16,237 --> 00:08:24,277 And I was completely sure that it's good and market is ready we we should launch with great success. 124 00:08:24,727 --> 00:08:26,317 And I remember we launched. 125 00:08:27,367 --> 00:08:33,527 Slowly in, in spring first and everything was ready to to grow. 126 00:08:33,587 --> 00:08:41,580 And, uh, we used a lot of mechanics, uh, all social you might, you might hate it or not, but when you check friends, Uh, initiating 127 00:08:41,580 --> 00:08:49,460 some invitations, uh, without, uh, lying of because, uh, we know the case LinkedIn had and so on, but it's a different topic, so 128 00:08:49,820 --> 00:08:53,370 everything was ready and I saw audience and, and like likes it. 129 00:08:53,640 --> 00:09:01,742 But at some point during summer, we experienced a very low uh, I, I didn't understand why, because I see like, like if we talk about, if 130 00:09:01,742 --> 00:09:06,152 you talk to specific people, they are happy, they consider project. 131 00:09:06,152 --> 00:09:06,842 They're interesting. 132 00:09:06,842 --> 00:09:18,327 And uh, analytics shows us, they use this system to connect to friends and so on, but somehow don't don't grow as I expected as I And my my experience 133 00:09:18,327 --> 00:09:26,566 was already quite good because I, it was already a third project for I like, I understand how people work and so on, but somehow it didn't grow. 134 00:09:26,596 --> 00:09:31,936 And I remember in the, in early September, I went to like digging what's happening. 135 00:09:31,936 --> 00:09:36,346 I went to code and I saw some select, which. 136 00:09:37,111 --> 00:09:42,451 I don't remember it was comparison or some etic operation, but it was not NULL safe. 137 00:09:42,991 --> 00:09:50,951 So the result was now, but developer obviously didn't expect result was unknown, converted to implicitly converted to 138 00:09:50,951 --> 00:09:54,671 now, but developer didn't expect now to be present there. 139 00:09:54,671 --> 00:09:58,121 And I just put coalesce there. 140 00:09:59,141 --> 00:10:03,671 and we started to grow like, like rocket during next several months. 141 00:10:03,821 --> 00:10:06,281 so by the end of year, it was huge success. 142 00:10:06,371 --> 00:10:08,201 So I just put one word coalesce that's there 143 00:10:08,649 --> 00:10:10,004 Michael: Wow so what was this doing? 144 00:10:10,004 --> 00:10:15,194 What part of it, like, was it rec, was it about, um, suggesting people add others? 145 00:10:15,194 --> 00:10:17,144 Or like how, how did that limit growth so 146 00:10:18,171 --> 00:10:22,421 Nikolay: if, if you're interested in off topic for us, but if you're interested, usually all social networks do like 147 00:10:22,451 --> 00:10:30,541 they, they offer you Who, which friends already use this service and when you do it, they suggest you to connect 148 00:10:31,074 --> 00:10:38,844 Michael: So back to back to coalesce, my understanding of that is it returns the first non-no, value that it that it sees. 149 00:10:38,844 --> 00:10:43,174 So you can, for example, put coalesce and then, the result you're expecting 150 00:10:43,786 --> 00:10:51,864 Nikolay: Zero for example, you expect, if, for example, want to use amatic uh, to make them now safe. 151 00:10:51,864 --> 00:10:58,714 You need to use coals, for example, uh, use zero when there is no value, otherwise whole result will become. 152 00:10:58,714 --> 00:11:03,004 Now if at least one now is present among, among, right. 153 00:11:03,334 --> 00:11:08,764 And this is exactly what happened Because of that, the thing didn't work at all. 154 00:11:09,904 --> 00:11:13,954 And, and once we fixed it, it was like, it was so good. 155 00:11:15,244 --> 00:11:15,814 I was happy. 156 00:11:15,861 --> 00:11:26,168 I I uh, the loss, like, and it was estimate was like $30,000 during several months loss of this, uh, missing one 157 00:11:26,200 --> 00:11:27,610 Michael: And that's at a startup, right. 158 00:11:27,610 --> 00:11:30,580 That could be, that could be bigger at a larger company. 159 00:11:30,940 --> 00:11:31,750 Imagine Yeah. 160 00:11:32,200 --> 00:11:37,270 Um, so it's significant and of course, silent as we were talking about before it doesn't fail 161 00:11:38,138 --> 00:11:43,000 Nikolay: It's just working but not working well again, we had some registrations, but not enough. 162 00:11:43,005 --> 00:11:46,425 You know what I thought about it, it's not have this person mistake. 163 00:11:46,425 --> 00:11:47,835 I would do the same easily. 164 00:11:47,925 --> 00:11:52,785 And even if you have 20 years experience writing SQL as I do. 165 00:11:52,910 --> 00:11:54,980 I completely a hundred percent understand. 166 00:11:55,250 --> 00:12:05,265 I realize it, that I, I still have high chances to write some not now safe code . this is the thing that you need always to keep in mind. 167 00:12:05,835 --> 00:12:13,995 And it's so hard to just forget about it and have it, uh, once again, have not safe operations and it's just dangerous part of SQL, 168 00:12:14,730 --> 00:12:18,210 Michael: So, I guess this takes us back to why they're your least favorite part? 169 00:12:18,483 --> 00:12:18,873 Nikolay: Right. 170 00:12:19,083 --> 00:12:24,623 So I like the flexibility because, through a false, like, it's not, it's not enough. 171 00:12:24,683 --> 00:12:33,683 You need something like unknown and it was added by purpose, but, maybe in future, we will have some better tools that will maybe it's already exists. 172 00:12:33,683 --> 00:12:41,710 I don't know if our listeners can suggest something, maybe there are tools that can put some warnings and say, you know, This 173 00:12:41,710 --> 00:12:45,760 column might have nows, and this operation is not now safe. 174 00:12:45,760 --> 00:12:48,250 Probably you will have unexpected results. 175 00:12:48,323 --> 00:12:52,733 It would be good to have such something like inci CD checked all the time we write code. 176 00:12:53,123 --> 00:12:59,733 But if you use ORM, which dynamically construct SQL , it's probably not. 177 00:13:00,333 --> 00:13:03,453 Well, it's an interesting topic, how to improve experience. 178 00:13:03,928 --> 00:13:04,948 I'm kind percent sure. 179 00:13:05,278 --> 00:13:09,033 We, we're not in the perfect position in terms of NAS in relational databases. 180 00:13:09,978 --> 00:13:12,198 Michael: That makes complete sense and tooling sounds interesting. 181 00:13:12,198 --> 00:13:17,808 I wonder if it could be like some even static analysis on, I guess you might need to know that a column does 182 00:13:17,813 --> 00:13:19,818 contain those, but knowing that it could is enough. 183 00:13:19,818 --> 00:13:20,118 Right. 184 00:13:20,298 --> 00:13:21,918 So that's yeah, it feels doable. 185 00:13:22,368 --> 00:13:23,908 I'm not aware of anything myself. 186 00:13:24,633 --> 00:13:33,330 So, so is the, the messages basically remember that, uh, something could be, could be know and handle it appropriately, but try to 187 00:13:33,330 --> 00:13:36,810 remember is the best and maybe, maybe useful for code reviews as well. 188 00:13:36,910 --> 00:13:37,790 I think sometimes. 189 00:13:39,055 --> 00:13:45,835 In teams that I see there's sometimes more people that are comfortable reviewing backend code or 190 00:13:45,835 --> 00:13:48,955 application code than there are reviewing database code. 191 00:13:49,285 --> 00:13:56,255 And sometimes there's, you know, there's an expert on the team or somebody who's better at this than most people, perhaps sometimes their code isn't 192 00:13:56,255 --> 00:14:00,815 getting reviewed or, you know, just remembering to review SQL as well. 193 00:14:02,055 --> 00:14:10,456 Nikolay: if you, if you are reviewer uh, of course this is one should be, you should have in, in, in in your back of, of reviewer's tool 194 00:14:10,456 --> 00:14:19,426 set, let's check if all operations are now safe, but I, I think it's not we, we should uh, the path that leads us to better results by 195 00:14:19,686 --> 00:14:24,216 Michael: Definitely not the perfect solution, but for now, I'm looking forward to hearing if there are good tools for 196 00:14:24,216 --> 00:14:29,466 this, but if there aren't and if neither of us are aware of them for now, I guess that's what people need to be doing. 197 00:14:29,526 --> 00:14:32,966 You know, the, the developer has to think about it and also the reviewer. 198 00:14:33,439 --> 00:14:33,799 Nikolay: all right. 199 00:14:33,799 --> 00:14:40,789 And we also discussed only operations in comparison, but there are many, many, many more other thick places where 200 00:14:40,794 --> 00:14:46,609 mask can, can, uh, shoot off your, uh, legs or, or arms. 201 00:14:46,699 --> 00:14:46,969 I dunno. 202 00:14:46,969 --> 00:14:48,619 Uh, For uh, aggregate. 203 00:14:49,019 --> 00:14:54,899 Some aggregates ignores some aggregates don't ignores, uh, some aggregates. 204 00:14:54,959 --> 00:14:57,179 Do it depending on situation. 205 00:14:57,179 --> 00:15:04,109 For example, if you do some again, like summarizing all values in a column, nows will be ignored. 206 00:15:04,114 --> 00:15:04,499 Of course. 207 00:15:04,499 --> 00:15:04,799 Right. 208 00:15:05,669 --> 00:15:09,487 Although some is based on plus operator, right? 209 00:15:09,547 --> 00:15:10,687 Michael: What's the, yeah. 210 00:15:10,807 --> 00:15:13,237 What would the difference be if they weren't ignored? 211 00:15:13,417 --> 00:15:15,167 It would return NULL guess 212 00:15:15,273 --> 00:15:19,863 Nikolay: If you think about some as first value plus second value, plus blah, blah, blah. 213 00:15:20,062 --> 00:15:22,522 If now, as present results also be now as well, 214 00:15:22,767 --> 00:15:23,007 Michael: Yeah. 215 00:15:23,012 --> 00:15:23,577 I see what you mean. 216 00:15:23,702 --> 00:15:28,082 Nikolay: but some actually does coalesce coma zero, right? 217 00:15:28,082 --> 00:15:30,392 So like it ignores now. 218 00:15:30,392 --> 00:15:31,952 So it's like now safe. 219 00:15:32,132 --> 00:15:32,432 Right? 220 00:15:32,562 --> 00:15:35,322 Michael: One people aren't probably aware of is, is count. 221 00:15:35,524 --> 00:15:39,754 Nikolay: count is, is interesting count, uh, for column will also ignore now. 222 00:15:39,754 --> 00:15:44,211 So it won't, it won't count, rose where you have now for this column. 223 00:15:44,451 --> 00:15:47,287 But if you say count star, all rose will be counted. 224 00:15:47,389 --> 00:15:51,149 And if you say count, table name from table. 225 00:15:51,764 --> 00:16:00,590 It's possible because like, you can count the whole row considering whole row, all its columns, as, as one single value it's POS magic you. 226 00:16:00,900 --> 00:16:04,014 It's like record type again. 227 00:16:04,014 --> 00:16:05,004 Mouse will be counted. 228 00:16:05,185 --> 00:16:11,875 Michael: What about this is probably ridiculous and definitely not a real case, but if every column in the 229 00:16:11,875 --> 00:16:15,375 table was no, sorry, imagine a single row with every column. 230 00:16:15,375 --> 00:16:15,765 No. 231 00:16:16,125 --> 00:16:16,575 Nikolay: single 232 00:16:16,845 --> 00:16:19,255 Michael: Would that be counted by table? 233 00:16:19,725 --> 00:16:20,085 Okay. 234 00:16:20,085 --> 00:16:20,475 Wow. 235 00:16:20,625 --> 00:16:22,005 Nikolay: yes, yes. 236 00:16:22,005 --> 00:16:22,245 Why? 237 00:16:22,245 --> 00:16:27,675 No, because I also discussed now some time ago that two years ago, it was a YouTube, life stream. 238 00:16:28,065 --> 00:16:34,035 And I said, you know, of course, if we, if, if we do this, what you describe it won't be counted. 239 00:16:34,125 --> 00:16:34,695 I do it. 240 00:16:34,695 --> 00:16:35,505 And I see it's counted. 241 00:16:35,565 --> 00:16:36,345 Michael: That's so funny. 242 00:16:37,560 --> 00:16:43,865 Nikolay: Like it's, it's so many unexpected parts when behavior is not as like we could have some certifications, 243 00:16:43,865 --> 00:16:51,977 like no expert in progress and also like array a aggregation. 244 00:16:52,667 --> 00:16:56,737 What do you think will be counted or like used or 245 00:16:56,992 --> 00:16:58,852 Michael: May it be a good quiz, wouldn't it? 246 00:16:58,982 --> 00:17:05,912 Nikolay: You if you have a chain of questions, uh, specifically boom, moving to very bad places. 247 00:17:06,692 --> 00:17:09,152 so it would, it would be tricky to answer everything properly. 248 00:17:09,272 --> 00:17:09,602 Michael: Yeah. 249 00:17:09,932 --> 00:17:11,672 What grade would you give people at the end? 250 00:17:11,702 --> 00:17:15,842 Would it be, a, B, C, D E or no, maybe 251 00:17:18,737 --> 00:17:19,427 Nikolay: or unknown. 252 00:17:19,607 --> 00:17:23,117 So what, what do you think about array aggregation array, 253 00:17:23,387 --> 00:17:23,567 a G 254 00:17:23,692 --> 00:17:23,812 Michael: I 255 00:17:25,457 --> 00:17:26,067 Nikolay: aggregate 256 00:17:27,362 --> 00:17:35,132 Michael: I'll be, I'm gonna guess I'm gonna play this game and I am gonna say that it does not ignore Knowles. 257 00:17:35,702 --> 00:17:36,122 Nikolay: Yes. 258 00:17:36,127 --> 00:17:38,612 It'll take them and use as a members of array. 259 00:17:38,852 --> 00:17:39,362 Exactly. 260 00:17:39,632 --> 00:17:47,282 So you see like it's, it's, uh, , it's a very complex thing, just one word, 261 00:17:47,282 --> 00:17:48,992 Michael: That was genuinely a 50, 50 guest. 262 00:17:48,997 --> 00:17:52,262 So I could, I would definitely not uh, I didn't know that. 263 00:17:52,430 --> 00:17:56,060 , Nikolay: al also like, like, uh, disclaimer, might be wrong. 264 00:17:57,950 --> 00:17:58,790 Michael: What percentage 265 00:17:59,540 --> 00:18:05,610 Nikolay: as I, as I've said, as I've said, no matter how many years experience you have, you cannot be a hundred percent, right. 266 00:18:05,610 --> 00:18:06,210 All the time. 267 00:18:06,240 --> 00:18:06,920 Uh, with 268 00:18:08,179 --> 00:18:16,868 Michael: But the, the, crucial part here is not being unaware of which one it is, it's remembering that it might not be null safe in the first place. 269 00:18:16,898 --> 00:18:22,068 So there might be unexpected behaviors when you're working with data that can include nulls. 270 00:18:22,278 --> 00:18:24,198 So it's the same takeaway. 271 00:18:24,198 --> 00:18:24,468 Isn't. 272 00:18:25,458 --> 00:18:31,441 Remember that when you're working with data that contains null values or is that I'm not even sure if that's the right way of saying it, that 273 00:18:31,441 --> 00:18:40,961 contains nulls then remember to test check, put some test data in, add some realistic test data and then check that your code's working as expected. 274 00:18:41,381 --> 00:18:43,481 But the, the key part is remembering. 275 00:18:45,046 --> 00:18:45,346 Nikolay: Sure. 276 00:18:46,216 --> 00:18:47,296 I'm quite skeptical. 277 00:18:47,301 --> 00:18:53,896 I think people really understand all the dangers related to now only when they experience it in production. 278 00:18:54,162 --> 00:19:01,012 So like learning curve, should bring you some more real examples to hit you so badly that you understand. 279 00:19:01,012 --> 00:19:02,482 Oh, next time I should be very. 280 00:19:03,322 --> 00:19:03,612 Michael: Yeah. 281 00:19:03,944 --> 00:19:08,701 Nikolay: also only recently I learned, for example, there is Jason B set function. 282 00:19:08,764 --> 00:19:14,764 And if you it's can be used to set some part of Jason B value to some different value. 283 00:19:14,764 --> 00:19:17,694 So not, not, not be need to. 284 00:19:18,694 --> 00:19:19,594 Override everything. 285 00:19:19,984 --> 00:19:23,554 So you can say along this path, I need to replace something. 286 00:19:23,674 --> 00:19:27,424 And if you put, if you want, Jason also has nulls. 287 00:19:27,507 --> 00:19:38,267 If you want to put now there and say Jason B provide some path array of keys and then say, no, you will have whole value set to know unexpectedly. 288 00:19:39,587 --> 00:19:44,947 So you need to use single quote now converted to J and B instead of regular sequel now. 289 00:19:45,157 --> 00:19:48,891 So it's like complete everywhere. 290 00:19:49,414 --> 00:19:53,074 Michael: you say converted to do you mean like the cast operator or what do you mean? 291 00:19:53,767 --> 00:19:54,157 Nikolay: Yes. 292 00:19:54,157 --> 00:19:55,477 Concussed to JS and B. 293 00:19:55,837 --> 00:19:59,921 So now, uh, string converter to J and V. 294 00:19:59,921 --> 00:20:05,836 So in this case, uh, it'll be as expected you say you only change the part of the whole value, not the. 295 00:20:05,926 --> 00:20:12,086 Michael: So if I'm a team lead and I've got a few junior developers on my team and I'd like them to learn a few 296 00:20:12,091 --> 00:20:15,476 things around this, are there any good starting places? 297 00:20:15,476 --> 00:20:20,366 I, I know you said they probably need to learn through experience, but if I want to try and avoid that, what can I do? 298 00:20:20,603 --> 00:20:21,473 Nikolay: Oh, it's a good question. 299 00:20:21,478 --> 00:20:22,403 And probably a hard one. 300 00:20:23,003 --> 00:20:27,323 I will, of course do some training maybe related to NAS. 301 00:20:28,058 --> 00:20:32,288 Of course making sure everyone, uh, understands what, three valid logic is. 302 00:20:32,288 --> 00:20:39,493 And so some theoretical basics and then, uh, some examples, I don't know, like it depends on the tools for education. 303 00:20:39,553 --> 00:20:44,549 You have, of course, if you have some playground where you some databases it's and you. 304 00:20:45,014 --> 00:20:46,214 do some exercises. 305 00:20:46,254 --> 00:20:51,694 It would be, it would be helpful to write some code and see how dangerous now can be. 306 00:20:52,324 --> 00:20:59,396 But, I don't have excellent, simple answer here again, as I've said, I'm very skeptical and think only when you hit 307 00:20:59,966 --> 00:21:06,448 only when it hits you very badly breaking some functionality only, then you probably will learn that it's really D. 308 00:21:06,761 --> 00:21:13,401 Michael: Yeah, I think I saw a really good article by hacky Benita that included an issue they came across that I will share in the show notes. 309 00:21:13,406 --> 00:21:16,374 And I think you shared one from as Jeff Davis. 310 00:21:16,763 --> 00:21:24,853 Nikolay: Jeff Davis, very old article and excellent collection of very unexpected examples, much more than we discussed here. 311 00:21:24,943 --> 00:21:27,794 So yeah, let's also attach the show 312 00:21:27,986 --> 00:21:28,586 Michael: For sure. 313 00:21:28,703 --> 00:21:33,838 I know it's difficult to ask people to go away and read stuff, but it feels like learning that there even are. 314 00:21:33,838 --> 00:21:36,028 These problems gives you a chance of knowing. 315 00:21:36,028 --> 00:21:39,538 I think probably sometimes people don't even realize this could be a problem. 316 00:21:39,838 --> 00:21:42,448 I definitely only learn about coalesce quite late in. 317 00:21:42,838 --> 00:21:45,898 I knew quite a lot about SQL sequel before learning that 318 00:21:46,423 --> 00:21:48,223 Nikolay: Anti joinin example as well. 319 00:21:48,313 --> 00:21:56,803 If you, if you, if you use a not in and then expect, then have nail inside in, you can also have unexpected, it's quite popular. 320 00:21:57,133 --> 00:21:58,183 I think all articles. 321 00:21:58,183 --> 00:22:06,263 So we mentioned, uh, they, they should discuss this example as anti joinin also like have some problems nails 322 00:22:06,263 --> 00:22:07,433 Michael: Same with not exists. 323 00:22:07,493 --> 00:22:07,953 I'm guessing. 324 00:22:08,163 --> 00:22:09,753 Nikolay: Uh here's the trick. 325 00:22:11,088 --> 00:22:12,468 I, I always forget about it. 326 00:22:12,468 --> 00:22:21,473 Actually, I, as I remember, not in is dangerous, but not exist is not something like this, but every time I deal with it, I'm refreshing memory 327 00:22:21,478 --> 00:22:30,642 and either experimenting gum or checking some book posts or articles, unfortunately again, it's a bag of knowledge you should carry all the time. 328 00:22:30,642 --> 00:22:34,743 And it's easy to stop thinking about it, if you don't use it every day. 329 00:22:37,218 --> 00:22:38,508 Michael: Always test, kids. 330 00:22:40,008 --> 00:22:40,308 Nikolay: Yes. 331 00:22:40,308 --> 00:22:41,958 So testing is perfect. 332 00:22:41,958 --> 00:22:44,628 So if you, if you think about something just tested. 333 00:22:44,735 --> 00:22:49,865 Michael: Yeah, actually one other, so in terms of learning resources, there are a couple of others that I thought were great. 334 00:22:49,985 --> 00:22:57,949 Probably always a good port of call is have you come across Marcus Winand's modern SQL, uh, or modern sequel 335 00:22:58,614 --> 00:22:58,904 Nikolay: long 336 00:22:59,365 --> 00:23:00,775 Michael: I think that's, that's pretty great. 337 00:23:00,775 --> 00:23:03,445 I'd be surprised if they don't have something around this. 338 00:23:04,525 --> 00:23:04,975 Awesome. 339 00:23:05,370 --> 00:23:05,790 Nikolay: Mm-hmm 340 00:23:06,265 --> 00:23:08,285 Michael: Do you, uh, the book Art of PostgreSQL. 341 00:23:08,305 --> 00:23:13,885 I think that's quite good in terms of like a mixture of beginner friendly and quite advanced topics, 342 00:23:14,074 --> 00:23:15,820 Nikolay: should, be somewhere behind 343 00:23:15,900 --> 00:23:16,465 Michael: purple one. 344 00:23:16,465 --> 00:23:16,645 Right. 345 00:23:17,365 --> 00:23:19,105 Um, awesome. 346 00:23:19,255 --> 00:23:23,965 So I think those, those stand out to me as particularly good places to learn. 347 00:23:24,177 --> 00:23:26,517 also anything else on those you wanted to talk about? 348 00:23:26,705 --> 00:23:35,630 Nikolay: Well, last thing I would like to say to add, you say like tested that I say tested, but I think many times when people 349 00:23:35,680 --> 00:23:42,915 start, it's hard for them to test properly because to create proper test, you need to understand the depth of the issue. 350 00:23:43,335 --> 00:23:54,853 And I wish some people, like, I so many times people go to some chats or some places where they can get public help and ask questions and got reaction. 351 00:23:54,858 --> 00:23:56,893 Like, oh, why you just don't test it? 352 00:23:57,403 --> 00:24:06,813 I wish some people like it's can be considered as negative actually reaction, but I agree that I don't know why the new people, why they don't ask. 353 00:24:07,233 --> 00:24:09,813 Can you help me to create proper. 354 00:24:10,728 --> 00:24:13,368 So I, I would, I would see myself, 355 00:24:13,914 --> 00:24:14,364 Michael: Yeah. 356 00:24:14,658 --> 00:24:19,188 Nikolay: So please help me to test it all, all the aspects of this problem. 357 00:24:19,698 --> 00:24:23,568 This would be a great, uh, approach to ask questions. 358 00:24:23,568 --> 00:24:24,978 When you start with some topic. 359 00:24:25,074 --> 00:24:26,214 Michael: I completely agree. 360 00:24:26,214 --> 00:24:33,514 I think we could definitely be a little bit friendlier in some communities in the Postgres world to newcomers, but equally I do 361 00:24:33,514 --> 00:24:40,549 also understand that we do get people that clearly haven't even used Google or even looked in the docs at the most appropriate place. 362 00:24:40,554 --> 00:24:42,469 So it, I think it goes both. 363 00:24:43,189 --> 00:24:53,239 But for sure, teaching people how to, how to share an online snippet or how to set a little test example in one of the, uh, fiddle tools or something like 364 00:24:53,239 --> 00:24:57,384 that, just to demonstrate their problem a little bit clearer goes a long way. 365 00:24:57,384 --> 00:25:01,354 And if you're asking for help, it definitely helps to show that you've tried something yourself first. 366 00:25:01,664 --> 00:25:04,624 But, I think it, I think we can all improve there for sure. 367 00:25:04,778 --> 00:25:04,958 Nikolay: Yeah. 368 00:25:05,768 --> 00:25:06,368 Okay, 369 00:25:07,095 --> 00:25:07,335 Michael: Nice. 370 00:25:07,335 --> 00:25:07,545 One. 371 00:25:07,828 --> 00:25:08,998 Nikolay: I hope it was helpful to 372 00:25:09,449 --> 00:25:10,379 Michael: I hope so, too. 373 00:25:10,718 --> 00:25:16,358 Nikolay: basic material, but as I've said, even you have 20 years of experience, uh, it it's still, it's still hurts. 374 00:25:16,458 --> 00:25:19,008 Michael: Hopefully this was a good public service announcement, at least. 375 00:25:19,008 --> 00:25:22,913 And I think some of this is quite technical and quite difficult to do without examples. 376 00:25:22,913 --> 00:25:26,723 So the articles we share in the show notes might be extra helpful for this one. 377 00:25:26,723 --> 00:25:29,544 So, I'll make sure to make those nice and clear. 378 00:25:29,588 --> 00:25:31,838 Nikolay: Oh, one more thing I wanted to mention. 379 00:25:31,908 --> 00:25:33,948 I forgot, uh, since August 11. 380 00:25:34,628 --> 00:25:37,148 We have, we are in better place . Why? 381 00:25:37,148 --> 00:25:38,588 Because before SGEs 11. 382 00:25:38,588 --> 00:25:47,522 Imagine if you have a billion row table and Romanian NA so, and you add some column, which can be true, false, 383 00:25:48,122 --> 00:25:51,182 and you have you think, okay, I want it to be strict. 384 00:25:51,182 --> 00:25:52,622 I want to have not now there. 385 00:25:53,972 --> 00:25:57,092 So, so only true or, and, or only false two 386 00:25:57,589 --> 00:25:58,819 Michael: Exclusive luck, right. 387 00:25:59,092 --> 00:26:01,802 Nikolay: you know, Yes, updating billion rows. 388 00:26:01,802 --> 00:26:02,822 It's it's nightmare. 389 00:26:02,822 --> 00:26:04,052 I don't want to go with it. 390 00:26:04,082 --> 00:26:06,362 So I will consider NAS as false. 391 00:26:06,432 --> 00:26:12,448 And this is what we always did because practically it was a big headache for us to update billion rows. 392 00:26:12,448 --> 00:26:19,531 So we started to treat NAS as false and because practically it makes sense you avoid big problems, but 393 00:26:19,531 --> 00:26:23,035 since post August 11, we can say default falls, that's it. 394 00:26:23,725 --> 00:26:26,665 And it'll, it'll be fast since post 11. 395 00:26:26,773 --> 00:26:29,563 Even you can say default falls. 396 00:26:29,623 --> 00:26:30,283 Not now. 397 00:26:30,475 --> 00:26:33,063 Michael: What does that mean Do you, oh, one minute. 398 00:26:33,063 --> 00:26:34,173 So I, I think I understand. 399 00:26:34,383 --> 00:26:34,953 So you could add. 400 00:26:35,007 --> 00:26:37,052 Nikolay: you, you can, you get results. 401 00:26:37,052 --> 00:26:39,392 You want only two options are possible. 402 00:26:39,392 --> 00:26:42,362 All existing row, uh, have false. 403 00:26:42,511 --> 00:26:44,191 and now that is prohibited in this column. 404 00:26:44,191 --> 00:26:44,941 It's perfect. 405 00:26:45,013 --> 00:26:51,171 By the way, the same trick allows you to redefine primary keys since Paul, August 11, but it's different topic. 406 00:26:51,171 --> 00:26:53,631 Let's discuss it another another day. 407 00:26:53,631 --> 00:26:54,081 Probably. 408 00:26:54,162 --> 00:26:54,582 Michael: Awesome. 409 00:26:55,002 --> 00:26:57,972 Well, thanks again, everybody for listening. 410 00:26:58,072 --> 00:27:00,922 keep the feedback coming and hope you have a good week. 411 00:27:02,821 --> 00:27:05,941 Nikolay: Don't forget to share us as much as possible. 412 00:27:05,941 --> 00:27:06,961 We like it a lot. 413 00:27:06,991 --> 00:27:07,321 Thank you. 414 00:27:07,491 --> 00:27:07,651 So. 415 00:27:08,002 --> 00:27:08,422 Michael: Cheers. 416 00:27:08,427 --> 00:27:08,812 Bye now. 417 00:27:09,136 --> 00:27:09,436 Nikolay: See you.