BrianMarquez3/PostgreSQL-Training

Learn PostgreSQL and how to get started quickly through practical examples.

🐘 PostgreSQL Training

PostgreSQL Documentation

Table of Contents

Numeration	Check	Stiker	Topic	Quantization	Edit Gitpot	Downloads	link
001	✔️		Downloads	✔️	💻	💾	⬅️ back
002	✔️		Usando la DB	✔️	💻	💾	⬅️ back
003	✔️		Visualizar Informacion Tablas	✔️	💻	💾	⬅️ back
004	✔️		SELECT	✔️	💻	💾	⬅️ back
005	✔️		SELECT DISTINCT	✔️	💻	💾	⬅️ back
006	✔️		SELECT COUNT	✔️	💻	💾	⬅️ back
007	✔️		WHERE	✔️	💻	💾	⬅️ back
008	✔️		ORDER BY	✔️	💻	💾	⬅️ back
009	✔️		LIMIT	✔️	💻	💾	⬅️ back
010	✔️		BETWEEN	✔️	💻	💾	⬅️ back
011	✔️		IN	✔️	💻	💾	⬅️ back
012	✔️		LIKE	✔️	💻	💾	⬅️ back
013	✔️		HAVING	✔️	💻	💾	⬅️ back
014	✔️		GROUP BY	✔️	💻	💾	⬅️ back
015	✔️		AS	✔️	💻	💾	⬅️ back

Union de Tablas

Numeration	Check	Stiker	Topic	Quantization	Edit Gitpot	Downloads	link
016	✔️		AS	✔️	💻	💾	⬅️ back
017	✔️		INNER JOIN	✔️	💻	💾	⬅️ back
018	✔️		FULL JOIN	✔️	💻	💾	⬅️ back
019	✔️		LEFT JOIN	✔️	💻	💾	⬅️ back
020	✔️		RIGHT JOIN	✔️	💻	💾	⬅️ back
021	✔️		UNION	✔️	💻	💾	⬅️ back

Funciones

Numeration	Check	Stiker	Topic	Quantization	Edit Gitpot	Downloads	link
022	✔️		Formato Hora Fecha	✔️	💻	💾	⬅️ back
023	✔️		Funciones Matematicas	✔️	💻	💾	⬅️ back
024	✔️		Funciones Caracteres	✔️	💻	💾	⬅️ back
025	✔️		Subconsulta Valor numerico	✔️	💻	💾	⬅️ back
026	✔️		Subconsulta por lista de Valores	✔️	💻	💾	⬅️ back
027	✔️		Subconsulta con EXITS	✔️	💻	💾	⬅️ back

Base de Datos

Numeration	Check	Stiker	Topic	Quantization	Edit Gitpot	Downloads	link
028	✔️		Bases de Datos	✔️	💻	💾	⬅️ back
029	✔️		Crear Tabla SQL	✔️	💻	💾	⬅️ back
030	✔️		Insert SQL	✔️	💻	💾	⬅️ back
031	✔️		UPDATE	✔️	💻	💾	⬅️ back
032	✔️		DELETE	✔️	💻	💾	⬅️ back
033	✔️		ALTER	✔️	💻	💾	⬅️ back
034	✔️		CASE	✔️	💻	💾	⬅️ back
035	✔️		COALESCE	✔️	💻	💾	⬅️ back
036	✔️		CAST	✔️	💻	💾	⬅️ back
037	✔️		Importar Ficheros	✔️	💻	💾	⬅️ back
038	✔️		Exportar Ficheros	✔️	💻	💾	⬅️ back

---

Downloads

⚙️ Install PostgreSQL Windows Download the installer for windows
⚙️ Install PostgreSQL Linux How to install and use PostgreSQL on Ubuntu

Correccion del Error PATH
Cambiar la Direccion del PATH de PostgreSQL

Linux installation - Ubuntu (Intallation/Comandos)

• Comandos consola Postgres

Install Postgres

• Install Postgres Actual en Ubuntu

