DEV Community: Alonso Pablo

PostgreSQL y no hacer DELETE sin WHERE.

Alonso Pablo — Tue, 02 Nov 2021 16:55:18 +0000

Fuente: https://www.youtube.com/watch?v=jxIEDKzGrOs&list=PL8gxzfBmzgex2nuVanqvxoTXTPovVSwi2

Programas utilizados: Pgadmin4 y Postgres.

CREATE DATABASE

CREATE DATABASE nombre_database;

Creamos una base de datos llamada: nombre_database.

-- Este es el script que se genera si lo hacemos a traves de la interfaz grafica de pgadmin4:
CREATE DATABASE nombre_database
    WITH
    OWNER = ownername
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

Creamos una base de datos llamada: nombre_database, damos el nombre del dueño de la base de datos: ownername (para este ejemplo). La codificación, en este caso es UTF8 y el límite de conexiones en -1.

DROP DATABASE IF EXISTS

DROP DATABASE IF EXISTS "nombre_database";
-- Tenemos que estar desconectados de la base de datos a borrar.

Borramos la base de datos si existe llamada: nombre_database.

CREATE TABLE

CREATE TABLE
    persona(
        id_persona int NOT NULL,
        nombre varchar(20),
        dni varchar(10)
    );

Creamos una tabla llamada persona que tiene 3 columnas: id_persona, nombre y dni.

INSERT

INSERT INTO
    persona
VALUES
    ('3', 'Maria', '12345');

Insertamos dentro de la tabla persona los valores en todas las columnas que tiene la tabla (id_persona, nombre y dni).

INSERT INTO
    persona(nombre, dni)
VALUES
    ('Maria', '12345');

Insertamos dentro de la tabla persona únicamente los valores en las columnas previamente dichas: nombre y dni.

SELECT

SELECT
    *
FROM
    persona;

Lista todos los registros con todas las columnas (ya que seleccionamos con el carácter * que refiere a todo) de la tabla persona.

WHERE AND

SELECT
    *
FROM
    persona
WHERE
    id > 2;

Lista todos los registros donde el id de la entidad sea mayor a 2.

--... igual al anterior
WHERE
    id > 2 AND nombre = 'Jose';

Lista todos los registros donde el id de la entidad sea mayor a 2 y la columna nombre sea igual a Jose.

UPDATE

UPDATE
    persona
SET
    nombre = 'Jose'
WHERE
    id = 2;

Actualiza la tabla persona actualizando, valga la redundancia, la columna nombre al valor Jose donde el id sea igual a 2.

ALTER

ALTER TABLE

ALTER TABLE persona
ADD COLUMN apellido varchar(20);
-- No se puede poner NOT NULL si ya la tabla persona contiene registros/datos agregados.

Alteramos la tabla persona y añadimos una columna apellido con límite de 20 caracteres.

ALTER TABLE persona
RENAME COLUMN apellido TO nombre;

Alteramos la tabla persona y renombramos una columna de apellido a nombre.

ALTER TABLE persona
DROP COLUMN nombre;

Alteramos la tabla persona y eliminamos la columna nombre

ALTER COLUMN

ALTER TABLE persona
ALTER COLUMN nombre SET NOT NULL;

Alteramos la columna nombre para decir que no puede tener NULL como valor.

ALTER TABLE persona
ALTER COLUMN nombre DROP NOT NULL;

Alteramos la columna nombre para borrar la restricción de la columna de no poder tener NULL como valor. Ahora acepta NULL como valor

ALTER TABLE persona
ALTER COLUMN nombre TYPE character varying;
-- No se puede poner tipos de datos que no acepten el tipo de dato al que pertenece el dato ya existente en la columna (en el caso de que dicho dato exista)

Alteramos la columna nombre para cambiar el tipo de dato aceptado a character varying.

SERIAL

CREATE TABLE
    persona(
        id serial PRIMARY KEY NOT NULL,
        -- Otras columnas...
    );

Creamos una tabla donde la columna id es de tipo serial, esto quiere decir que va a generar automáticamente e incrementándose cuando insertemos datos.

DELETE DROP TRUNCATE

DELETE

DELETE FROM persona;

Vacía la tabla persona (borra todos los registros). Cuando se agregan nuevos registros los id generados automáticamente no empiezan desde 1, sino que siguen desde el último id de la entidad borrada. ~~1, 2, 3~~; new=> 4, 5, 6.

DROP

DROP TABLE persona;

Borra la tabla persona con sus registros. Se tiene que volver a crear la tabla y los id generados automáticamente empiezan desde 1.

TRUNCATE

TRUNCATE TABLE persona;

Vacia la tabla persona como DELETE.

TRUNCATE TABLE persona RESTART IDENTITY;

Vacía la tabla persona. Cuando se agregan nuevos registros los id generados automáticamente empiezan desde 1.

VALORES POR DEFAULT

CREATE TABLE
    persona(
        --... otras columnas
        telefono varchar(10) DEFAULT 'Unknown'
    );

Creamos una tabla con una columna llamada telefono con un valor por default de 'Unknown'

COLUMNAS CALCULADAS

SELECT
    nombre,
    salario,
    (salario + 1500) AS bono
FROM
    plantilla;

Lista los registros con 3 columnas, la última es una creada a partir del salario + 1500 y es nombrada como bono

ORDER BY

SELECT
    *
FROM
    persona
ORDER BY
    nombre DESC;

Lista todos los registros ordenadas por nombre de forma descendente.

--... igual que el anterior.
ORDER BY
    salario ASC;

Lista todos los registros ordenadas por salario de forma ascendente.

LIKE

SELECT
    nombre
FROM
    persona
WHERE
    nombre LIKE '%e%';

Muestra los nombres de personas que tengan una letra e minúscula en el campo nombre.

