/PostgreSQL-Training

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

OtherNOASSERTION

🐘 PostgreSQL Training

Stars Forks

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 Linux How to install and use PostgreSQL on Ubuntu
⚙️ Install PostgreSQL Windows Download the installer for windows

Correccion del Error PATH

Cambiar la Direccion del PATH de PostgreSQL

Linux installation - Ubuntu (Intallation/Comandos)

Install Postgres

Delete Postgres

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: ...

Inicializar Postgres - Ubuntu terminal

 service postgresql start
 service postgresql status
 service postgresql stop

Usar Postgres

Cambie a la cuenta de postgres

sudo -i -u postgres

Acceder de inmediato a una línea de comandos de PostgresSQL

psql

ver base de datos Acceder de inmediato a una línea de comandos de PostgresSQL

\l

entrar a la base de datos Acceder de inmediato a una línea de comandos de PostgresSQL

\dt <nasededatos>

Create Postgres User

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

Ver Usuarios

SELECT usename FROM pg_user;

Rename a database

ALTER DATABASE olddbname RENAME TO newdbname;

Delete user postgres

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