Join egghead, unlock knowledge.

Want more egghead?

This lesson is for members. Join us? Get access to all 3,000+ tutorials + a community with expert developers around the world.

Unlock This Lesson
Become a member
to unlock all features

Level Up!

Access all courses & lessons on egghead today and lock-in your price for life.


    Insert Data into Postgres Tables

    Brett CassetteBrett Cassette

    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.



    Become a Member to view code

    You must be a Member to view code

    Access all courses and lessons, track your progress, gain confidence and expertise.

    Become a Member
    and unlock code for this lesson




    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.

    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.

    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.

    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.

    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.

    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.

    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.

    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.

    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.

    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.

    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.

    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.

    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.

    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.

    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.

    That's the basic way that you're going to insert data into a table.