--... igual que el anterior
    nombre LIKE 'e%';

Muestra los nombres de las personas que tengan una letra e minúscula como primera letra en el campo nombre.

--... igual que el anterior
    nombre LIKE '%E';

Muestra los nombres de las personas que tengan una letra e mayúscula como última letra en el campo nombre.

--... igual que el anterior
    nombre LIKE '%e_';

Muestra los nombres de las personas que tengan una letra e minúscula como anteúltima letra en el campo nombre.

COUNT

SELECT
    COUNT(*)
FROM
    plantilla;

Muestra una columna de la suma de los registros dentro de plantilla

SUM

SELECT
    SUM(salario)
FROM
    planilla;

Muestra en una columna la suma total de todos los valores de salario.

MIN MAX

-- Usando MIN
SELECT
    MIN(salario)
FROM
    plantilla;

Muestra en una columna el salario con el valor más bajo.

-- Usando MAX
SELECT
    MAX(salario)
FROM
    plantilla;

Muestra en una columna el salario con el valor más alto.

AVG

SELECT
    AVG(salario)
FROM
    planilla;

Saca el promedio entre todos los números de la columna.

SELECT
    nombre,
    AVG(salario)
FROM
    plantilla
GROUP BY
    nombre;

Saca el promedio dentro de cada grupo por nombre.

HAVING

Para usarlo necesitamos la cláusula GROUP BY.

WHERE trabaja con SELECT y HAVING trabaja con GROUP BY.

SELECT
    nombre, salario
FROM
    plantilla
WHERE
    nombre = 'Jose'
GROUP BY
    nombre, salario
HAVING
    salario > 3000;

Muestra los registros de nombre 'Jose' que en salario tengan más de 3000.

DISTINCT

SELECT
    DISTINCT nombre
FROM
    persona;

Lista los nombres de las diferentes registros. Si uno o muchos registros tienen el mismo nombre solo se muestra 1 vez. No muestra nombres repetidos.

BETWEEEN

SELECT
    *
WHERE
    salario
BETWEEN
    2000 AND 5000;

Lista todos los registros donde el valor de la columna salario sea entre 2000 y 5000.

-- ... igual que el anterior
NOT BETWEEN
    2000 AND 5000;

Lista todos los registros donde el valor de la columna salario no sea entre 2000 y 5000.

UNIQUE

ALTER TABLE persona
ADD CONSTRAINT uq_salario
UNIQUE(salario)

Restringe que la columna salario de la tabla persona no pueda tener valores repetidos, tienen que ser único.

DROP CONSTRAINT

ALTER TABLE persona
DROP CONSTRAINT uq_salario

Elimina la restricción uq_salario. En este ejemplo quitamos la restricción de valor único en la columna de salario

FOREIGN KEY

-- Añadimos una columna llamada codigo_empresa en la tabla 'planilla'.
ALTER TABLE planilla
ADD codigo_empresa integer

-- Añadimos una llave foranea tomando el id de la tabla 'empresa' para podes añadirla en la columna 'codigo_empresa'.
ALTER TABLE planilla
ADD CONSTRAINT test_fk
FOREIGN KEY(codigo_empresa)
REFERENCES empresa (id)

UPDATE planilla SET codigo_empresa = '2'

Creamos una llave foránea para la tabla planilla en la columna codigo_empresa, a partir de la columna id de la tabla empresa. Ahora en la columna codigo_empresa de la tabla planilla solo se pueden poner valores que tenga la columna id de la tabla empresa, de otra forma nos daría error.

FUNCTION (stored procedure)

Funcion con parametros con nombre:

-- Creamos la funcion o la reemplazamos en el caso de que exista, y la guardamos.
CREATE OR REPLACE FUNCTION
    Suma (num1 int, num2 integer)
RETURNS
    integer AS
    $$
    SELECT num1 + num2;
    $$
LANGUAGE SQL;

-- Ejecutamos la funcion guardada y retornaria una columna 'suma' con valor 200.
SELECT Suma ('50', '150');

Creamos una funcion llamada suma con dos parametros de tipo int e integer llamados num1 y num2 respectivamente.

Declaramos que retorna un integer y entre pares de símbolo $ indicamos la lógica de la función.

Luego indicamos el lenguaje utilizado.

Finalmente llamamos la funcion suma y le pasamos los dos argumentos que necesita.

Funcion con parametro sin nombre:

CREATE FUNCTION
    SearchSalary(varchar)
RETURNS
    integer AS
    $$
    SELECT salary FROM company
    WHERE name = $1
    $$
    LANGUAGE SQL

SELECT SearchSalary('Eduard')

Creamos una funcion llamada SearchSalary, decimos que va a tener un parámetro de tipo varchar y que retornara un integer.

Hacemos referencia al primer parámetro con $1 (si queremos hacer referencia al segundo parámetro, si hubiera, lo haríamos con $2 y así).

Finalmente llamamos a la función y le pasamos el nombre para que nos retorne su salario.

Funcion sin parametros y sin retornar resultado:

CREATE FUNCTION
    InsertPersons ()
RETURNS
    void AS
    $$
    INSERT INTO planilla VALUES ('Armando','3','65000');
    INSERT INTO planilla VALUES ('Esteban','4','46000');
    INSERT INTO planilla VALUES ('Quito','5','92000');
    $$
    LANGUAGE SQL

SELECT InsertPersons()

Creamos una función sin parámetros y no retorna ningún valor. Solo inserta los datos en la tabla planilla.

CREATE FUNCTION
    SearchInfo (int)
RETURNS
    planilla AS
    $$
    SELECT * FROM planilla
    WHERE id = $1
    $$
    LANGUAGE SQL

SELECT SearchInfo(1)

Creamos una función que retorna una columna que solo tiene una fila y esta contiene todas las columnas de la tabla planilla con los datos encontrados según el id que hayamos pasado a la función SearchInfo.

