Module S9
Dalibo SCOP
24.04
17 avril 2024
Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :
PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.
Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.
PostgreSQL offre des types avancés :
hstore
json
jsonb
bytea
Stocker des données non structurées
CREATE EXTENSION hstore ; CREATE TABLE animaux (nom text, caract hstore); INSERT INTO animaux VALUES ('canari','pattes=>2,vole=>oui'); INSERT INTO animaux VALUES ('loup','pattes=>4,carnivore=>oui'); INSERT INTO animaux VALUES ('carpe','eau=>douce'); CREATE INDEX idx_animaux_donnees ON animaux USING gist (caract);
SELECT *, caract->'pattes' AS nb_pattes FROM animaux WHERE caract@>'carnivore=>oui';
nom | caract | nb_pattes ------+-----------------------------------+----------- loup | "pattes"=>"4", "carnivore"=>"oui" | 4
{ "firstName": "Jean", "lastName": "Dupont", "age": 27, "address": { "streetAddress": "43 rue du Faubourg Montmartre", "city": "Paris", "postalCode": "75009" } }
jsonpath
SELECT '{"auteur": "JRR Tolkien","titre":"Le Hobbit"}' IS JSON ; -- true
SELECT '{"auteur": "JRR Tolkien","titre":"Le Hobbit}' IS JSON ; -- false
IS JSON WITH/WITHOUT UNIQUE KEYS
IS JSON ARRAY
IS JSON OBJECT
IS JSON SCALAR
CREATE TABLE personnes (datas jsonb ); INSERT INTO personnes (datas) VALUES (' { "firstName": "Jean", "lastName": "Valjean", "address": { "streetAddress": "43 rue du Faubourg Montmartre", "city": "Paris", "postalCode": "75002" }, "phoneNumbers": [ { "number": "06 12 34 56 78" }, {"type": "bureau", "number": "07 89 10 11 12"} ], "children": [], "spouse": null } '), (' { "firstName": "Georges", "lastName": "Durand", "address": { "streetAddress": "27 rue des Moulins", "city": "Châteauneuf", "postalCode": "45990" }, "phoneNumbers": [ { "number": "06 21 34 56 78" }, {"type": "bureau", "number": "07 98 10 11 12"} ], "children": [], "spouse": null } ');
SELECT datas->>'firstName' AS prenom, -- text datas->'address' AS addr -- jsonb FROM personnes ;
SELECT datas #>> '{address,city}' AS villes FROM personnes ; -- text
SELECT datas['address']['city'] as villes from personnes ; -- jsonb, v14
SELECT datas['address']->>'city' as villes from personnes ; -- text, v14
SELECT jsonb_array_elements (datas->'phoneNumbers')->>'number' FROM personnes;
json_object_agg()
jsonb_each()
jsonb_to_record()
jsonb_array_elements()
jsonb_to_recordset()
Inconvénients par rapport à un modèle normalisé :
CREATE INDEX idx_prs_nom ON personnes ((datas->>'lastName')) ; ANALYZE personnes ;
ALTER TABLE personnes ADD COLUMN lastname text GENERATED ALWAYS AS ((datas->>'lastName')) STORED ;
CREATE INDEX idx_prs ON personnes USING gin(datas jsonb_path_ops) ;
SELECT jsonb_path_query (datas, '$.phoneNumbers[*] ? (@.type == "bureau") ') FROM personnes ;
xml
XMLPARSE
XMLSERIALIZE
query_to_xml
xmlagg
xpath
Souvent une mauvaise idée…
2 méthodes
bytea_output
hex
escape
SELECT pg_read_binary_file ('/chemin/fichier');
lo_unlink
vacuumlo
lo_create
lo_import
lo_seek
lo_open
lo_read
lo_write
https://dali.bo/s9_quiz