DanLevy.net

Quiz:Postgres approfondito: Parte 1

SQL ti fa strillare?

Parte 1 di 2. Vai alla Parte 2

PostgreSQL 🐘 è facilmente il mio database preferito! Imparo sempre nuovi trucchi e insidie, così ho deciso di raccoglierli in un nuovo quiz!

Questo quiz copre un mix di funzionalità PostgreSQL note e meno conosciute, incluse le insidie: dagli aggregati integrati al casting dei tipi, ai vincoli e altro ancora.

Buona fortuna! 🍀

1. 

Riscaldamento: Funzioni

Qual è la funzione aggregata NON incorporata in PostgreSQL?

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

MEDIAN non è incorporata! Serve:

PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY grade)

Aggregati incorporati comuni:

  • MIN, MAX, COUNT
  • AVG, SUM
  • ARRAY_AGG, STRING_AGG
  • Varie funzioni statistiche

2. 

Riscaldamento: Conversione di tipo

Quale di queste conversioni di tipo è non valida ❌?

PostgreSQL supporta tre sintassi di cast:

  1. ANSI SQL: CAST(expression AS type).
  2. PostgreSQL: expression::type.
  3. Funzione di tipo: type 'literal'.

Tutte sono funzionalmente equivalenti, ma:

  • CAST() è la più portabile.
  • :: è specifico di PostgreSQL ma comunemente usato.
  • Lo stile infisso type 'literal' è meno comune ma comunque valido.

3. 

Vincoli

Quanti valori NULL sono consentiti qui?

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

Vincoli UNIQUE in PostgreSQL:

  • Consentono più valori NULL.
  • NULL ≠ NULL nei controlli di unicità.

Per impedire valori NULL, aggiungi NOT NULL:

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

4. 

Data/Ora

Cosa restituisce questo?

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

Gli intervalli sono uno strumento potente per semplificare le operazioni su intervalli di date!

Aritmetica delle date in PostgreSQL:

  • + interval '24 hours' aggiunge 24 ore
  • + interval '1 day' aggiunge 1 giorno
  • + interval '1 month' aggiunge 1 mese
  • + interval '1 year' aggiunge 1 anno

Il risultato è 2024-11-28 00:00:00.

5. 

Timestamp

Qual è l’affermazione più accurata su timestamptz e timestamp?

Entrambi sono 8 byte, ma non memorizzano lo stesso tipo di valore.

Qual è la differenza? Sta nell’analisi dell’input.

timestamptz

  • Normalizza l’input a un punto assoluto nel tempo.
  • Tiene conto dell’impostazione TimeZone del server/connessione quando analizza input senza offset esplicito e quando visualizza l’output.

timestamp

  • Memorizza data e ora senza conversione di fuso orario.
  • Non conserva né normalizza le informazioni sul fuso orario.

timestamp

  • Memorizza data e ora senza informazioni sul fuso orario.
  • Utile per archiviare esplicitamente date standardizzate, sia in UTC che in un fuso orario specifico.

6. 

Tipi Postgres

Quale di questi ❌ non è un tipo PostgreSQL valido?

PostgreSQL ha un ricco insieme di tipi di dati, ma STRING(100) non è tra questi.

I tipi di stringa corretti includono:

  • VARCHAR(100) (stringa a lunghezza variabile)
  • CHAR(100) (stringa a lunghezza fissa)
  • TEXT (lunghezza illimitata)
  • CHARACTER VARYING(100) (stesso di VARCHAR(100))

7. 

Tipi Postgres

Quale di questi ❌ non è un tipo PostgreSQL valido?

Può sembrare familiare, poiché decimal128 è un tipo in molti ambienti (inclusi Mongo e Java). Non è un tipo PostgreSQL valido; il tipo corretto è decimal.

I tipi numerici corretti includono:

  • int (intero a 4 byte)
  • bigint (intero a 8 byte)
  • real (virgola mobile a 4 byte)
  • double precision (virgola mobile a 8 byte)
  • bigserial (intero auto‑incrementante a 8 byte)
  • smallserial (intero auto‑incrementante a 2 byte)