LIMIT (Top) OFFSET

LIMIT

SELECT
    *
FROM
    planilla
LIMIT
    5

Lista los primeros 5 o el número que pongamos después de LIMIT. Sirve para limitar el número de resultados/registros.

OFFSET

SELECT
    *
FROM
    planilla
LIMIT
    3
OFFSET
    4

A partir de la 4 fila/registro lista las 3 filas/registros siguientes.

-- 1 Pepe      < OFFSET (Ignora)
-- 2 Juan      < OFFSET (Ignora)
-- 3 Roberto   < OFFSET (Ignora)
-- 4 Julio     < OFFSET (Ignora)
-- 5 German    < LIMIT (Lista)
-- 6 Ana       < LIMIT (Lista)
-- 7 Sandra    < LIMIT (Lista)
-- 8 Eduardo
-- 9 Maria

-- OUTPUT:
    -- 5 German
    -- 6 Ana
    -- 7 Sandra

TRIGGER

-- Esta funcion se va a guardar dentro de 'Trigger Functions'
CREATE FUNCTION
    SP_Test()
RETURNS
    TRIGGER AS
    $$
    BEGIN

    -- A la hora de hacer el UPDATE
    -- 'old': Si quiero guardar la informacion que ya estan en la planilla.
    -- 'new': Si quiero guardar la informacion nueva que se ingresa a la tabla.
    INSERT INTO "Log_Triggers" VALUES (old.nombre, old.dni, old.salario);

    -- Para que el Trigger haga algo es necesario un RETURN
    RETURN NEW;

    END
    $$
    -- Indicamos que vamos a usar codigo mas de postrgreSQL
    LANGUAGE PLPGSQL;

-- Creamos el trigger, asignamos cuando se acciona y que ejecuta.
CREATE TRIGGER
    TR_Update
BEFORE UPDATE ON
    planilla
FOR EACH ROW
    EXECUTE PROCEDURE SP_Test();

-- Para probar el trigger:
UPDATE
    planilla
SET
    nombre = 'Eduardo',
    dni = '123456',
    salario = '20000'
WHERE nombre = 'Eduard';

Creamos una función la cual va a ser ejecutada por el Trigger. Esta función en el caso de usar old toma los datos que estaban en la tabla planilla antes de ser actualizados y los guarda dentro de la tabla Log_Triggers. O en el caso de usar new guarda los datos nuevos que se ingresan a la tabla.

Creamos el Trigger, le decimos que se ejecute antes de actualizar la tabla planilla y que por cada fila ejecute el procedimiento SP_Test(). En este caso es la función que creamos líneas arriba.

CREATE FUNCTION
    SP_TR_Insert()
RETURNS
    TRIGGER AS
    $$
    -- Para declarar variables usamos 'DECLARE'
    DECLARE

    -- User => es reemplazado por el nombre de usuario que esta usando la BD.
    username Varchar(250) := User;
    datenow date := current_date;
    timenow Time := current_time;

    BEGIN

    INSERT INTO
        "Log_Triggers"
    VALUES (new.nombre, new.dni, new.salario, username, datenow, timenow);

    RETURN NEW;

    END
    $$
    LANGUAGE PLPGSQL;

    CREATE TRIGGER
        TR_Insert
    AFTER INSERT ON
        planilla
    FOR EACH ROW
        EXECUTE PROCEDURE SP_TR_Insert();

Creamos una funcion llamada SP_TR_Insert. Dentro declaramos variables y guardamos el nombre del usuario que utiliza la base de datos, la fecha y la hora en la que se ejecuta la función.

En la lógica de la función hacemos que guarde los nuevos datos que se van a ingresar y las variables que declaramos líneas arriba.

Finalmente creamos el Trigger que va a activarse después de insertar en la tabla planilla. Por cada fila que ingresamos ejecuta la función SP_TR_Insert.

OR IN

OR

SELECT
    *
FROM
    planilla
WHERE
    dni = '2' OR dni = '11' OR dni = '3'

Listamos de la tabla planilla todos los registros que en la tabla dni tengan como valor 2, 11 o 3. Usando el operador lógico OR

IN

SELECT
    *
FROM
    planilla
WHERE
    dni IN ('2','11','3')

Listamos de la tabla planilla todos los registros que en la tabla dni tengan como valor 2, 11 o 3. Pero esta vez usando IN y al lado un paréntesis con el o los valores que deben tener para ser listados.

VIEW

-- Creamos una vista a partir de determinadas columnas de la tabla planilla:
CREATE VIEW
    view_dataperson
AS SELECT
    nombre,dni
FROM
    planilla;

Creamos una vista llamada view_dataperson que muestre solo las columnas que nosotros le digamos. Si se realiza un cambio en la tabla planilla también se va a cambiar en la vista view_dataperson.

-- Podemos consultar la vista como si fuera una tabla normal:
SELECT
    *
FROM
    view_dataperson

UNION

SELECT
    nombre,
    dni
FROM
    planilla
-- Si queremos no filtre los repetidos usamos 'UNION ALL' en vez de 'UNION'
UNION
SELECT
    nombre,
    id
FROM
    persona;

Lista todos los registros que haya en la tabla planilla y persona con las columnas que seleccionemos. En este caso usamos UNION por lo tanto los registros repetidos van a ser filtrados.

Si queremos que no se filtren los registros repetidos hay que agregar ALL luego de UNION quedando: UNION ALL.

SELECT
    nombre,
    dni,
    'Planilla' AS Origen
FROM
    planilla
UNION ALL
SELECT
    nombre,
    id,
    'Persona'
FROM
    persona;

