Comandi psql
I meta-comandi vengono eseguiti dentro psql (il client da terminale di PostgreSQL), non sul server. Iniziano con una barra rovesciata e non richiedono il punto e virgola.
| Cosa fa | Comando |
|---|
| Elenca i database | \l |
| Connettiti a un altro database | \c shop |
| Elenca le tabelle | \dt |
| Elenca le tabelle di tutti gli schemi | \dt *.* |
| Descrivi una tabella (colonne, indici, chiavi) | \d users |
| Descrivi con archiviazione e commenti | \d+ users |
| Elenca gli schemi | \dn |
| Elenca le viste | \dv |
| Elenca gli indici | \di |
| Elenca le funzioni | \df |
| Elenca i ruoli (utenti) | \du |
| Mostra la connessione attuale | \conninfo |
| Esegui SQL da un file | \i setup.sql |
| Attiva o disattiva il tempo delle query | \timing |
| Output espanso (una colonna per riga) | \x |
| Modifica l'ultima query nel tuo editor | \e |
| Aiuto sulla sintassi SQL | \h CREATE TABLE |
| Elenca tutti i meta-comandi | \? |
| Esci da psql | \q |
Connettersi dal terminale
| Cosa fa | Comando |
|---|
| Connettiti a un database come utente | psql -U postgres -d shop |
| Connettiti a un host e a una porta remoti | psql -h db.example.com -p 5432 -U app -d shop |
| Connettiti con un URL di connessione | psql postgresql://app:secret@localhost:5432/shop |
| Esegui una query ed esci | psql -d shop -c "SELECT count(*) FROM users;" |
| Esegui un file SQL | psql -d shop -f setup.sql |
| Crea un database dalla shell | createdb shop |
| Elimina un database dalla shell | dropdb shop |
Database e schemi
| Cosa fa | SQL |
|---|
| Crea un database | CREATE DATABASE shop; |
| Elimina un database | DROP DATABASE shop; |
| Mostra il database attuale | SELECT current_database(); |
| Crea uno schema | CREATE SCHEMA sales; |
| Mostra l'ordine di ricerca degli schemi | SHOW search_path; |
| Cerca prima in uno schema | SET search_path TO sales, public; |
| Mostra la versione del server | SELECT version(); |
Tabelle e tipi di dati
| Cosa fa | SQL |
|---|
| Crea una tabella con una chiave identity | CREATE TABLE products (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) CHECK (price >= 0), created_at timestamptz DEFAULT now()); |
| Aggiungi una colonna | ALTER TABLE users ADD COLUMN phone text; |
| Rinomina una colonna | ALTER TABLE users RENAME COLUMN country TO country_code; |
| Cambia il tipo di una colonna | ALTER TABLE users ALTER COLUMN age TYPE smallint; |
| Imposta un valore predefinito | ALTER TABLE users ALTER COLUMN active SET DEFAULT false; |
| Elimina una colonna | ALTER TABLE users DROP COLUMN tags; |
| Svuota una tabella e azzera la sua identity | TRUNCATE orders, users RESTART IDENTITY; |
| Elimina una tabella se esiste | DROP TABLE IF EXISTS products; |
| Decimale esatto per gli importi | numeric(10,2) |
| Data e ora con fuso orario | timestamptz |
| UUID casuale | SELECT gen_random_uuid(); |
| Tipo enum | CREATE TYPE mood AS ENUM ('happy', 'sad'); |
Vincoli e chiavi
| Cosa fa | SQL |
|---|
| Aggiungi una chiave primaria | ALTER TABLE logs ADD PRIMARY KEY (id); |
| Chiave esterna che elimina le righe figlie | CREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE); |
| Aggiungi un vincolo check | ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0); |
| Unique in cui i NULL contano come uguali (15+) | CREATE TABLE emails (address text UNIQUE NULLS NOT DISTINCT); |
| Rendi obbligatoria una colonna | ALTER TABLE users ALTER COLUMN country SET NOT NULL; |
| Elimina un vincolo | ALTER TABLE users DROP CONSTRAINT users_email_key; |
SELECT, WHERE e JOIN standard sono nella SQL cheat sheet; queste sono le forme di PostgreSQL.
| Cosa fa | SQL |
|---|
| Confronto senza distinzione tra maiuscole e minuscole | SELECT * FROM users WHERE name ILIKE 'a%'; |
| Corrispondenza con espressione regolare | SELECT * FROM users WHERE email ~ '@example\.com$'; |
| Prima riga per gruppo | SELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC; |
| Limit e offset | SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; |
| Limite di righe standard | SELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY; |
| Cast con :: | SELECT '42'::integer + 1; |
| Valore di riserva per NULL | SELECT name, COALESCE(email, 'none') FROM users; |
| Confronto che tratta i NULL come uguali | SELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com'; |
| Genera una serie di numeri | SELECT * FROM generate_series(1, 5); |
| Aggrega in una stringa | SELECT string_agg(name, ', ' ORDER BY name) FROM users; |
| Aggregazione condizionale | SELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders; |
Insert, update, delete e upsert
| Cosa fa | SQL |
|---|
| Inserisci e ottieni il nuovo id | INSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id; |
| Inserisci più righe | INSERT INTO users (name) VALUES ('Eli'), ('Femi'); |
| Upsert (inserisci o aggiorna) | INSERT INTO users (name, email) VALUES ('Asha K', 'asha@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; |
| Inserisci ignorando i duplicati | INSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING; |
| Aggiorna e vedi le righe modificate | UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary; |
| Aggiorna da un'altra tabella | UPDATE orders SET status = 'vip' FROM users WHERE orders.user_id = users.id AND users.country = 'IN'; |
| Elimina e vedi cosa è stato eliminato | DELETE FROM orders WHERE status = 'refunded' RETURNING id; |
| Elimina usando un'altra tabella | DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false; |
| Copia le righe in una nuova tabella | CREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid'; |
JSONB
| Cosa fa | SQL |
|---|
| Ottieni un campo come JSON | SELECT profile -> 'plan' FROM users; |
| Ottieni un campo come testo | SELECT profile ->> 'plan' FROM users; |
| Ottieni un campo annidato come testo | SELECT profile #>> '{address,city}' FROM users; |
| Righe il cui JSON contiene un valore | SELECT * FROM users WHERE profile @> '{"plan": "pro"}'; |
| Righe che hanno una chiave | SELECT * FROM users WHERE profile ? 'city'; |
| Imposta una chiave | UPDATE users SET profile = jsonb_set(profile, '{plan}', '"team"') WHERE id = 2; |
| Unisci oggetti | UPDATE users SET profile = profile || '{"beta": true}'; |
| Rimuovi una chiave | UPDATE users SET profile = profile - 'city'; |
| Costruisci JSON dalle colonne | SELECT jsonb_build_object('id', id, 'name', name) FROM users; |
| Righe come array JSON | SELECT jsonb_agg(users) FROM users; |
| Indice per ricerche con @> e ? | CREATE INDEX users_profile_idx ON users USING gin (profile); |
Array
| Cosa fa | SQL |
|---|
| Letterale di array | SELECT ARRAY[1, 2, 3]; |
| Righe in cui l'array contiene un valore | SELECT * FROM users WHERE 'sql' = ANY(tags); |
| Righe in cui l'array contiene tutti i valori | SELECT * FROM users WHERE tags @> ARRAY['sql', 'python']; |
| Una riga per elemento | SELECT name, unnest(tags) FROM users; |
| Aggiungi un elemento in fondo | UPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2; |
| Lunghezza dell'array | SELECT name, cardinality(tags) FROM users; |
Date e orari
| Cosa fa | SQL |
|---|
| Adesso, con fuso orario | SELECT now(); |
| Oggi | SELECT current_date; |
| Aggiungi un intervallo | SELECT current_date + INTERVAL '7 days'; |
| Righe degli ultimi 30 giorni | SELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days'; |
| Tronca al mese | SELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1; |
| Estrai una parte | SELECT EXTRACT(YEAR FROM hired_on) FROM employees; |
| Differenza tra date (giorni) | SELECT DATE '2026-12-25' - DATE '2026-11-17'; |
| Età tra due date | SELECT name, age(current_date, hired_on) FROM employees; |
| Formatta una data come testo | SELECT to_char(hired_on, 'DD Mon YYYY') FROM employees; |
| Converti testo in data | SELECT to_date('17/11/2026', 'DD/MM/YYYY'); |
| Converti in un fuso orario | SELECT now() AT TIME ZONE 'Asia/Kolkata'; |
Funzioni di testo
| Cosa fa | SQL |
|---|
| Concatena (NULL dà NULL) | SELECT name || ' <' || email || '>' FROM users; |
| Concatena saltando i NULL | SELECT concat_ws(' ', name, email) FROM users; |
| Maiuscole e minuscole | SELECT upper(name), lower(name) FROM users; |
| Lunghezza | SELECT name, length(name) FROM users; |
| Parte di una stringa | SELECT substring(name FROM 1 FOR 2) FROM users; |
| Sostituisci | SELECT replace(email, '@example.com', '') FROM users; |
| Estrai una parte tra separatori | SELECT split_part(email, '@', 2) FROM users; |
| Sostituzione con espressione regolare | SELECT regexp_replace('a1b22c', '[0-9]+', '', 'g'); |
| Togli gli spazi ai lati | SELECT trim(' hi '); |
CTE e funzioni finestra
| Cosa fa | SQL |
|---|
| Dai un nome a una subquery con WITH | WITH paid AS (SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id) SELECT * FROM paid WHERE total > 50; |
| CTE ricorsiva (conta fino a 5) | WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n; |
| Numera le righe per gruppo | SELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees; |
| Classifica con pari merito | SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees; |
| Totale progressivo | SELECT id, amount, sum(amount) OVER (ORDER BY id) FROM orders; |
| Valore della riga precedente | SELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees; |
| Join con una subquery propria per ogni riga | SELECT u.name, o.amount FROM users u CROSS JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) o; |
Indici
| Cosa fa | SQL |
|---|
| Indice B-tree (il predefinito) | CREATE INDEX orders_user_idx ON orders (user_id); |
| Indice unico | CREATE UNIQUE INDEX employees_name_idx ON employees (name); |
| Indice multicolonna | CREATE INDEX orders_user_created_idx ON orders (user_id, created_at); |
| Indice parziale | CREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid'; |
| Indice su espressione | CREATE INDEX users_lower_email_idx ON users (lower(email)); |
| Indice di copertura (scansioni solo indice) | CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount); |
| Crea senza bloccare le scritture | CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status); |
| Elimina un indice | DROP INDEX IF EXISTS orders_user_idx; |
| Vedi il piano | EXPLAIN SELECT * FROM orders WHERE user_id = 1; |
| Vedi il piano con i tempi reali | EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1; |
Transazioni e lock
| Cosa fa | SQL |
|---|
| Inizia, poi conferma o annulla | BEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK; |
| Annulla parte di una transazione | BEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT; |
| Isolamento più rigoroso | BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT; |
| Blocca le righe che leggi | BEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT; |
| Coda di lavori: salta le righe bloccate | BEGIN; SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; COMMIT; |
Utenti, ruoli e privilegi
| Cosa fa | SQL |
|---|
| Crea un ruolo con login | CREATE ROLE app LOGIN PASSWORD 'change-me'; |
| Accesso in sola lettura a una tabella | GRANT SELECT ON users TO app; |
| Accesso in lettura a ogni tabella di uno schema | GRANT SELECT ON ALL TABLES IN SCHEMA public TO app; |
| Revoca un privilegio | REVOKE SELECT ON users FROM app; |
| Cambia una password | ALTER ROLE app PASSWORD 'new-secret'; |
| Mostra l'utente attuale | SELECT current_user; |
Manutenzione e monitoraggio
| Cosa fa | SQL |
|---|
| Query in esecuzione | SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle'; |
| Annulla una query | SELECT pg_cancel_backend(12345); |
| Chiudi una connessione | SELECT pg_terminate_backend(12345); |
| Dimensione di una tabella con i suoi indici | SELECT pg_size_pretty(pg_total_relation_size('orders')); |
| Dimensione del database | SELECT pg_size_pretty(pg_database_size(current_database())); |
| Aggiorna le statistiche del planner | ANALYZE orders; |
| Recupera le righe morte | VACUUM orders; |
| Riscrivi una tabella alla dimensione minima (la blocca) | VACUUM FULL orders; |
| Mostra un'impostazione | SHOW work_mem; |
| Cambia un'impostazione per questa sessione | SET work_mem = '64MB'; |
Backup e ripristino
Comandi di shell degli strumenti client di PostgreSQL.
| Cosa fa | Comando |
|---|
| Esporta un database in un file SQL | pg_dump -U postgres shop > shop.sql |
| Esporta nel formato custom | pg_dump -Fc -U postgres shop > shop.dump |
| Esporta una sola tabella | pg_dump -t orders shop > orders.sql |
| Ripristina un dump SQL | psql -d shop -f shop.sql |
| Ripristina un dump in formato custom | pg_restore -d shop shop.dump |
| Esporta tutti i database e i ruoli | pg_dumpall > cluster.sql |
| Esporta una query in CSV (da psql) | \copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER |
| Importa un CSV (da psql) | \copy orders (user_id, amount, status) FROM 'orders.csv' CSV HEADER |
Ogni comando psql e ogni istruzione PostgreSQL che ti serve, in tabelle da copiare e incollare: prima la connessione e i comandi \d, poi tabelle, upsert, JSONB, date, funzioni finestra, indici, ruoli, backup e manutenzione.
L'SQL di questa pagina usa la sintassi di PostgreSQL 17 e gran parte funziona anche nelle versioni precedenti. Le istruzioni SQL sono state verificate su PostgreSQL 17; per provarne una, incollala nel playground PostgreSQL, che include tabelle di esempio.