POSTGRESQL

It's a Object-relational database management system

It's open source, roboust and performatic

The database is too important to backend career

What is a Database

Is where you can store, manipulate and retrieve data on the server

What is SQL and relational Databases

Structured Query Language(sql) is a programming language used to manage data on the databases, especily structured databases

Data is stored in tables

Tables are formed in two ways:

• columns are the attributes of the table

• rows are the datas inside the table

Relational database is connection between two or more tables

Create a docker with postgress

docker container run -d -e POSTGRES_PASSWORD=senha postgres

To use de postgres, run the command

docker container exec -ti id psql -U postgres

List databases

\l

Create database

CREATE DATABASE test;

Important

You need put de semicolon on the line final

Note

The syntax can be write in small case

How to connect a database

on remote server

psql -h server -p port_number -U user database_name

inside postgres terminal interface

\c database_name

Delete a database

To delete a database, you can't use them

DROP DATABASE database_name;

How to create tables

CREATE TABLE table_name (
    column_name + data type + constraints if any
);

Example

CREATE TABLE person (
    per_id BIGSERIAL NOT NULL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    gender CHAR(1) NOT NULL,
    date_of_birth DATE NOT NULL
);

List tables

\d

show only tables:

\dt

Describe table

\d table_name

Drop tables

DROP TABLE table_name;

Insert

For insert records into tables we run the follow command:

INSERT INTO table (column) VALUE (value);

this person doesn't have an email

INSERT INTO person (name,gender,date_of_birth)
VALUES ('Anne Smith', 'F', DATE '1988-01-09');

this person has an email

INSERT INTO person (name,gender,date_of_birth,email)
VALUES ('Jake Jones', 'M', DATE '1990-01-10','jake@gmail.com');

Generate 1k rows Mockaroo

Access https://mockaroo.com/ to generate data

Run command from a file

\i /path/file.sql

Select data

SELECT * FROM person;

On place asteristc insert column names to select a specify column data

example:

SELECT name FROM person;
SELECT name, gender FROM person;

Order By

ASC: 1,2,3,4,5

DESC: 5,4,3,2,1

SELECT * FROM person ORDER BY column ASC|DESC;
SELECT * FROM person ORDER BY column,column ASC|DESC;

Distict

When you have many data with the same value, example country, you can 'agroup' with the same values

SELECT DISTINCT column FROM table;

Where

Use to filter data from database

example:

SELECT * FROM person WHERE gender = 'F';

To add more condition, you can use and or or

SELECT * FROM person WHERE gender = 'F' AND (country_of_birth='Brazil' OR country_of_birth='China');

Comparison operators

Operator	Name
=	equal
<	less than
<=	less or equal of
>	bigger than
>=	bigger or equal of
<>	different

In

SELECT * FROM person WHERE country_of_birth = 'Brazil' OR country_of_birth = 'China' OR country_of_birth = 'France';

Let's simplify this query

SELECT * FROM person WHERE country_of_birth IN ('Brazil','China','France');

Between

SELECT * FROM person WHERE date_of_birth BETWEEN DATE '2000-01-01' AND '2010-12-31';

Like and ILike

Like: search aboute a pattern

End with this pattern

SELECT * FROM person WHERE email LIKE '%.com';

Has this pattern

SELECT * FROM person WHERE email LIKE '%google%';

Use underscore, to represent count of characters

SELECT * FROM person WHERE email LIKE '________@%';

Ilike: use that patter without case sensitive

SELECT * FROM person WHERE country_of_birth ILIKE 'p%';

Group By

Is used to group datas in a column

SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth;

Group By Having

We can view only coutries have most 5 people

SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth HAVING COUNT(*) >= 5;

Calculing MIN, MAX, AVERAGE, SUM

SELECT MAX(price) FROM car;
SELECT MIN(price) FROM car;
SELECT AVG(price) FROM car;
SELECT ROUND(AVG(price)) FROM car;
SELECT SUM(price) FROM car;

You can make everything aritmethic operation that way

SELECT operation;

Round

SELECT id, make, model, price, ROUND(price * 0.1,2), ROUND(price - (price * .1),2) FROM car;

Alias

Rename a column

SELECT id, make, model, price AS original_price, ROUND(price * 0.1,2) AS ten_percent_value, ROUND(price - (price * .1),2) AS discount_after_ten_percent FROM car;

Coalesce

If a value is null, he can the next valid value

SELECT COALESCE(email,'Email not provided') FROM person;

NULLIF

Return null if the values're equals

If first argument's different than second argument, return the first argument