Lista todos los registros (hasta los repetidos) que haya en la tabla planilla y persona con las columnas que seleccionemos. Además creamos una columna nueva llamada Origen la cual contiene el nombre de la tabla de la cual pertenece el registro 'Planilla' o 'Persona'.

CREATE VIEW
    view_union
AS SELECT
    nombre,
    dni
FROM
    planilla
UNION
SELECT
    nombre,
    id
FROM
    persona;

Por último podemos crear una vista para guardar los registros y consultarlo después sin tener que armar la unión de las dos tablas.

INNER JOIN (Simple join)

SELECT
    *
FROM
    planilla
INNER JOIN
    persona
ON
    planilla.dni = persona.id

Lista los registros que en las columnas de las tablas seleccionadas tienen el mismo valor.

En este caso hacemos un INNER JOIN entre las tablas planilla y persona donde la relación sea entre la columna dni de planilla y la columna id de persona.

( A | AB | B )

LEFT JOIN

SELECT
    *
FROM
    planilla
-- Es lo mismo escribir 'LEFT JOIN' que 'LEFT OUTER JOIN'
LEFT JOIN
    persona
ON
    planilla.dni = persona.id;

Lista todos los registro que existen en la tabla de la izquierda que en este caso seria planilla y los registros que comparten el mismo valor en la columna dni e id de la tabla planilla y persona respectivamente.

( A | AB | B )

RIGHT JOIN

SELECT
    *
FROM
    planilla
-- Es lo mismo escribir 'RIGHT JOIN' que 'RIGHT OUTER JOIN'
RIGHT JOIN
    persona
ON
    planilla.dni = persona.id;

Lista todos los registro que existen en la tabla de la derecha que en este caso seria persona y los registros que comparten el mismo valor en la columna dni e id de la tabla planilla y persona respectivamente.

( A | AB | B )

FULL JOIN

SELECT
    *
FROM
    planilla
-- Es lo mismo escribir 'FULL JOIN' que 'FULL OUTER JOIN'
FULL JOIN
    persona
ON
    planilla.dni = persona.id;

Muestra todas las columnas de la tabla planilla y persona una al lado de la otra con todos los registros de ambas tablas. Donde no haya relación entre la columna dni de la tabla planilla y la columna id de la tabla persona los campos van a ser rellenados con [null].

( A | AB | B )

CROSS JOIN

SELECT
    *
FROM
    planilla
CROSS JOIN
    persona

Lista cada registro de la tabla de la izquierda por el número de registros totales de la tabla derecha.

CROSS JOIN no utiliza ON

Ejemplo:

-- nombre dni salario id nombre     cedula
-- Jose   2   2500    20 Francisco  12345
-- Jose   2   2500    21 Daniel     21354
-- Jose   2   2500    22 Pedro      35421
-- Maria  3   4500    20 Francisco  12345
-- Maria  3   4500    21 Daniel     21354
-- Maria  3   4500    22 Pedro      35421
-- David  4   6500    20 Francisco  12345
-- David  4   6500    21 Daniel     21354
-- David  4   6500    22 Pedro      35421

WITH CHECK OPTION

CREATE VIEW
    view_person
AS SELECT
    *
FROM
    "Persona"
WHERE
    "Pais" = 'Costa Rica';

Primero necesitamos una vista

INSERT INTO
    view_person
VALUES
    ('12345', 'Manuel', 'Vargas', 'Colombia', '43');

Insertamos un registro en la vista view_person el cual el 'Pais' del registro no es 'Costa Rica' sino 'Colombia'. Esto hará que no se inserte en la vista view_person sino en la tabla "Persona" causando un registro "fantasma" y posiblemente generando errores a largo plazo.

Para evitar esto se utiliza WITH CHECK OPTION:

CREATE VIEW
    view_person
AS SELECT
    *
FROM
    "Persona"
WHERE
    "Pais" = 'Costa Rica'
WITH CHECK OPTION;

Antes de realizar un UPDATE o un INSERT primero se va a fijar en el criterio de la vista, que "Pais" solo tenga de valor 'Costa Rica'. Si cumple va a seguir con la operación, pero si no, nos va a dar un error.

ABS CBRT CEILING FLOOR

ABS

SELECT ABS(-30);
-- Salida esperada: 30

Devuelve el valor absoluto del número ingresado.

CRBT

SELECT CBRT(27);
-- Salida esperada: 3

Devuelve la raíz cubica del número ingresado.

CEILING

SELECT CEILING(15.33);
-- Salida esperada: 16

Devuelve el número ingresado redondeado para arriba.

FLOOR

SELECT FLOOR(15.58);
-- Salida esperada: 15

Devuelve el número ingresado redondeando para abajo.

POWER ROUND SIGN SQRT

POWER

SELECT POWER(2,4);
-- Salida esperada: 16

Devuelve el primer número ingresado (2) elevado a la potencia del segundo número ingresado (4).

ROUND

SELECT ROUND(24.82);
-- Salida esperada: 25

SELECT ROUND(24.824,2);
-- Salida esperada: 24.82

SELECT ROUND(24.825,2);
-- Salida esperada: 24.83

SELECT ROUND(24.8,4);
-- Salida esperada: 24.8000

Devuelve el primer número ingresado redondeado, si se ingresa un segundo número este va a determinar cuantos números decimales va a tener luego del punto.

Redondea para arriba a partir del 5.

Redondea para abajo a hasta el 4.

Si se necesitan mas decimales se le agregan 0 hasta cumplir con el segundo número ingresado.

SIGN

SELECT SIGN(0);
-- Salida esperada: 0

SELECT SIGN(234);
-- Salida esperada: 1

SELECT SIGN(-234);
--  Salida esperada: -1

Devuelve 1 si el número ingresado es positivo.

Devuelve 0 si el número ingresado es 0.

Devuelve -1 si el número ingresado es negativo.

SQRT