• Install Postgres 14 en Ubuntu

• Install Doker

• Install Doker Compose

Delete Postgres

• Uninstall ubuntu Terminal Postgres Simple
• Eliminar Postgres Completamente

Restore Database

pg_restore -h 127.0.0.1 -p 5432 -U user -d database < backup.sql
psql -h 127.0.0.1 -p 5432 -U user -W -d dabatase < backup.sql

Backup Base de Datos

pg_dump -U postgres -W -h 127.0.0.1 database > backup.sql

Create Postgres Password

sudo -u postgres psql
\password
Enter password: ...

Inicialisar Postgres

 service postgresql start

Create Postgres User

• DigitalOcean

• Como crear usuarios en PostgreSQL

CREATE USER newuser WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE database to newuser;

Rename a database

ALTER DATABASE olddbname RENAME TO newdbname;

Delete user postgres

• Como crear usuarios en PostgreSQL

Configuration Information

La utilidad postgresql pg_lsclusters muestra información sobre la configuración y el estado de todos los clusters, incluyendo el número de puerto.

$ pg_lsclusters

Migrating from SQLite to Postgresql - Django

Migrating data from SQlite to PostgreSQL. Shift database from SQlite to PostgreSQL in Django Migrating data from SQlite to PostgreSQL | Django

Data.json

python manage.py dumpdata > data.json

Example Setting Django

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'p2phelp_db',
        'USER': 'briandb',
        'PASSWORD': 'briandb',
        'HOST': 'localhost',
        'PORT': '5432'
    }
}

Python Django Shell

python manage.py migrate --run-syncdb

python manage.py shell

from django.contrib.contenttypes.models import ContentType
ContentType.objects.all().delete()
exit()

python manage.py loaddata data.json

---

Usando la DB

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
002	Usando la DB	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Visualizar Informacion Tablas

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
003	Visualizar Informacion Tablas	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

SELECT

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
004	SELECT	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

SELECT DISTINCT

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
005	SELECT DISTINCT	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

SELECT COUNT

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
006	SELECT COUNT	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

WHERE

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
007	WHERE	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

ORDER BY

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
008	ORDER BY	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

LIMIT

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
009	LIMIT	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

BETWEEN

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
010	BETWEEN	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

IN

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
011	IN	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

LIKE

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
012	LIKE	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

GROUP-BY

SELECT "PRODUCTO", SUM("IMPORTE")
FROM esquema."PEDIDOS"
GROUP BY "PRODUCTO"

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
013	GROUP-BY	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

HAVING

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
014	HAVING	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Union de Tablas

AS

SELECT "ID" AS "CLAVE", "PRODUCTO" AS "VALOR 0", "IMPORTE"  AS "VALOR 1" 
from esquema."PEDIDOS"

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
015	AS	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

INNER JOIN

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
016	INNER JOIN	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

FULL JOIN

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
017	FULL JOIN	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

LEFT JOIN

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
018	LEFT JOIN	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

RIGHT JOIN

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
019	RIGHT JOIN	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

UNION

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
020	UNION	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Funciones

Formato Hora Fecha

SELECT EXTRACT (DAY FROM "FECHA") AS "DIAA"
FROM esquema."PEDIDOS" -- EXTRAE EL DIA

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
021	Formato Hora Fecha	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Funciones Matematicas

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
022	Funciones Matematicas	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Funciones Caracteres

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
023	Funciones Caracteres	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Subconsulta Valor numerico

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
024	Subconsulta Valor numerico	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Subconsulta por lista de Valores

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
025	Subconsulta por lista de Valores	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Subconsulta con EXITS

-- Subconsulta con EXITS

SELECT "NOMBRE", "APELLIDO1", "DEP"
from esquema."PERSONAS" as p
where EXISTS
(SELECT * FROM esquema."DEPARTAMENTOS" as D
WHERE D."DEP" = P."DEP")

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
026	Subconsulta con EXITS	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Bases de Datos

