Quiz: Deep Postgres: Teil 1

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

PostgreSQL unterstützt drei Cast‑Syntaxen:

1. ANSI‑SQL: CAST(expression AS type).
2. PostgreSQL: expression::type.
3. Typfunktion: 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.

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

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.

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.

timestamp

• Speichert Datum & Uhrzeit ohne Zeitzonenangabe.
• Praktisch, wenn standardisierte Daten explizit gespeichert werden sollen, sei es in UTC oder einer bestimmten Zeitzone.

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

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)

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 denken, dass es muss ipv4‑ und ipv6‑Typen geben muss. 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)

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)

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

Ich glaube, dass kommende Versionen von GEOS Triangle‑Unterstützung für OGC/WKT enthalten werden, was bedeutet, dass es irgendwann in PostGIS aufgenommen werden sollte. (Im Grunde könnte diese Antwort in Zukunft falsch sein.)

Die korrekten spezialisierten Typen umfassen:

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

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;

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.

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)

NOT VALID-Constraints:

• Werden sofort für neue Inserts und Updates geprüft
• Validieren nicht vorhandene Zeilen
• 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