SELECT SQRT(9);
-- Salida esperada: -1

Devuelve la raíz cuadrada del número ingresado.

MOD PI RANDOM TRUNC

MOD

SELECT MOD(33,2);
-- Salida esperada: 1

Devuelve el resto de la división del primer número ingresado con respecto al segundo número ingresado.

PI

SELECT PI();
-- Salida esperada: 3.141592653589793

Devuelve el número Pi.

RANDOM

SELECT RANDOM();
-- Salida esperada: 0.699286726400242
-- Salida esperada: 0.6155701055762748
-- Salida esperada: 0.2833145983469656

Devuelve un número aleatorio entre 0 y 1.

TRUNC

SELECT TRUNC(-57.35)
-- Salida esperada: -57

SELECT TRUNC(-57.35736434,2)
-- Salida esperada: -57.35

Si no se ingresa un segundo número, se retorna el primer número ingresado sin decimal.

Si se ingresa un segundo número, se retorna el primer número ingresado con tantos decimales según el segundo número ingresado.

CHAR_LENGTH UPPER LOWER POSITION

CHAR_LENGTH

SELECT CHAR_LENGTH('Hola mundo');
-- Salida esperada: 10

Retorna el número de caracteres del texto ingresado.

UPPER

SELECT UPPER('Hola mundo');
-- Salida esperada: 'HOLA MUNDO'

Devuelve el texto ingresado pero en mayúsculas

LOWER

SELECT LOWER('Hola Mundo');
-- Salida esperada: 'hola mundo'

Devuelve el texto ingresado pero en minúscula

POSITION

SELECT POSITION('mundo' in 'Hola mundo');
-- Salida esperada: 6

Devuelve la posición del primer texto ingresado dentro del segundo texto ingresado.

Si no encuentra la palabra devuelve 0.

SUBSTRING TRIM

SUBSTRING

SELECT SUBSTRING('Hello World' FROM 2 FOR 6);
-- Salida esperada: 'ello W'

Desde la posición 2 (empieza a contar desde el 1) toma los caracteres hasta la posición 6 y lo retorna.

Sirve para extraer una sección del texto pasándole dos números refiriendo a la posición de inicio y fin de la extracción.

TRIM

SELECT TRIM('  Hola Mundo  ');
-- Salida esperada: 'Hola Mundo'

Retorna el texto pero sin espacios al principio ni al final.

SELECT TRIM(LEADING '-' FROM '--Hola Mundo---');
-- Salida esperada: 'Hola Mundo---'

Retorna el texto, pero sin el carácter escogido al principio del texto, si hay un carácter al final este va a seguir estando.

SELECT TRIM(TRAILING '-' FROM '--Hola Mundo---');
-- Salida esperada: '--Hola Mundo'

Retorna el texto, pero sin el carácter escogido al final del texto, si hay un carácter al principio este va a seguir estando.

SELECT TRIMG(BOTH '-' FROM '--Hola Mundo---');
-- Salida esperada: 'Hola Mundo'

Retorna el texto pero sin el carácter escogido en ambos lados, al principio y al final.

LTRIM RTRIM SUBSTR LPAD RPAD

LTRIM

SELECT LTRIM('   Hola Mundo');
-- Salida esperada: 'Hola Mundo'

SELECT LTRIM('___Hola Mundo', '_');
-- Salida esperada: 'Hola Mundo'

Retorna el texto, pero sin espacios a la izquierda y si queremos remover un carácter en específico a la izquierda del texto lo hacemos pasándole el carácter luego del texto separado con una coma.

RTRIM

SELECT LTRIM('Hola Mundo   ');
-- Salida esperada: 'Hola Mundo'

SELECT LTRIM('Hola Mundo___', '_');
-- Salida esperada: 'Hola Mundo'

Retorna el texto, pero sin espacios a la derecha y si queremos remover un carácter en específico a la derecha del texto lo hacemos pasándole el carácter luego del texto separado con una coma.

SUBSTR

SELECT SUBSTR('Hola Mundo___', 5);
-- Salida esperada: ' Mundo___'

Retorna el texto a partir de la posición que le digamos, en este caso a partir de la posición 5.

SELECT SUBSTR('Hola Mundo___', 5, 8);
-- Salida esperada: ' Mundo__'

Retorna el texto a partir de la posicion que le digamos, en este caso a partir de la posicion 5. Y hasta la posicion del segundo numero que le pasemos, en este caso 8.

LPAD

SELECT LPAD('Hola Mundo',15,'-');
-- Salida esperada: '-----Hola Mundo'

Retorna el texto, pero le agrega a la izquierda (al principio) del mismo el carácter que le indiquemos.

Siempre se va a detener cuando el largo o el número de caracteres sea el número que le pasemos o en este caso el 15.

RPAD

SELECT RPAD('Hola Mundo',15,'-');
-- Salida esperada: 'Hola Mundo-----'

Retorna el texto, pero le agrega al la derecha (al final) del mismo el carácter que le indiquemos.

Siempre se va a detener cuando el largo o el número de caracteres sea el número que le pasemos o en este caso el 15.

DATE TIME TIMESTAMP EXTRACT()

DATE

SELECT CURRENT_DATE;
-- Salida esperada: 2021-11-01

Retorna la fecha en la que se corre esta consulta.

Año-Mes-Día

TIME

SELECT CURRENT_TIME;
-- Salida esperada: 02:52:07.291573+00:00

Retorna la hora en la que se corre esta consulta.

Hora:Minutos:Segundos:Milisegundos+ZonaHoraria

TIMESTAMP

SELECT CURRENT_TIMESTAMP;
-- Salida esperada: 2021-11-01 02:54:04.825941+00

Retorna la fecha y hora en la que se corre esta consulta.

Año-Mes-Día Hora:Minutos:Segundos:Milisegundos+ZonaHoraria

