Hello everybody, I’m glad to announce that this is my first post and video that I do. This is a simple introduction to PostgreSQL, also known as postgres, that will show you how to create a SQL database and a table

Postgres is a very popular relational database, it is open source and it is easy to get started with it.

This is intended to the anyone that is learning about general programming, who knows may be data science, business analysis, like I said this is a hands-on video that will show you how easy is to create a database and a table using prostgres.

How to install postgres⌗

My recommendation is to start with postgres, this relational database management system and the best thing on this is ’this is an opensource solution’, so this is free/libre so very important to have in mind. Installation it’s simple, just go the postgres official website and the instructions are actually there, very clear.

In case you’re using a macbook computer, you have two easy options to install:

  1. Install postgressapp
  2. Install posgres using docker

In this case, I will use option 1, since its the most simple to start, I will cover how to work with docker and postgres in another post. A terminal will be used as well.

Create a new database⌗

Great, now we’re going to do real work, let’s open up a terminal. It is very important to notice that postgres is called ‘psql’ from the terminal, you can check your postgres version doing this:

psql --version

That is important to know, because in the future you might want to know what version you’re using.

Let suppose we are creating a new database, so let’s create a database called example_db

CREATE DATABASE example_db OWNER my_name;

Normally the scripts intended for SQL are going to be called.

For removing/erasing databases there is the command DROP, which I would recommend to do a double check before proceeding, let’s do an example of dropping the example_db.

In order to clear your screen you can do this:

\! clear

Create a new table⌗

Well, now that you have created the database, at this point we should be knowing in advance the datatype of the information that we’re going to be using, for purposes of this video we’re going to use a csv generated with random information from [[https://extendsclass.com/csv-generator.html][Online CSV generator - Generate random SQL data]], you can pick you’re csv here or in any other place in the internet.

So, you can see there are 5 columns in our csv files.

We have downloaded the document and moved it into a different directory.

In order to create the table in postgres, let’s put the next on the terminal.

CREATE TABLE example_table(
id INTEGER,
firstname VARCHAR(30),
lastname VARCHAR(30),
email1 VARCHAR(50),
email2 VARCHAR(50),
profession VARCHAR(50));

Load a csv⌗

Now is the time to load the information that is stored in the CSV file, to do this we have to know the path to csv file, we’re going to use the next command of copy

COPY example_table(id, firstname, lastname, email1, email2, profession)
FROM '/Users/franciscosanudo/psql_example/myFile0.csv'
DELIMITER ','
CSV HEADER;

Take a look to the data you have loaded

SELECT * FROM example_table;

Conclusions⌗

Well now we have created our database and table in postgres, there are a lot to know about this for example it would be cool to use postgres in a docker container, but for now we would leave it here.