Inserting Data and Managing Conflicts

[00:00:00]
>> So that is the databases and tables. We're gonna start getting into how to handle more data going in and out of your Postgres database. So we're gonna go down here the next lesson, and we're gonna do inserts first. So now we have three different columns that we have to insert into, well, two of them we have to, one of them we could leave blank.

[00:00:25]
So let's go ahead and do that right now, we're gonna say INSERT INTO ingredients, okay. And we're gonna insert them in the order of title, image, type, we don't have to do it in that order, but this is the order that we're gonna do it. Okay, and then we're gonna say VALUES.

[00:00:53]
Single quotes, again, I'm gonna re-emphasize that, single quotes, not double quotes, 'red pepper', 'red_pepper.jpg' and we gonna make it a 'vegetable', okay? Close the parentheses semicolon and there we go. We inserted one item into our database. The 0 means we didn't update anything, all we did was insert.

[00:01:31]
So this is kind of a weird syntax, why is it done this way? Well, we can actually comma separated these values, and we can insert multiple values at a time, so we can insert like 10 rows at a time and we'll do that here in just a second.

[00:01:42]
But and then here, we're just telling it the order that we're going to put these things. Notice that title is the first one, image was the second one, type was the third one, right? We can reorganize that, there's no sacred order here. So I could have image first as long as I put redpepper.jpg first, right?

[00:02:03]
And that is 99% of what inserting is into, you just insert into and you tell at the table. You tell it the order that you're gonna give it things and then you give it the values, the end. So let's talk just a moment about single quotes, double quotes.

[00:02:18]
This was confusing for me as a JavaScript developer where it makes literally no difference, right? It does make a difference when it comes to SQL. So let's do this again, we're gonna say INSERT INTO ingredients. Here, we can use double quotes. So I can say "title", I can say "image" and I can say, I can leave it off for type if I feel like it, right?

[00:02:47]
This is letting it knows it's the actual name of the column, right? So we're actually, I can even put ingredients in quotes, let's do that. Anyway, you can put ingredients in quotes as well cuz it's the name of the table, right? So it's the identifiers that you can put into the double quotes.

[00:03:02]
But notice here, here I'm doing it, here I'm not, it's optional and frequently I don't, almost never do. And then here again, because it's the actual, the literal value that I'm putting in here, I have to use single quotes, 'broccoli', 'broccoli.jpg', and I'm pretty sure it's a 'vegetable', okay?

[00:03:36]
So another thing to note here is that whitespace is not significant, notice I'm just putting things on new lines and I'm putting. If you look read my notes there's tabs everywhere, whitespace is not significant, SQL you can put infinite space in there and it doesn't care.
>> Since type is a key word, is there an issue having that as a column name.

[00:04:00]
>> It's good question. Since type is a key word, like you'll notice in my, some of my highlighting, it'll show up as highlighted. It's not right when, you can see here in this particular case, there are things I think that are off limits for column names, but I think most of it it's pretty fair game because it's usually unambiguous in the grammar of SQL.

[00:04:23]
There are people, I'm sure some of them might take exception that I call the type but I've called lots of stuff type in my career. So here we are.
>> So do you put single quotes into matters of value type, even if it's like a number?
>> So that's a good question.

[00:04:38]
No, it's just for a string types. Yeah, so if I wanted to put like five there, I would just put the actual number five because if you put the quotes around it becomes a string, right, that's a good thing to point out, good question. To do comments in SQL.

[00:04:53]
So this will actually fail because we have a unique constraint. But notice here I have the dash dash here, this is how you do comments, in SQL is the dash dash, anything that comes after the dash dash is commented. And again here, this should fail cuz it's gonna say, this title key here broccoli we put a unique constraint on that, so that the title is unique for that particular table, so it failed to insert that's cuz I tried to insert it again cuz it's already in there, right?

[00:05:28]
One of the things I like about Postgres is I think it has for the most part, pretty useful errors, right? Key, title = broccoli already exists, lets me know right away if you violated the constraint around having unique titles. You can name this ingredients_title_key, right? If I say \d, you can see or maybe here if I do ingredients.

[00:06:00]
You can see that this has an index on the title key. We'll talk about indexes in a bit, but you can actually name these whatever you want. But if you don't name them, it has really useful sane default names for them. So I generally don't name them because I like the names that they, I would be typing out those names anyway, so I just let them name it for me.

