Check out a free preview of the full Complete Intro to Databases course

The "PostgreSQL" Lesson is part of the full, Complete Intro to Databases course featured in this preview video. Here's what you'd learn in this lesson:

Brian demonstrates how to create and run a PostgreSQL database inside of a Docker container. The database created is a sample PostgreSQL message board database containing various boards, messages, users, and comments.


Transcript from the "PostgreSQL" Lesson

>> So we're gonna talk about Postgres. So you can see down here, PostgreSQL, the way you pronounce that aloud is Postgres, just so you know. Great open source database, has a huge market share, very well used with Node in particular. Has some really killer features, which I'm excited to talk to you about today, very flexible, scalable.

Yeah, very good database. So that's what we're gonna talk about today. So going back here, I'm gonna get out of Mongo. And here I'm gonna say docker ps, I called this test-mongo. So I'm gonna say docker kill test-mongo. Just so you know, this is going to actually kill the server and will drop all of your data.

So if you wanna keep it around, just leave this running. I'm done with it. So I'm gonna say docker kill test-mongo. Now you can see it is no longer running. Instead I'm gonna run this one here. Docker run --name my-postgres. In retrospect, that's really funny. That's my SQL and I'm calling it my Postgres.

That was not intentional but kinda funny. You do have to give it a login password or will not start Postgres for you. I mean, I think they're doing that in the name of security. Obviously, we're not doing anything secure here. So I just said mysecretpassword. We'll have to use it on a the connection stream later, that's the only place.

Port 5432, that's the one that we wanna forward on. Make sure you're putting that in there. D for detach, --rm for remove the logs when we're done. And we're gonna run Postgres:13.0. So that should be running now, and then we're gonna run this right here, this docker exec right here, to attach this program here called pSQL.

That's the name of the command line client. And you can see now I am connected to Postgres. So again, we're gonna start with databases, right? We are inside of a database and right now we are connected to the Postgres database. By default, you connect to this generic database called Postgres.

We may wanna use something else. So let's make our own and call it message boards, so we're gonna say, CREATE DATABASE message_boards. Very critical here that you put semicolons, Postgres doesn't know you're done until you put a semicolon. And then you know something is successful when it tells you that it repeats back.

Just like, I created a database, it'll say back to create database, okay? So now we've created a database and now we have to use that database. So the way that we'll end up doing that, not use, connect. That's what it is. Sometimes it's a struggle to keep all of these various syntax in my head.

All right, so you're gonna say \c message_boards. And then it'll say you are now connected to the database message_boards as user Postgres. So the backslash character is kind of, by the way, you can also say connect. So this c is just short for that. The way that you do admin commands in Postgres is use the backslash.

So if I do \l, you can see here, I have a message_boards database, a Postgres database, and there's some template databases. That's kind of to list the databases out to see all the tables in a database. Right now, we don't have any tables but \d will let me know all of the tables in my database.

If you just wanna see all the available commands, \ question mark will output. Here's everything that you can do with backslash. And if you do backslash, oop, yeah, and it puts it into more, right? Cuz there's a lot of stuff you can do with this, right. Anyway, and then \h.

This will let you know here's all the various different kinds of queries you can do. Again, that can sometimes be useful. I don't know why keeps beeping at me, that's fine. You can hit Q to get out of those lists, by the way, and Q will drop you out of those lists.

And then lastly here, you can do --, And that's a comment, how you do a comment in SQL is with dash dash. So anything after the dash dash is a comment to the end of the line.

Learn Straight from the Experts Who Shape the Modern Web

  • In-depth Courses
  • Industry Leading Experts
  • Learning Paths
  • Live Interactive Workshops
Get Unlimited Access Now