DanLevy.net

Quiz : Deep Postgres : Pt. 1

SQL vous fait-il pousserun 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

Quel 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. Fonction de type : 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 sont tous les deux de 8 octets, mais ils ne stockent pas le même type de valeur.

Alors, quelle est la différence ? Elle se situe au niveau de l’analyse de l’entrée.

timestamptz

  • Normalise l’entrée vers un point absolu dans le temps.
  • Prend en compte le paramètre TimeZone du serveur/connexion lors de l’analyse d’une entrée sans décalage explicite et lors de l’affichage du résultat.

timestamp

  • Stocke une date et une heure sans conversion de fuseau horaire.
  • Ne préserve ni ne normalise les informations de fuseau horaire.

timestamp

  • Stocke la date & l’heure sans information de fuseau horaire.
  • Utile pour enregistrer explicitement des dates standardisées, soit en UTC soit dans un fuseau horaire spécifique.

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.

Je pense que les versions à venir de GEOS incluront le support Triangle OGC/WKT, ce qui signifie qu’il devrait éventuellement être intégré à PostGIS. (En gros, cette réponse pourrait être erronée à l’avenir.)

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

Quel est le plus petit littéral timestamp qui dépasse la précision maximale du time dans Postgres ?

CREATE TABLE class_sessions (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  start_time timestamptz,
  end_time timestamptz
);

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;

NOT VALID contraintes :

  • 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...