EXTRACT()

-- Retorna en que año estamos:
SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP);
-- Salida esperada: 2021

-- Retorna en que mes estamos:
SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP);
-- Salida esperada: 11

-- Retorna en que dia estamos:
SELECT EXTRACT(DAY FROM CURRENT_TIMESTAMP);
-- Salida esperada: 1

-- Retorna la hora:
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP);
-- Salida esperada: 3

-- Retorna en que siglo estamos:
SELECT EXTRACT(CENTURY FROM CURRENT_TIMESTAMP);
-- Salida esperada: 21

-- Retorna en que parte del cuarto del año estamos:
SELECT EXTRACT(QUARTER FROM CURRENT_TIMESTAMP);
-- Salida esperada: 4

-- Retorna en que numero de dia de la semana estamos:
SELECT EXTRACT(DOW FROM CURRENT_TIMESTAMP);
-- Salida esperada: 1

-- Retorna el numero de dias que pasaron desde principio del año:
SELECT EXTRACT(DOY FROM CURRENT_TIMESTAMP);
-- Salida esperada: 305

-- Retorna el numero de meses que pasaron desde principio del año:
SELECT EXTRACT(WEEK FROM CURRENT_TIMESTAMP);
-- Salida esperada: 44

NULL

SELECT
    *
FROM
    person
WHERE
    pais IS  NULL;

Retorna todos los registros donde la columna pais no tenga el valor NULL

SELECT
    *
FROM
    person
WHERE
    pais IS NULL;

Retorna todos los registros donde la columna pais tenga como valor NULL

SEQUENCE

-- Creamos la secuencia
CREATE SEQUENCE
    sec_index
START WITH
    1
INCREMENT BY
    20
MINVALUE
    1
MAXVALUE
    100
CYCLE;

Si no ponemos valores MINVALUE Y MAXVALUE se setean los de por defecto: -9223372036854775808 y 9223372036854775807.

Si no queremos que sea CYCLE agregamos un NO al inicio: NO CYCLE. O simplemente lo quitamos.

-- Verificamos la secuencia creada
SELECT * FROM "sec_index";

-- Salida esperada:
-- sequence_name last_value start_value increment_by max_value min_value cache_value >>
-- sec_index     1          1           20           100       1         1
-- >> log_cnt is_cycled is_called
--    0       true      false

-- A mi me salio:
-- last_value log_cnt is_called
-- 1          0       false

En last_value indica cuál es el número actual.

En is_called indica si la secuencia fue iniciada/llamada.

-- Para iniciar/llamar la secuencia:
SELECT NEXTVAL('sec_index');
-- Salida esperada: 1
-- Salida esperada: 21
-- Salida esperada: 41
-- Salida esperada: 61
-- Salida esperada: 81
-- Salida esperada: 1
-- Salida esperada: 21
-- ... ya que es un ciclo vuelve a reiniciarse.
-- Si no es 'CYCLE' nos devolveria un error al querer superar el valor maximo.

DROP SEQUENCE sec_index;

Eliminamos la secuencia sec_index.

SUBCONSULTA

DENTRO DE SELECT

SELECT
    nombre,
    apellido,
    pais_residencia,
    (
        SELECT
            MAX(precio) AS precio_max
        FROM
            precios
        WHERE
            pais = pais_residencia
    )
FROM
    persona;

Lista las columnas nombre, apellido, pais_residencia y precio_max. En este último tenemos el precio máximo de la tabla precios coincidiendo el valor de la columna pais_residencia de la tabla persona con el valor de la columna pais de la tabla precios.

DENTRO DE WHERE

SELECT
    nombre,
    apellido
    pais_residencia
FROM
    persona
WHERE
    pais_residencia = (SELECT pais FROM precios LIMIT 1);

Lista los registros si coincide que algún registro de la tabla persona tenga en la columna pais_residencia el mismo valor que la subconsulta, del valor de la columna pais del primer registro de la tabla precios.

DENTRO DE WHERE IN

SELECT
    nombre,
    apellido,
    pais_residencia
FROM
    persona
WHERE
    pais IN (SELECT pais FROM precios WHERE pais LIKE '%C%');

Lista los registros que coinciden que la columna pais de la tabla persona tengan el mismo valor que los resultados de la subconsulta de países que tengan C mayuscula en la columna pais de la tabla pais.

Ejemplo:

-- nombre apellido pais_residencia
-- Alex   Chacon   Colombia
-- Maria  Solis    Costa Rica
-- Ana   Alfaro   Costa Rica
-- Otto   Solano   Colombia

DELETE UPDATE CON SUBCONSULTA

UPDATE

UPDATE
    persona
SET
    pais_residencia = (SELECT pais FROM precios ORDER BY precio LIMIT 1 OFFSET 10)
WHERE
    pais_residencia IS NULL;

Actualiza los campos que tengan NULL de la columna pais_residencia de la tabla persona con los datos de la subconsulta sacado de la columna pais de la tabla precios ignorando los primeros 10 registros y solo trayendo 1 registro.

DELETE

DELETE FROM
    persona
WHERE pais_residencia IN (SELECT pais FROM precios WHERE pais LIKE '%er%');

Borra los registros de la tabla persona donde los registros tengan en la columna pais_residencia el valor de los resultados de la subconsulta. La subconsulta trae la columna pais de la tabla precios donde en su valor contenga: er.

INSERT CON SUBCONSULTA

-- Existe una tabla llamada "precios"
-- Existe una tabla vacia llamada "precios_maximos"

INSERT INTO
    precios_maximos
SELECT
    pais, max(precio)
FROM
    precios
GROUP BY
    pais

Insertamos en la tabla vacía de precios_maximos los resultados de la subconsulta, la misma trae el precio maximo de cada pais sin repetir el valor de la columna pais de la tabla precios.

