Creating tables into a database (PostgreSQL)

To set up the database software (PostgreSQL) in your machine, run these commands:

$ brew install postgresql
$ brew services start postresql

In SQL we use the CRUD operations:

CRUD — Create, Read, Update and Delete

• to create data, we will use the command (INSERT)
• to Read data, (SELECT)
• to Update (UPDATE)
• to Delete (DELETE)

Now we have the basics to create our database. Let’s say we want to store data from Medium. I want to make two tables in my database: one for users and another for posts. I love to use Excalidraw to understand what I am doing before starting anything. My tables should be something like this:

I used Excalidraw to design these tables

In my posts table, I have a (foreing_key) link to my table users with my table posts. This means the table posts depend on users.
A post can exist only if have a user to write it.

posts “belongs to” users.

But the user is independent. We have users on Medium that has no posts. Also, one single user can have many posts, but one single post cannot have more than one user.

Let’s close Excalidraw and come back to the terminal.
We will open the software that we just installed and create a database by typing:

psql -h 127.0.0.1

It shows something like:

psql (14.4)
Type “help” for help.

To create a database, we’ll type:

CREATE DATABASE medium_data;

It outputs something like:

CREATE DATABASE;

Now you have a database called “medium_data”! You also can create a database without using psql as well. Just by typing: createdb medium_data

Now we should put some data inside of it. You should make a SQL file called “medium_data.sql” with some critical information about the tables you would like to create:

• create a table (the independent one) → users
• create another table (that belongs to the above one → this table has a foreign key) → posts

Following the example from Excalidraw, I would do something like:

SQL file “/medium_data.sql”

CREATE TABLE users (
id SERIAL PRIMARY KEY,
name text,
email text
);

CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title text,
content text,
user_id int,
constraint fk_user foreign key(user_id) references users(id)
);

Now let’s insert all this information into our database using the terminal.

psql -h 127.0.0.1 medium_data < medium_data.sql

You should see a message: CREATE TABLE twice.

Now we want to ensure our tables are there. Let’s go into our database by typing: psql -h 127.0.0.1 medium_data

Great! We will use the command SELECT to read if the information is there.
I will select all (*) the data from users table. Go into the database by typing:

SELECT * FROM users;

users table

And also to read the post table:

SELECT * FROM posts;

posts table

Amazing! We have a database called “medium_data” with two tables: “users” and “posts”, that allow us to store the information from the website!