Dan Levy

Quiz : Postgres en profondeur, partie 1

SQL vous fait-il pousser un cri ?

Partie 1 sur 2. Passer à la partie 2

PostgreSQL 🐘 est sans conteste ma base de données préférée ! J’apprends constamment de nouvelles astuces et pièges, alors j’ai décidé d’en faire le sujet d’un nouveau quiz !

Ce quiz mélange des fonctionnalités PostgreSQL bien connues et des subtilités moins répandues : agrégats intégrés, cast de types, contraintes, et bien plus encore.

Bonne chance ! 🍀

1. 

Échauffement : Fonctions

Quelle fonction n’est PAS une fonction d’agrégat intégrée dans PostgreSQL ?

SELECT
  MIN(grade) as lowest,
  MAX(grade) as highest,
  AVG(grade) as average,
  MEDIAN(grade) as middle
FROM grades;

MEDIAN n’est pas intégré ! Vous avez besoin :

PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY grade)

Agrégats intégrés courants :

  • MIN, MAX, COUNT
  • AVG, SUM
  • ARRAY_AGG, STRING_AGG
  • Diverses fonctions statistiques

2. 

Échauffement : Conversion de type

Laquelle de ces conversions de type est non valide ❌ ?

PostgreSQL prend en charge trois syntaxes de cast :

  1. ANSI SQL : CAST(expression AS type).
  2. PostgreSQL : expression::type.
  3. Littéral typé : type 'literal'.

Toutes sont fonctionnellement équivalentes, mais :

  • CAST() est la plus portable.
  • :: est spécifique à PostgreSQL mais couramment utilisé.
  • Le style infixe type 'literal' est moins répandu mais reste valide.

3. 

Contraintes

Combien de valeurs NULL sont autorisées ici ?

CREATE TABLE student_emails (
  student_id INTEGER,
  email VARCHAR(255),
  UNIQUE(email)
);

Contraintes UNIQUE dans PostgreSQL :

  • Autorisent plusieurs valeurs NULL.
  • NULL ≠ NULL dans les vérifications d’unicité.

Pour empêcher les valeurs NULL, ajoutez NOT NULL :

CREATE TABLE student_emails (
  student_id INTEGER,
  email VARCHAR(255) NOT NULL,
  UNIQUE(email)
);

4. 

Date/Heure

Que renvoie-t‑il ?

SELECT '2024-11-27'::date + interval '24 hours';

Les intervalles sont un outil puissant pour simplifier les opérations sur les plages de dates !

Arithmétique de dates dans PostgreSQL :

  • + interval '24 hours' ajoute 24 heures
  • + interval '1 day' ajoute 1 jour
  • + interval '1 month' ajoute 1 mois
  • + interval '1 year' ajoute 1 an

Le résultat est 2024-11-28 00:00:00.

5. 

Horodatages

Quelle est l’affirmation la plus précise concernant timestamptz et timestamp ?

Ils occupent tous deux 8 octets, mais représentent des valeurs différentes.

timestamptz représente un instant absolu. PostgreSQL tient compte du paramètre TimeZone lors de l’analyse d’une entrée sans décalage explicite et lors de l’affichage. Il ne conserve ni le nom ni le décalage du fuseau d’origine.

timestamp stocke une date et une heure sans conversion de fuseau : il représente une heure locale dont le contexte doit être fourni séparément.

Des types à donner le vertige

Vous avez sûrement utilisé énormément de types PostgreSQL, n’est-ce pas ?

Les prochaines questions concernent les types natifs de PostgreSQL 17. 🤯

Pour chaque question, repérez le seul type invalide. 🕵️‍♂️

En avant !

6. 

Types Postgres

Lequel de ceux‑ci n’est pas ❌ un type PostgreSQL valide ?

PostgreSQL possède un ensemble riche de types de données, mais STRING(100) n’en fait pas partie.

Les types de chaîne corrects sont :

  • VARCHAR(100) (chaîne de longueur variable)
  • CHAR(100) (chaîne de longueur fixe)
  • TEXT (longueur illimitée)
  • CHARACTER VARYING(100) (identique à VARCHAR(100))

7. 

Types Postgres

Lequel de ceux‑ci n’est pas ❌ un type PostgreSQL valide ?

Il peut sembler familier car decimal128 est un type dans de nombreux environnements (y compris Mongo et Java). Ce n’est pas un type PostgreSQL valide, c’est decimal qui l’est.

Les types numériques corrects sont :

  • int (entier sur 4 octets)
  • bigint (entier sur 8 octets)
  • real (virgule flottante sur 4 octets)
  • double precision (virgule flottante sur 8 octets)
  • bigserial (entier auto‑incrémenté sur 8 octets)
  • smallserial (entier auto‑incrémenté sur 2 octets)

8. 

Types Postgres