VARIABLES

-- NO SE PUEDE DECLARAR VARIABLES FUERA DE UN BLOQUE.
DECLARE @variable int;
SET @variable = 50;

SELECT @variable;
-- Nos da error.

Retornaría un error, ya que las variables tienen que declararse dentro de una estructura de bloque.

DO $$
DECLARE x int := 50;
        y int := 500;
        z int;
BEGIN

z := x * y;

-- Para que retorne el valor de z utilizamos '%':
RAISE NOTICE '%',z;

END
$$;
-- Salida esperada: NOTICE: 2500
-- DO

IF

DO $$
BEGIN

IF EXISTS(SELECT pais FROM precios WHERE pais = 'Iraq') THEN

RAISE NOTICE 'El pais SI ha sido encontrado';

ELSE

RAISE NOTICE 'El pais NO ha sido encontrado';

END IF;

END $$

Retorna 'El país SI ha sido encontrado' o 'El país NO ha sido encontrado' según si la condición dentro de los paréntesis de EXISTS es true o falsa.

WHILE

DO
$$
DECLARE x int := (SELECT COUNT(id) FROM precios) -- x = 396
        y int := 0;
BEGIN

WHILE (y < x)
LOOP
RAISE NOTICE 'Vuelta # %', y;
y := y + 1;
END LOOP;

END
$$

-- Salida esperada:
-- NOTICE:  Vuelta # 0
-- NOTICE:  Vuelta # 1
-- NOTICE:  Vuelta # 2
-- NOTICE:  Vuelta # 3
-- NOTICE:  Vuelta # 4
-- NOTICE:  Vuelta # 5
-- NOTICE:  Vuelta # 6
-- NOTICE:  Vuelta # 7
-- NOTICE:  Vuelta # 8
-- NOTICE:  Vuelta # 9
-- NOTICE:  Vuelta # 10
-- ...
-- NOTICE:  Vuelta # 393
-- NOTICE:  Vuelta # 394
-- NOTICE:  Vuelta # 395
-- DO

CASE

SELECT
    pais,
    precio,
    CASE WHEN pais = 'Spain' THEN 'Vuelo con Escalas'
         WHEN pais = 'Turkey' THEN 'Vuelo Retrasado'
         ELSE 'Vuelo Normal'
    END AS travel_status
FROM
    precios;

-- Salida esperada:
-- pais     precio   travel_status
-- Turkey   $89.28   Vuelo con Escalas
-- Spain    $9.87    Vuelo Retrasado
-- Brazil   $95.24   Vuelo Normal
-- Italy    $12.22   Vuelo Normal

EXPORTAR IMPORTAR BASE DE DATOS PGADMIN4

EXPORTAR

Click derecho en la base de datos > Backup... > Elegimos una dirección donde se guardara el backup > Role name: postgres > Y para terminar le damos al botón: Backup

IMPORTAR

Click derecho en Databases > Create > Database... > Le damos un nombre > Finalizamos la creación con Save .

Click izquierdo en la nueva base de datos que creamos y luego click derecho > Restore... > En la fila de Filename le damos a los tres puntitos (...) para elegir el archivo que queremos importar > Para poder ver el archivo hay que cambiar el Format a sql > Seleccionamos el archivo de extensión .sql > Seleccionamos en Role name: postgres > Y para terminar le damos al botón: Restore.

EXPORTAR IMPORTAR UNA TABLA A CSV

EXPORTAR

Click derecho a la tabla > Import/Export... > Le damos click en los tres puntitos (...) para ubicar donde se va a guardar el archivo exportado > Format: csv > Header: Yes > Delimiter: , > Para finalizar le damos al botón OK.

IMPORTAR

Click derecho a la tabla > Import/Export... > Cambiamos a importar en Import/Export: Import > Seleccionamos el archivo con los tres puntitos (...) > Format: csv > Delimiter: , > Para finalizar le damos al botón OK.

CURSOR

Es una variable donde vamos a guardar el contenido de una registro resultado de una consulta

DO
$$
DECLARE
    registro Record;
    cur_precios CURSOR FOR SELECT * FROM precios ORDER BY pais;
BEGIN

OPEN cur_precios;
FETCH cur_precios INTO registro;

RAISE NOTICE 'Pais: % , Precio: %', registro.pais, registro.precio;

END
$$
LANGUAGE PLPGSQL;

Creamos un cursor llamado cur_precios que consulta las columnas de la tabla precios ordenados por la columna pais.

Abrimos el cursor y luego hacemos un FETCH para insertar un registro de cur_precios dentro de la variable registro.

Por último damos una noticia de cuanto vale registro.pais y registro.precio cada vez que ejecutemos este script.

Al ejecutar el script va a insertar un registro a la tabla registro, hasta que el cursor no tenga registros a que apuntar en cur_precios.

Cabe destacar que el FETCH va haciendo que el CURSOR vaya apuntando al siguiente registro dentro de cur_precios. Si hacemos dos FETCH seguidos va a correr el primer FETCH, se guarda un registro dentro de registro y luego va a correr el segundo FETCH con el registro que le sigue al primero dentro de cur_precios para guardarlo dentro de registro.

CURSOR CON WHILE

DO
$$
DECLARE
    registro Record;
    cur_precios CURSOR FOR SELECT * FROM precios ORDER BY pais;
BEGIN

OPEN cur_precios;

-- Mientras que encuentre registros el WHILE va a seguir funcionando.
-- Cuando no encuentre registros el WHILE va a terminar.
WHILE (FOUND) LOOP
FETCH cur_precios INTO registro;
END LOOP;

RAISE NOTICE 'Pais: % , Precio: %', registro.pais, registro.precio;

END
$$
LANGUAGE PLPGSQL;

