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.

Get Unlimited Access Now

Transcript from the "PostgreSQL" Lesson

[00:00:00]
>> 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.

[00:00:27] 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.

[00:00:55] 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.

[00:01:17] 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.

[00:01:42] 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.

[00:02:09] 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.

[00:02:32] 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.

[00:02:56] 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.

[00:03:21] 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.

[00:03:52] 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.

[00:04:14] 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.

[00:04:35] 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.

[00:04:57] 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.