Dan Levy

Quiz: Deep Postgres: Teil 1

Lässt SQL Sie kreischen?

Teil 1 von 2. Weiter zu Teil 2

PostgreSQL 🐘 ist eindeutig meine Lieblingsdatenbank! Ich lerne ständig neue Tricks und Fallstricke, also habe ich sie in einem neuen Quiz zusammengefasst.

Dieses Quiz deckt eine Mischung aus bekannten und weniger bekannten PostgreSQL‑Features und Fallstricken ab: von eingebauten Aggregaten über Typumwandlungen, Constraints und mehr.

Viel Erfolg! 🍀

1. 

Aufwärmen: Funktionen

Welche ist KEINE eingebaute Aggregatfunktion in PostgreSQL?

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

MEDIAN ist nicht eingebaut! Du brauchst:

PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY grade)

Übliche eingebaute Aggregate:

  • MIN, MAX, COUNT
  • AVG, SUM
  • ARRAY_AGG, STRING_AGG
  • Verschiedene statistische Funktionen

2. 

Aufwärmen: Typumwandlung

Welche dieser Typumwandlungen ist ungültig ❌?

PostgreSQL unterstützt drei Cast‑Syntaxen:

  1. ANSI‑SQL: CAST(expression AS type).
  2. PostgreSQL: expression::type.
  3. Typisiertes Literal: type 'literal'.

Alle sind funktional äquivalent, aber:

  • CAST() ist am portabelsten.
  • :: ist PostgreSQL‑spezifisch, wird aber häufig verwendet.
  • Der infix‑artige type 'literal' ist weniger verbreitet, aber dennoch gültig.

3. 

Einschränkungen

Wie viele NULL‑Werte sind hier erlaubt?

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

UNIQUE‑Constraints in PostgreSQL:

  • Erlauben mehrere NULL‑Werte.
  • NULL ≠ NULL bei Unique‑Prüfungen.

Um NULL‑Werte zu verhindern, füge NOT NULL hinzu:

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

4. 

Datum/Uhrzeit

Was gibt das zurück?

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

Intervalle sind ein mächtiges Werkzeug, um Datumsbereichs‑Operationen zu vereinfachen!

Datumsarithmetik in PostgreSQL:

  • + interval '24 hours' fügt 24 Stunden hinzu
  • + interval '1 day' fügt 1 Tag hinzu
  • + interval '1 month' fügt 1 Monat hinzu
  • + interval '1 year' fügt 1 Jahr hinzu

Das Ergebnis ist 2024-11-28 00:00:00.

5. 

Zeitstempel

Was ist die genaueste Aussage zu timestamptz und timestamp?

Sie sind beide 8 Byte groß, aber sie speichern nicht denselben Werttyp.

Also, was ist der Unterschied? Er liegt beim Parsen der Eingabe.

timestamptz

  • Normalisiert die Eingabe zu einem absoluten Zeitpunkt.
  • Berücksichtigt die Server‑/Verbindungseinstellung TimeZone, wenn Eingaben ohne expliziten Offset geparst werden und bei der Ausgabe.

timestamp

  • Speichert Datum und Uhrzeit ohne Zeitzonen‑Konvertierung.
  • Bewahrt keine Zeitzoneninformationen und normalisiert sie nicht.

Schwindelerregende Datentypen

6. 

Postgres-Typen

Welche dieser ist ❌ kein gültiger PostgreSQL‑Typ?

PostgreSQL bietet eine umfangreiche Palette an Datentypen, aber STRING(100) gehört nicht dazu.

Die korrekten String‑Typen sind:

  • VARCHAR(100) (variable Länge)
  • CHAR(100) (feste Länge)
  • TEXT (unbegrenzte Länge)
  • CHARACTER VARYING(100) (gleichbedeutend mit VARCHAR(100))

7. 

Postgres-Typen

Welche dieser ist ❌ kein gültiger PostgreSQL‑Typ?

Es könnte vertraut wirken, da decimal128 in vielen Systemen (z. B. Mongo und Java) vorkommt. Es ist kein gültiger PostgreSQL‑Typ, decimal ist es.

Die korrekten numerischen Typen sind:

  • int (4‑Byte‑Integer)
  • bigint (8‑Byte‑Integer)
  • real (4‑Byte‑Gleitkomma)
  • double precision (8‑Byte‑Gleitkomma)
  • bigserial (auto‑inkrementierender 8‑Byte‑Integer)
  • smallserial (auto‑inkrementierender 2‑Byte‑Integer)

8. 

Postgres-Typen

Welcher dieser ist ❌ kein gültiger PostgreSQL‑Typ?

Hat dich das frustriert, sogar wütend? Du bist nicht allein! Wie ein namenloser “Core”‑Datenbank‑Mitwirkender sagte: “Was zum Teufel, Dan?! Ich bin bei den Typ‑Fragen abgestürzt! Das ist gewalttätig, Sir! Teile meine Punktzahl nicht, haha.” 😈 Bitte sehr.

