Insert Data into Postgres Tables

Brett Cassette
InstructorBrett Cassette
Share this video with your friends

Social Share Links

Send Tweet
Published 7 years ago
Updated 5 years ago

Learn how to insert one record, two records, a million records! We’ll also explore some edge cases, like where not all data is known at the time of insertion.

[00:00] Now, we're going to insert some data into the director's table. We're going to insert into the name column. We only have an ID and a name. We're going to insert Quentin Tarantino.

[00:11] Hopefully, you didn't insert any data before. If you did, just look ahead to the "Deleting" episode, and make sure you delete that data, and you have a clean slate here.

[00:18] We can run this a bunch of times. We'll see, we just keep inserting Quentin Tarantino, because we don't have any kind of constraint on the table that says, "Quentin can't be in there multiple times." That's what is allowed right now.

[00:31] We can also insert Judd Apatow. If I want to insert multiple at the same time, I can do that. I can insert Judd and Mel. Again, that will keep going if we keep running that.

[00:45] Let's see what else happens, though, if we do some weird things. We want to see what happens. We can insert nothing. That will return in a syntax error. We can't insert null, because we have a constraint on our column that says it cannot be null. If we remember, from our director's field, we see name not null, that's part of our table definition.

[01:12] For good measure, I'll show you what it looks like if you want to insert into multiple columns. We could say 200, and we could say M. Night Shyamalyan. Insert that guy. It will actually give us the ID 200. But I just want to stress that we never actually want to do this, because as we get up to the number 200, we're going to run into a key conflict.

[01:37] There's something under the hood that is going to tell us what the next ID is. We don't really want to mess with this ourselves. I'm just letting you know. Don't mess with it if you have a serial column.

[01:49] If we really want to see what it's like to insert a bunch of values, let's insert into the movies table. Again, we going to leave off the ID. We'll add the title, the release date, the count stars and the director ID. Another value statement here. This is going to be a list of fields.

[02:08] Remember, if you don't remember all the columns on it you can take a look at what your table definition is, all the different Postgres editors have different ways of doing this. You have to look at your particular one. These are going to go in the order that I specify them in.

[02:24] The title will be "Kill Bill," and the release date was October 10th 2003. Count stars, I haven't seen it, you can hate on me later, I'll assume it was a three. Then a one. We'll also do a select star from movies, just so we see what that's about. Here's our movie, here's "Kill Bill," our count stars, our director ID. This should be linked up with the first Quentin Tarantino.

[02:51] Let's just see here. Let's switch up the title and release date, just to prove that we can do these in any order that we want to. We'll flip this, and this. It's not aligned, but our editor will understand either way. Just doing that so it's easier for us to read as humans.

[03:11] Let's also insert a second movie, to show what that's like. Remember, we put the comma, and then, we open up another parenthesis to put this in. Let's say, this was the movie "Funny People." That is a Judd Apatow movie. We have to go look for his ID 11. Let's say that's five stars, because any movie I've seen is five stars.

[03:34] There we go. That will happen there. We see that we did still give the next "Kill Bill" three stars, director ID one, and the title and the release date go in the right column. Nothing got messed up by us moving these around.

[03:47] I just want to show you here something else that is nice. Postgres will save us if we make some really obvious errors. If our date, time doesn't make any sense, then, it will fix that. But in other cases it's not going to fix this for us.

[04:05] If we try to insert a number for "Kill Bill," it will figure out a way to parse that to a string. The number five is parsable. It will do that. These are things to watch out for and things to notice. You can play around with that a little bit yourself.

[04:20] That's the basic way that you're going to insert data into a table.

Long
Long
~ 7 years ago

Hi Brett, what is the name of the application that you used in the videos ?

Serge Bedzhik
Serge Bedzhik
~ 6 years ago

And again: "INSERT INTO directors (name) ('Quentin Tarantino');" is "INSERT INTO directors (name) VALUES ('Quentin Tarantino');" I suppose? Pretty careless transcripts so far…

Joe
Joe
~ 4 years ago

Hi Brett, what is the name of the application that you used in the videos ?

Looks like SQLPro https://macpostgresclient.com/

Markdown supported.
Become a member to join the discussionEnroll Today