SELECT 10 / NULLIF(0,0);

Timestamps and Dates

Timestamp return date and time

SELECT NOW();
SELECT NOW()::DATE;
SELECT NOW()::TIME;

Adding and subtracting with dates

SELECT NOW() - INTERVAL '1 YEAR';
SELECT NOW() + INTERVAL '1 YEAR';

Extract fields from date

SELECT EXTRACT(YEAR FROM NOW());

Age function

SELECT *, AGE(NOW(), date_of_birth) AS age FROM person;

What are PRIMARY KEYS

Primary key is a uniquely identify a record in tables

It create a constraint to have a unique value in that table

Drop a constraint

ALTER TABLE person DROP CONSTRAINT person_pkey;

Add a constraint

ALTER TABLE person ADD PRIMARY KEY(id);

Unique constraint

Is used to have an unique value in that column

Example: each people has an email, two people cannot have the same email

ALTER TABLE person ADD CONSTRAINT unique_email_address UNIQUE(email);
ALTER TABLE person ADD UNIQUE(email);

Difference at primary and second way Primary: we put a name for the constraint Second: postgres put a name for the constraint

Check constraints

Impossibilite insert different values of declaration on check

ALTER TABLE person ADD CONSTRAINT gender_constraint CHECK (gender = 'F' OR gender = 'M');

How to delete records

Is common use primary key to delete a record in a table, because is unique

DELETE FROM person;

BUT, this command will delete ALL RECORDS

To delete a especify record, use WHERE clausure

DELETE FROM person WHERE id = 2;

How to update records

Make the same effect witch delete without WHERE

So, don't forget the WHERE clausure

UPDATE table SET column='new value' WHERE id=1;
UPDATE table SET column='new value',column='new value', ... WHERE id=1;

You can update an only or more column, separe they with the comma.

On conflict do nothing

To don't have an error on return

INSERT INTO person (id, name, email, gender, date_of_birth, country_of_birth) VALUES (1, 'Coretta Blaksland', 'cblaksland0@altervista.org', 'F', '1996-06-08', 'Mozambique')
ON CONFLICT (id) DO NOTHING;
INSERT INTO person (id, name, email, gender, date_of_birth, country_of_birth) VALUES (1, 'Coretta Blaksland', 'cblaksland0@altervista.org', 'F', '1996-06-08', 'Mozambique')
ON CONFLICT (email) DO NOTHING;
INSERT INTO person (id, name, email, gender, date_of_birth, country_of_birth) VALUES (1, 'Coretta Blaksland', 'cblaksland0@altervista.org', 'F', '1996-06-08', 'Mozambique')
ON CONFLICT DO NOTHING;

Upsert

We will update if try insert the same data and alter only email

INSERT INTO person (id, name, email, gender, date_of_birth, country_of_birth) VALUES (1, 'Coretta Blaksland', 'cblaksland0@altervista.org', 'F', '1996-06-08', 'Mozambique')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

You can update an only or more column, separe they with the comma.

What is a relationship and foreign keys

When we have a relationshipt, we put an foreign key in one table

Example: A person can have to one car, and a car can belong to one person

The foreign key will be put to create a relationship

Add Foreign key

With alter

ALTER TABLE table ADD COLUMN column_name type...;
ALTER TABLE table ADD CONSTRAINT constraint_name FOREIGN_KEY(column_name) REFERENCES other_table(id_other_table);
-- or
ALTER TABLE table ADD COLUMN column_name type... REFERENCES other_table (column_other_table);

On table creation

CREATE TABLE person (
    ...,
    car_id BIGINT REFERENCES car (id),
    UNIQUE(car_id)
);

For make on creation, you ma

Inner Joins

With inner join, we get the data has on two tables, at the same time.

SELECT * FROM person JOIN car ON person.car_id = car.id;

We are EXCLUDING the people has no car

Left Join

We get all data in left table and get also data combine left table with right table, and exclude right table without relationship

SELECT * FROM person LEFT JOIN car ON person.car_id = car.id;

Delete records with foreign key

We need to resolve first foreign key to delete a record

UPDATE person SET car_id = null WHERE id=4;
DELETE FROM car WHERE id=3;

Exporting Query Results to CSV

\copy (query) TO 'path/file.csv' DELIMITER ',' CSV HEADER;

Restart sequences

ALTER SEQUENCE seq_name RESTART WITH 1;

SOME DATATYPES DETAILS

BIGSERIAL

Autoincrementing integer number

Create a table to control this sequence

DATE

year-month-day