Lequel de ceux‑ci n’est pas ❌ un type PostgreSQL valide ?

Cela vous a-t-il frustré, voire en colère ? Vous n’êtes pas seul ! Pour citer un contributeur « core » anonyme de la base de données, « what the hell, Dan?! I crashed on the type questions! Thats violent sir! Not sharing my score, hah. » 😈 De rien.

L’ensemble riche de types réseau de PostgreSQL n’inclut pas ipv4. Chaque fois que j’essaie de l’utiliser sans chercher, je me trompe. Peut‑être que macaddr8 me fait penser qu’il doit exister les types ipv4 et ipv6. Non, inet couvre les deux. De plus, cidr couvre les masques réseau pour les deux.

Les types réseau valides incluent :

  • cidr (adresse réseau IPv4/IPv6)
  • inet (adresse hôte IPv4/IPv6)
  • macaddr (adresse MAC)
  • macaddr8 (adresse MAC EUI‑64)

9. 

Types Postgres

Lequel de ceux‑ci n’est pas ❌ un type PostgreSQL valide ?

PostgreSQL possède un ensemble riche de types spécialisés, mais currency n’en fait pas partie !

Les types valides sont :

  • xml (données XML)
  • uuid (UUID)
  • money (montant monétaire)
  • interval (intervalle de temps)

10. 

Types Postgres

Lequel de ceux‑ci n’est pas ❌ un type PostgreSQL valide ?

PostgreSQL possède un ensemble riche de types spécialisés, mais triangle n’en fait pas partie.

Cette question porte sur les types géométriques intégrés à PostgreSQL. Les extensions comme PostGIS disposent de leurs propres fonctionnalités géométriques ; cela ne fait pas de triangle un type intégré à PostgreSQL.

Les types spécialisés corrects incluent :

  • box (boîte rectangulaire)
  • line (ligne infinie)
  • point (point 2D)
  • circle (cercle 2D)
  • polygon (polygone 2D)

11. 

Arithmétique des entiers

Que se passe-t-il lors du calcul du nombre total d’ID étudiants ?

SELECT 256 * 256 * 256 * 256;

Le type integer de PostgreSQL est signé sur 32 bits, allant de -2,147,483,648 à 2,147,483,647.

Le calcul 256^4 = 4,294,967,296 dépasse cette plage.

Pour gérer des nombres plus grands :

-- Use BIGINT
SELECT 256::bigint * 256 * 256 * 256;


-- Or numeric for arbitrary precision
SELECT 256::numeric * 256 * 256 * 256;

12. 

Date/Heure

Parmi ces valeurs, laquelle contient le moins de chiffres décimaux tout en dépassant la précision maximale de six décimales des timestamps PostgreSQL ?

Les timestamps PostgreSQL ont une précision microseconde (6 décimales).

  • Maximum : .123456 (6 chiffres)
  • Les nanosecondes (9 chiffres) sont arrondies ou tronquées à la précision prise en charge
  • Les décalages de fuseau horaire sont acceptés pour timestamptz, mais ne sont pas obligatoires

Astuce peu courante : Certaines langues/cadres envoient une précision en nanosecondes, mais PostgreSQL stocke les timestamps avec une précision microseconde.

13. 

Types Postgres

Lequel de ceux‑ci n’est pas ❌ un type PostgreSQL valide ?

(Sérieusement, ce sont (pour la plupart) de vrais types.)

PostgreSQL possède plusieurs types géométriques et de recherche texte intégrés, mais tsrank n’en fait pas partie.

Les types géométriques et de recherche texte corrects sont :

  • lseg (segment de ligne)
  • bytea (données binaires)
  • tsquery (requête de recherche texte)
  • tsvector (document de recherche texte)

14. 

Contraintes

Quand cette contrainte de note est‑elle vérifiée ?

ALTER TABLE students
ADD CONSTRAINT valid_grade
CHECK (
  (grade >= 0 AND grade <= 100) OR
  grade IS NULL
) NOT VALID;

Les contraintes NOT VALID :

  • Sont vérifiées immédiatement pour les nouvelles insertions et mises à jour
  • Ne valident pas les lignes existantes
  • Peuvent valider les lignes existantes plus tard avec VALIDATE CONSTRAINT
  • Sont utiles pour les grandes tables

Sans NOT VALID :

  • La contrainte est vérifiée immédiatement
  • Toutes les lignes existantes sont validées
  • Peut être lent sur les grandes tables

Bien joué ! Vous avez creusé plusieurs aspects de PostgreSQL ! 🐘

J’espère que vous avez appris quelque chose de nouveau, ou au moins obtenu un score dont vous pouvez vous vanter ! 🏆

Découvrez la Partie 2 pour plus de fun PostgreSQL ! 🚀

Envie de plus d’adrénaline dans la vie ? Consultez ma Collection de Quiz pour un plaisir sans fin* !

Loading comments...