PostgreSQL CHECK constraint for new tables

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
	id SERIAL PRIMARY KEY,
	first_name VARCHAR (50),
	last_name VARCHAR (50),
	birth_date DATE CHECK (birth_date > '1900-01-01'),
	joined_date DATE CHECK (joined_date > birth_date),
	salary numeric CHECK(salary > 0)
);

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
027	Base de Datos	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Crear Tabla SQL

Tabla Usuarios

Create table usuarios(
	id_usuario SERIAL PRIMARY KEY,
	nombre VARCHAR(45) not NULL,
	apellido1 VARCHAR(45) not NULL,
	apellido2 VARCHAR(45) not NULL,
	contraseña VARCHAR(45) not NULL,
	Email VARCHAR(45) unique NOT NULL,
	Fecha_cracion TIMESTAMP  unique NOT NULL
)

Tabla Ocupaciones

CREATE TABLE ocupaciones(
	id_ocupaciones serial PRIMARY KEY,
	tipo_ocupacion VARCHAR(45) not NULL, 
	Descrpcion VARCHAR(100) not NULL
)

Tabla Usuario -ocupaciones (FOREING KEY)

CREATE Table usuario_ocupaciones(
	id_usuario INTEGER REFERENCES usuarios(id_usuario), -- hace referencia
	id_ocupacion INTEGER REFERENCES ocupaciones(id_ocupacion)
) 

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
028	Crear Tabla SQL	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Insert SQL

Insertar a la Tabla Usuarios

INSERT INTO usuarios(nombre, apellido1, apellido2, contraseña, email, fecha_creacion)
values('Brian', 'marquez', 'inca roca', '123', 'brian@mail.com', CURRENT_TIMESTAMP)

_Insertar a la Tabla Usuarios_

INSERT INTO usuarios(nombre, apellido1, apellido2, contraseña, email, fecha_creacion)
values('Maria', 'Isabel', 'Isabel', '456', 'maria@mail.com', CURRENT_TIMESTAMP)

Insertar a la Tabla Ocupaciones

Insert Into ocupaciones(tipo_ocupacion, descripcion)
values ('DBA', 'Reliaza Mantenimiento a la base de datos')

Insert into usuario_ocupaciones(id_usuario, id_ocupacion)
values (1,1)

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
029	Insert SQL	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

UPDATE

select * from usuarios
update usuarios set email='brianenrique@mail.com'
where id_usuario = 1

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
30	Insert SQL	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

DELETE

Insert Into ocupaciones(tipo_ocupacion, descripcion)
values ('programador', 'Reliaza Mantenimiento de lo sistemas')

Insert Into ocupaciones(tipo_ocupacion, descripcion)
values ('programador 2', 'Reliaza Mantenimiento a la base de datos 2')

/* delete */
delete from ocupaciones
where id_ocupacion = 3

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
31	DELETE	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

ALTER

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
32	ALTER	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

CASE

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
33	CASE	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

COALESCE

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
34	COALESCE	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

CAST

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
35	CAST	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Importar Ficheros

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
36	Importar Ficheros	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Exportar Ficheros

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
37	Exportar Ficheros	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

---

Ubuntu

• Install Terminal

Install Terminal 1 - Digitalocean

Install Terminal 2 - PostgreSQL

• Comandos

Crear Usuario

Para crear un usuario de PostgreSQL, utilice la siguiente instrucción SQL:

CREATE USER myuser WITH PASSWORD 'secret_passwd';

También puede crear un usuario con la siguiente instrucción SQL:

CREATE ROLE myuser WITH LOGIN PASSWORD 'secret_passwd';

Books

Server	Name	Authors	Editorial	ISBN	Link
OneDrive	Mastering PostgreSQL 13	Paul Deitel, Harvey Deitel	Packt	N 978-1-80056-749-8	Mastering PostgreSQL 13

---

Paypal Donation

🩸 Hacer una donación PAYPAL 🍵

---

A			B
C			E
F			G