Capítulo 10 PostgreSQL

10.1 Antecedentes

Historia y evolución de PostgreSQL…¹²

10.2 Instalación

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

sudo passwd postgres

su - postgres
createdb testdb
psql testdb

10.3 Implementación en Cloud Computing

10.4 PostGis

10.5 Privilegios

ALTER TABLE table_name OWNER TO new_owner;

10.6 JSON & JSONB

Implementación en escenarios relacional y no relacional…

10.6.1 SELECT

SELECT value as forma 
FROM (SELECT * 
FROM forms f, jsonb_array_elements(f.form) obj 
WHERE  obj->>'report' = 'TRUE') test;

agrega columnas desde dos tablas en un campo jsonb común

SELECT s.id::TEXT ,
jsonb_set(s.data, '{info}',  jsonb_build_object('pi',p.data->'pi','email',p.data->'email',
'contacto','','emailcontacto',''),true) AS data
FROM solicitudes s JOIN proyectos p ON s.data->>'proyecto'= p.id::TEXT

verificar si columna existe

SELECT * FROM cards WHERE data ? 'direction';
SELECT * FROM atencion WHERE data->>'atidm'='9876872' AND data ? 'atencion'
SELECT count(*) FROM neuro WHERE data @> '{ "examen" : "token" }’;

cuantos pacientes tienen correo registrado

select count(*) FROM pacientes WHERE data ? 'email';

si un array contiene cierto valor

SELECT count(*) from pacientes WHERE data->’tags’ ? ‘uno’;

si pertenece a un proyecto determinado dentro de un array de proyectos

WHERE data->'proyectos' @> '[{\"proyecto\":\"$_REQUEST[idp]\"}]'

select los nombres de los grupos en los que esta inscrito un usuario basado en un array

SELECT g.id AS gid, g.data->>'nombre' AS nombre from grupos g
WHERE g.id::TEXT in (SELECT
jsonb_array_elements_text(data->'grupo') as tag
FROM usuarios u
WHERE u.data->>'rut' = ‘9999999’) 
ORDER BY nombre

seleccionar los usuarios de un grupo (gid)

SELECT id, concat(data->>'apellido'::TEXT ,' ' ,data->>'nombre'::TEXT) AS nombre 
FROM usuarios
WHERE data->>'gid'::TEXT = '1c1850ae-8774-48c1-bfa2-5a8dc5c545b5'

10.6.2 UPDATE

UPDATE informes set data=jsonb_set(data, '{camino}', '"teatro"', true) 
WHERE id='a'

SET data = data::jsonb - 'a' || '{"a":"5","macro":"text macro","micro":"text micro"}'::jsonb
WHERE id='e1046274-7003-4d48-b9de-ed47e7ed5d0a'

SET data = data::jsonb - 'a' || '{"a":"9099","rol":["PAT","MED","SEC"],
"conclusion":"no hay aun","macro":"textual","micro":"text micro"}'::jsonb
WHERE id='e1046274-7003-4d48-b9de-ed47e7ed5d0a'

actualiza o agrega rut

UPDATE usuarios SET data=jsonb_set(data, '{rut}', '"555555555"', true)

agrega “rol”:[”USR”]

UPDATE usuarios SET data=jsonb_set(data,'{rol}','["USR"]',true)

cambia el nombre de una etiqueta (“id” por “rut”)

UPDATE usuarios SET data = replace(data::TEXT,'"id"','"rut"')::jsonb

Agrega un atributo “grupo”

UPDATE pacientes SET data=jsonb_set(data,'{grupo}','"ASC"') ;

agrega valor a un array

UPDATE usuarios 
SET data=jsonb_set(data,'{rol,9}’, ’”MED"'::jsonb, true)

elimina un tag con “#-”

UPDATE comunas SET data = data::jsonb #- '{idcomuna}'

cambia el valor de un tag

UPDATE comunas SET data = jsonb_set(data::jsonb, '{idcomuna}', '"codigoco"');

actualiza el nombre de un tag

UPDATE comunas SET data = replace(data::TEXT,'"idcomuna"','"codigocomuna"')::jsonb

actualiza varios campos a la vez

UPDATE tabla SET data=data || '{"can_rate":false,"num_votes":0,"revote":true }';

UPDATE pacientes SET data=data || '{
"er":{"estado":"positivo","grado":"3+","pcje": "90"},
"pr":{"estado":"positivo","grado":"3+","pcje": "90"}, "her2":{"estado":"negativo","grado":"0"}, 
"ki67":{"estado":"positivo","grado":""}
}' 
WHERE id = '091f5a0f-8921-408d-b046-720ae543a072';

10.6.3 JOIN

SELECT data->>'id' AS id, data->>'nombre' as nombre, 
data->>'direccion' AS direccion, comuna 
FROM establecimientos e
LEFT JOIN LATERAL (SELECT c.data->>'comuna' AS comuna 
FROM comunas c 
WHERE data->>'idcomuna' = e.data->>'comuna') c ON true 
WHERE upper(data->>'nombre' ) LIKE '%LOTA%’

unir 3 tablas

SELECT id::TEXT, pgdate(data->>'inicio') AS fecha, proyecto, servicio 
FROM servicioschg c 
LEFT JOIN LATERAL (SELECT p.data->>'nombre' AS proyecto 
FROM proyectos p 
WHERE id::TEXT = c.data->>'proyecto') p ON true 
LEFT JOIN LATERAL (SELECT s.data->>'nombre' AS servicio 
FROM servicios s
WHERE id::TEXT = c.data->>'servicio') s ON true

10.6.4 INDEX

GIN

-- genera un indice basado en todo el campo jsonb (data)
CREATE INDEX nombre ON table USING gin (data)

Es más eficiente utilizar basado en un tag dentro de Jsonb

CREATE index grupos_idx ON grupos using gin ((data->'id'))
-- lo que permite buscar por 'id' mas eficientemente
SELECT * from grupos where data @> '{"id":78516620}’

ALTER TABLE ordenes 
ADD CONSTRAINT cotizacion_null 
CHECK ((data->>'cotizacion') IS NOT NULL);

-- crea indice de tipo unico basado en 'a'
create unique index example_uq_a on example
((data->’a’))
WHERE (data->’a’) not null;

-- crea un indice único basado en 'rut'
CREATE UNIQUE INDEX medicos_id ON medicos ((json->>’rut’))

https://www.postgresql.org/docs/13/history.html ↩︎