PostgreSQLs umfangreiche Sammlung von Netzwerk‑Typen enthält kein ipv4. Jedes Mal, wenn ich versuche, es ohne zu googeln zu benutzen, liege ich falsch. Vielleicht lässt mich macaddr8 vermuten, dass es auch ipv4- und ipv6-Typen geben müsste. Nein, inet deckt beides ab. Außerdem deckt cidr Netzwerk‑Masken für beide ab.

Gültige Netzwerk‑Typen sind:

  • cidr (IPv4/IPv6‑Netzwerkadresse)
  • inet (IPv4/IPv6‑Hostadresse)
  • macaddr (MAC‑Adresse)
  • macaddr8 (EUI‑64‑MAC‑Adresse)

9. 

Postgres-Typen

Welche dieser ist ❌ kein gültiger PostgreSQL‑Typ?

PostgreSQL verfügt über eine umfangreiche Menge spezialisierter Typen, aber currency gehört nicht dazu!

Die gültigen Typen sind:

  • xml (XML‑Daten)
  • uuid (UUID)
  • money (Währungsbetrag)
  • interval (Zeitintervall)

10. 

Postgres-Typen

Welcher dieser ist ❌ kein gültiger PostgreSQL‑Typ?

PostgreSQL verfügt über eine umfangreiche Menge spezialisierter Typen, aber triangle ist keiner davon.

Diese Frage betrifft die eingebauten Geometrietypen von PostgreSQL. Erweiterungen wie PostGIS haben eigene Geometriefunktionen; dadurch wird triangle aber kein eingebauter PostgreSQL-Typ.

Die korrekten spezialisierten Typen umfassen:

  • box (rechteckige Box)
  • line (unendliche Linie)
  • point (2D‑Punkt)
  • circle (2D‑Kreis)
  • polygon (2D‑Polygon)

11. 

Ganzzahlarithmetik

Was passiert bei der Berechnung der insgesamt möglichen Student‑IDs?

SELECT 256 * 256 * 256 * 256;

Der integer‑Typ von PostgreSQL ist 32‑Bit vorzeichenbehaftet und reicht von -2,147,483,648 bis 2,147,483,647.

Die Berechnung 256^4 = 4,294,967,296 überschreitet diesen Bereich.

Um größere Zahlen zu verarbeiten:

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


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

12. 

Datum/Uhrzeit

Welches Beispiel hat die wenigsten Nachkommastellen, die über PostgreSQLs Zeitstempelpräzision von sechs Stellen hinausgehen?

PostgreSQL‑Zeitstempel haben eine Mikrosekunden‑Präzision (6 Dezimalstellen).

  • Maximum: .123456 (6 Ziffern)
  • Nanosekunden (9 Ziffern) werden auf die unterstützte Präzision gerundet oder abgeschnitten
  • Zeitzonen‑Offsets werden für timestamptz akzeptiert, sind aber nicht zwingend erforderlich

Nicht so häufiges Gotcha: Einige Sprachen/Frameworks senden Nanosekunden‑Präzision, aber PostgreSQL speichert Zeitstempel mit Mikrosekunden‑Präzision.

13. 

Postgres-Typen

Welcher dieser ist ❌ kein gültiger PostgreSQL‑Typ?

(Im Ernst, das sind (meist) echte Typen.)

PostgreSQL hat mehrere geometrische und Volltext‑Suchtypen eingebaut, aber tsrank gehört nicht dazu.

Die korrekten geometrischen und Volltext‑Suchtypen sind:

  • lseg (Liniensegment)
  • bytea (Binärdaten)
  • tsquery (Volltext‑Suchanfrage)
  • tsvector (Volltext‑Dokument)

14. 

Einschränkungen

Wann wird diese Grade-Constraint geprüft?

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

NOT VALID-Constraints:

  • Werden sofort für neue Inserts und Updates geprüft
  • Bereits vorhandene Zeilen werden zunächst nicht geprüft.
  • Können vorhandene Zeilen später mit VALIDATE CONSTRAINT validieren
  • Sind nützlich für große Tabellen

Ohne NOT VALID:

  • Constraint wird sofort geprüft
  • Alle vorhandenen Zeilen werden validiert
  • Kann bei großen Tabellen langsam sein

Gut gemacht! Du hast inmehreren Bereichen von PostgreSQL tief gegraben! 🐘

Ich hoffe, du hast etwas Neues gelernt oder zumindest eine Punktzahl, mit der du prahlen kannst! 🏆

Sieh dir Teil 2 für mehr Postgres‑Spaß an! 🚀

Willst du mehr Nervenkitzel im Leben? Schau dir meine Quiz‑Sammlung für endlosen* Spaß an!

Loading comments...