DEV Community 👩‍💻👨‍💻

Cover image for PostgreSQL y no hacer DELETE sin WHERE.
Alonso Pablo
Alonso Pablo

Posted on

PostgreSQL y no hacer DELETE sin WHERE.

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

Programas utilizados: Pgadmin4 y Postgres.

CREATE DATABASE

CREATE DATABASE nombre_database;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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)
    );
Enter fullscreen mode Exit fullscreen mode

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

INSERT

INSERT INTO
    persona
VALUES
    ('3', 'Maria', '12345');
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

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

SELECT

SELECT
    *
FROM
    persona;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

--... igual al anterior
WHERE
    id > 2 AND nombre = 'Jose';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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

ALTER TABLE persona
RENAME COLUMN apellido TO nombre;
Enter fullscreen mode Exit fullscreen mode

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

ALTER TABLE persona
DROP COLUMN nombre;
Enter fullscreen mode Exit fullscreen mode

Alteramos la tabla persona y eliminamos la columna nombre

ALTER COLUMN

ALTER TABLE persona
ALTER COLUMN nombre SET NOT NULL;
Enter fullscreen mode Exit fullscreen mode

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

ALTER TABLE persona
ALTER COLUMN nombre DROP NOT NULL;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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...
    );
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Vacia la tabla persona como DELETE.

TRUNCATE TABLE persona RESTART IDENTITY;
Enter fullscreen mode Exit fullscreen mode

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'
    );
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Lista todos los registros ordenadas por nombre de forma descendente.

--... igual que el anterior.
ORDER BY
    salario ASC;
Enter fullscreen mode Exit fullscreen mode

Lista todos los registros ordenadas por salario de forma ascendente.

LIKE

SELECT
    nombre
FROM
    persona
WHERE
    nombre LIKE '%e%';
Enter fullscreen mode Exit fullscreen mode

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

--... igual que el anterior
    nombre LIKE 'e%';
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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_';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

SUM

SELECT
    SUM(salario)
FROM
    planilla;
Enter fullscreen mode Exit fullscreen mode

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

MIN MAX

-- Usando MIN
SELECT
    MIN(salario)
FROM
    plantilla;
Enter fullscreen mode Exit fullscreen mode

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

-- Usando MAX
SELECT
    MAX(salario)
FROM
    plantilla;
Enter fullscreen mode Exit fullscreen mode

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

AVG

SELECT
    AVG(salario)
FROM
    planilla;
Enter fullscreen mode Exit fullscreen mode

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

SELECT
    nombre,
    AVG(salario)
FROM
    plantilla
GROUP BY
    nombre;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

DISTINCT

SELECT
    DISTINCT nombre
FROM
    persona;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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');

Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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';

Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

WITH CHECK OPTION

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

Primero necesitamos una vista

INSERT INTO
    view_person
VALUES
    ('12345', 'Manuel', 'Vargas', 'Colombia', '43');
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Devuelve el valor absoluto del número ingresado.

CRBT

SELECT CBRT(27);
-- Salida esperada: 3
Enter fullscreen mode Exit fullscreen mode

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

CEILING

SELECT CEILING(15.33);
-- Salida esperada: 16
Enter fullscreen mode Exit fullscreen mode

Devuelve el número ingresado redondeado para arriba.

FLOOR

SELECT FLOOR(15.58);
-- Salida esperada: 15
Enter fullscreen mode Exit fullscreen mode

Devuelve el número ingresado redondeando para abajo.

POWER ROUND SIGN SQRT

POWER

SELECT POWER(2,4);
-- Salida esperada: 16
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

MOD PI RANDOM TRUNC

MOD

SELECT MOD(33,2);
-- Salida esperada: 1
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Devuelve el número Pi.

RANDOM

SELECT RANDOM();
-- Salida esperada: 0.699286726400242
-- Salida esperada: 0.6155701055762748
-- Salida esperada: 0.2833145983469656
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Retorna el número de caracteres del texto ingresado.

UPPER

SELECT UPPER('Hola mundo');
-- Salida esperada: 'HOLA MUNDO'
Enter fullscreen mode Exit fullscreen mode

Devuelve el texto ingresado pero en mayúsculas

LOWER

SELECT LOWER('Hola Mundo');
-- Salida esperada: 'hola mundo'
Enter fullscreen mode Exit fullscreen mode

Devuelve el texto ingresado pero en minúscula

POSITION

SELECT POSITION('mundo' in 'Hola mundo');
-- Salida esperada: 6
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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

SELECT TRIM(LEADING '-' FROM '--Hola Mundo---');
-- Salida esperada: 'Hola Mundo---'
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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___'
Enter fullscreen mode Exit fullscreen mode

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__'
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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-----'
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

NULL

SELECT
    *
FROM
    person
WHERE
    pais IS  NULL;
Enter fullscreen mode Exit fullscreen mode

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

SELECT
    *
FROM
    person
WHERE
    pais IS NULL;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode
DROP SEQUENCE sec_index;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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%');
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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%');
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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 $$
Enter fullscreen mode Exit fullscreen mode

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
$$
Enter fullscreen mode Exit fullscreen mode
-- 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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
-- 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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)

Update Your DEV Experience Level:

Settings

Go to your customization settings to nudge your home feed to show content more relevant to your developer experience level. 🛠