Quiz : Postgres en profondeur, partie 1

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

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.

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

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.

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.

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

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)

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)

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)

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)

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;

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.

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)

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