El script esta vez toma todos los registros dentro del cursor cur_precios y los inserta dentro de la tabla registro.
El script se detiene solo, ya que el WHILE se va a detener cuando el cursor no tenga registros a que apuntar dentro de cur_precios.

CURSOR CON FOR

DO
$$
DECLARE
    registro Record;
    cur_precios CURSOR FOR SELECT * FROM precios ORDER BY pais;
BEGIN

-- Por cada registro dentro de 'cur_precios' lo guarde dentro de 'registro'
FOR registro IN cur_precios LOOP
RAISE NOTICE 'Pais: % , Precio: %', registro.pais, registro.precio;
END LOOP;

END
$$
LANGUAGE PLPGSQL;

Con FOR no hace falta abrir el cursor. Hace lo mismo que el WHILE pero en forma más resumida.

Vim y no morir en el intento

Alonso Pablo — Wed, 20 Oct 2021 15:26:57 +0000

Modo: NORMAL 🕹

ESC: Sale del modo 'Insert'/'Inserción' para introducir comandos
.: Repite el último comando ingresado

💾 Archivo:

:w: Guarda el archivo (no cierra)
:wq!: Guarda y cierra el archivo
:x!: Guarda y cierra el archivo
:q!: Cierra el archivo sin preguntar

♻ Movimientos:

h: Mueve el cursor un carácter a la izquierda
l: Mueve el cursor un carácter a la derecha
j: Mueve el cursor a la línea inferior/abajo
k: Mueve el cursor a la línea superior/arriba
Shift + h: Mueve el cursor a la primera línea que se ve en la pantalla
Shift + l: Mueve el cursor a la última línea que se ve en la pantalla

w: El cursor avanza hacia la primera letra de la siguiente 'palabra'
b: El cursor retrocede y se sitúa en la primera letra de la palabra anterior
o: El cursor se sitúa en la línea siguiente/inferior a donde esté y entramos en modo Inserción/Insert
Shift + o: El cursor se sitúa en la línea anterior/superior a donde esté

🏗 Edición:

dd: Cortamos la línea completa donde se encuentra el cursor
dw: Cortamos la palabra desde donde se encuentra el cursor
dl: cortamos la siguiente letra desde donde se encuentra el cursor
yy: Copiamos la línea completa donde se encuentra el cursor
d<número>d || d<número>w || y<número>y: Copiamos o Cortamos la cantidad de líneas que pongamos entre las letras. Ejemplo: d5d, d7w o y6y
p: Pegamos lo que hayamos 'copiado' o 'cortado' 1 carácter después de donde se encuentra el cursor
r: Entra en modo inserción/reemplazar para que reemplacemos la letra donde se encuentra el cursor y luego vuelve al modo 'Normal' (salimos con la tecla: 'ESC')
Shift + r: Entramos en modo inserción/reemplazar para que reemplacemos desde la letra donde se encuentra el cursor (salimos con la tecla: 'ESC')
x: Borra el carácter anterior a donde se encuentra el cursor
Shift + x: Borra el carácter siguiente a donde se encuentra el cursor

u: Deshacemos los últimos cambios
Ctrl + r: Rehacemos los últimos cambios

Modo: INSERCIÓN 📝

i || Insert: Entramos en modo de inserción y podemos insertar texto en donde se encontraba el cursor
Shift + i: Entramos en modo de inserción y podemos insertar texto al inicio de la línea donde se encontraba el cursor

Modo: VISUAL 📑

v: Entramos en modo visual, para seleccionar texto
gg: Mueve el cursor al principio del archivo
Shift + g: Mueve el cursor al final del archivo
> || Shift + .: Indenta las líneas previamente seleccionadas.
d: Cortamos el texto seleccionado
y: Copiamos el texto seleccionado
p: Pegamos lo que hayamos 'copiado' o 'cortado' 1 carácter después de donde se encuentra el cursor
/<texto>: Busca el introducido

n: Mueve el cursor a la próxima ocurrencia encontrada en el archivo
Shift + n: Mueve el cursor a la anterior ocurrencia

Tmux

Alonso Pablo — Wed, 20 Oct 2021 15:25:12 +0000

Antes que nada necesitamos instalar y luego abrir Tmux:

sudo apt-get install tmux
tmux

"Atajo principal"

Ctrl + b: Activamos por unos segundos la escucha de Tmux de comandos/atajos. Inmediatamente podemos intentar ejecutar los siguientes "sub-atajos":

Shift + .: (esto es para teclado inglés o para otros teclados la combinación de teclas tiene que resultar en un: >) Este comando nos permite abrir un menú con opciones como:

Horizontal Split (h)
Vertical Split (v)
---
Swap Up (u)
Swap Down (d)
Swap Marked
---
Kill (x)
Respawn (r)
Mark (m)
Zoom (z)

Arrow left: Para ir a la consola de la izquierda.
Arrow right: Para ir a la consola de la derecha.
Arrow up: Para ir a la consola de arriba.
Arrow down: Para ir a la consola de abajo.
x: Cerramos la consola en la que esté el cursor, nos pedirá confirmación y tendremos que apretar y.
c: Abrimos una nueva consola tmux ocupando todo espacio de la Shell.
Para movernos entre ventanas abiertas podemos utilizar:
-n: Nos movemos a la siguiente ventana.
-p: Nos movemos a la anterior ventana.

Cambiar el atajo principal:

Si queremos cambiar el atajo principal (Ctrl + b) por otro tendremos que ir a:
/etc/tmux.conf: Para que el comando se aplique a todos los usuarios del sistema.
~/.tmux.conf: Para que el comando se aplique a algunos usuarios.

Si no existen los archivos hay que crearlos.

En el siguiente ejemplo reemplazo Ctrl+b por Ctrl+a:

unbind C-b
set -g prefix C-a
bind C-a send-prefix