[00:06:18]
But if you see pkey that's another common one you'll see her, stands for primary key.
>> Are tables stored like btree?
>> Tables are not stored as btrees. Indexes are stored as btrees sometimes. We will talk about that when we get to indexes. The actual physical manifestation of how Postgres stores them, I actually don't know but it would not be a btree.

[00:06:46]
So I wanna put a bunch of stuff in our table and I don't wanna type it all out. So you and I are gonna copy this query right here. Yeah, right there, feel free to copy and paste too much typing, okay? Now if I paste this in here, it might get a little mangled because it might be too long for your, you can see right here it's kinda mangled there and it's fine, it's just my terminal is not emulating it correctly.

[00:07:16]
I hit Enter, you can see that I had 29 that inserted correctly. The only interesting thing here is, one, you can see here we're just comma separating all these values out, right? But the interesting thing here is ON CONFLICT DO NOTHING. All right, let's go look it over here, it's pretty easy to see, I had for example broccoli was in there already and red pepper was in there already, right?

[00:07:42]
That would have been a conflict cuz it's like hey, you're trying to insert the title key again here, so basically ON CONFLICT DO NOTHING is saying. If you hit any of these rows that you want let me insert, just drop it, I don't care, just keep going, right?

[00:07:57]
So I can actually potentially just run this again and it's gonna insert 0 but it's not gonna crash, right? Whereas if I did it again and I delete the ON CONFLICT part and I just, I don't cuz is that correct, no, it's not, so we're just gonna copy and paste to here.

[00:08:25]
You can always hit Ctrl+C and that'll escape whatever query you're in the middle of. Now, I do that again. So, because I didn't put the ON CONFLICT DO NOTHING here, it's gonna immediately fail and then nothing gets inserted, right? So you have to tell it what to do ON CONFLICTs.

[00:08:46]
It's good if you're doing massive inserts or you're seeding a database with a bunch of information to give it those kinds of ON CONFLICT things, otherwise if you have one error, everything crashes. So I just wanted to show you a different way or something else you can do with that ON CONFLICT.

[00:09:03]
If I say INSERT INTO ingredients and I say title, image, value, VALUES, as a 'watermelon'. You can see there that watermelon definitely already exists and I have, and I wanna give it the image of banana cuz I don't live by the rules. And then I can say something of the type here, but I can just say 'this won''t be updated'.

[00:09:44]
So you might wonder why am I putting two quotes there? This is how you escape single quotes inside of the values, right? Because we can only use single quotes. We need some way to say like what happens if I actually want a single quote inside of my query.

[00:09:59]
The way you do that it's not with backslash which is what I was used to with literally everything else in SQL, you need to put two single quotes next to each other. Okay, this won't work, right, I can actually just run it as is and it's gonna say I didn't put it, it's not valuing it's type.

[00:10:19]
Okay, you can't put watermelon in here, I already have a watermelon heck off, I'm not going to allow you to do this. What if I want to say if this doesn't exist insert it, if it does exist update it, right? So watermelon exists. What if I just wanna update it.

[00:10:42]
This is where ON CONFLICT comes in help, is helpful again. ON CONFLICT, you have to tell it on what sort of conflict, in this case, title. Then you're gonna say DO UPDATE SET image = excluded.image. So imagine if I have 50 of these, right? And I wanted to just update it if it has some sort of conflict there, that's what the excluded.images.

[00:11:14]
The excluded means the row that got excluded and then I want you to pull the image off and I want you to set the image on the whatever one that conflict it on title. So just to prove my point from you, again, but we'll talk about selects here just a second, SELECT * FROM ingredients WHERE title = 'watermelon', into there it did update.

[00:11:38]
I don't have two rows in here, I have just one and the one got updated. You will typically hear this referred to as an upsert, right? It's an update and an insert in one step. If you're doing something like MongoDB, they actually literally call it upsert, right? It's the name of the method that you use.

[00:11:59]
In SQL, you're gonna do this ON CONFLICT business. So if I come back and do this again, what happened? You can also just say that this would just get set to some string as well, right? And now if I do this, let's say I have 500 conflicts, right?

[00:12:18]
All of them we can set to default.jpg instead of actually going and update it.