8. 

Tipi Postgres

Quale di questi ❌ non è un tipo PostgreSQL valido?

Ti ha fatto arrabbiare, persino arrabbiato? Non sei solo! Per citare un anonimo collaboratore “core” del database, “che diavolo, Dan?! Sono andato in crash sulle domande sui tipi! È violento, signore! Non condivido il mio punteggio, ahah.” 😈 Prego.

9. 

Tipi Postgres

Quale di questi non è ❌ un tipo PostgreSQL valido?

PostgreSQL ha un ricco insieme di tipi specializzati, ma currency non è uno di questi!

I tipi validi includono:

  • xml (dati XML)
  • uuid (UUID)
  • money (importo monetario)
  • interval (intervallo di tempo)

10. 

Tipi Postgres

Quale di questi ❌ non è un tipo PostgreSQL valido?

PostgreSQL ha un ricco insieme di tipi specializzati, ma triangle non è tra questi.

Credo che le prossime versioni di GEOS includeranno il supporto Triangle OGC/WKT, il che significa che dovrebbe essere eventualmente incluso in PostGIS. (In pratica, questa risposta potrebbe essere sbagliata in futuro.)

I tipi specializzati corretti includono:

  • box (scatola rettangolare)
  • line (linea infinita)
  • point (punto 2D)
  • circle (cerchio 2D)
  • polygon (poligono 2D)

11. 

Aritmetica Interi

Cosa succede quando si calcola il totale possibile di ID studente?

SELECT 256 * 256 * 256 * 256;

Il tipo integer di PostgreSQL è a 32 bit con segno, con intervallo da -2,147,483,648 a 2,147,483,647.

Il calcolo 256^4 = 4,294,967,296 supera questo intervallo.

Per gestire numeri più grandi:

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


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

12. 

Data/Ora

Qual è il più piccolo letterale timestamp che supera la precisione massima del time in Postgres?

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

I timestamp di PostgreSQL hanno una precisione di microsecondi (6 cifre decimali).

  • Massimo: .123456 (6 cifre)
  • I nanosecondi (9 cifre) vengono arrotondati o troncati alla precisione supportata
  • I fusi orari sono accettati per timestamptz, ma non obbligatori

Curiosità poco comune: Alcuni linguaggi/framework inviano precisione a nanosecondi, ma PostgreSQL memorizza i timestamp con precisione microsecondi.

13. 

Tipi Postgres

Quale di questi ❌ non è un tipo PostgreSQL valido?

(Sul serio, questi sono (per lo più) tipi reali.)

PostgreSQL ha diversi tipi geometrici e di ricerca testuale incorporati, ma tsrank non è tra questi.

I tipi geometrici e di ricerca testuale corretti includono:

  • lseg (segmento di linea)
  • bytea (dati binari)
  • tsquery (query di ricerca testuale)
  • tsvector (documento di ricerca testuale)

14. 

Vincoli

Quando viene verificato questo vincolo?

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

NOT VALID constraints:

  • Vengono controllati immediatamente per nuovi inserimenti e aggiornamenti
  • Non convalidano le righe esistenti
  • È possibile convalidare le righe esistenti in seguito con VALIDATE CONSTRAINT
  • Sono utili per tabelle grandi

Without NOT VALID:

  • Il vincolo viene controllato immediatamente
  • Tutte le righe esistenti sono convalidate
  • Può essere lento su tabelle grandi

Ben fatto! Hai scavato a fondo in diversi ambiti di PostgreSQL! 🐘

Spero tu abbia imparato qualcosa di nuovo, o almeno ottenuto un punteggio di cui vantarti! 🏆

Dai un’occhiata a Parte 2 per più divertimento con Postgres! 🚀

Vuoi più brividi nella vita? Dai un’occhiata alla mia Raccolta di Quiz per divertimento infinito!*

Loading comments...