Menu
CoddyTech

PostgreSQL Spickzettel

Zuletzt aktualisiert

psql-Befehle

Meta-Befehle laufen in psql (dem Terminal-Client von PostgreSQL), nicht auf dem Server. Sie beginnen mit einem Backslash und brauchen kein Semikolon.

Was es tutBefehl
Datenbanken auflisten\l
Mit einer anderen Datenbank verbinden\c shop
Tabellen auflisten\dt
Tabellen in allen Schemas auflisten\dt *.*
Eine Tabelle beschreiben (Spalten, Indizes, Schlüssel)\d users
Mit Speicher und Kommentaren beschreiben\d+ users
Schemas auflisten\dn
Views auflisten\dv
Indizes auflisten\di
Funktionen auflisten\df
Rollen (Benutzer) auflisten\du
Die aktuelle Verbindung anzeigen\conninfo
SQL aus einer Datei ausführen\i setup.sql
Zeitmessung für Abfragen umschalten\timing
Erweiterte Ausgabe (eine Spalte pro Zeile)\x
Die letzte Abfrage in deinem Editor bearbeiten\e
Hilfe zur SQL-Syntax\h CREATE TABLE
Alle Meta-Befehle auflisten\?
psql beenden\q

Verbinden im Terminal

Was es tutBefehl
Als Benutzer mit einer Datenbank verbindenpsql -U postgres -d shop
Mit einem entfernten Host und Port verbindenpsql -h db.example.com -p 5432 -U app -d shop
Mit einer Verbindungs-URL verbindenpsql postgresql://app:secret@localhost:5432/shop
Eine Abfrage ausführen und beendenpsql -d shop -c "SELECT count(*) FROM users;"
Eine SQL-Datei ausführenpsql -d shop -f setup.sql
Eine Datenbank in der Shell erstellencreatedb shop
Eine Datenbank in der Shell löschendropdb shop

Datenbanken und Schemas

Was es tutSQL
Eine Datenbank erstellenCREATE DATABASE shop;
Eine Datenbank löschenDROP DATABASE shop;
Die aktuelle Datenbank anzeigenSELECT current_database();
Ein Schema erstellenCREATE SCHEMA sales;
Die Suchreihenfolge der Schemas anzeigenSHOW search_path;
Zuerst in einem Schema suchenSET search_path TO sales, public;
Die Serverversion anzeigenSELECT version();

Tabellen und Datentypen

Was es tutSQL
Eine Tabelle mit Identity-Schlüssel erstellenCREATE 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());
Eine Spalte hinzufügenALTER TABLE users ADD COLUMN phone text;
Eine Spalte umbenennenALTER TABLE users RENAME COLUMN country TO country_code;
Den Typ einer Spalte ändernALTER TABLE users ALTER COLUMN age TYPE smallint;
Einen Standardwert setzenALTER TABLE users ALTER COLUMN active SET DEFAULT false;
Eine Spalte löschenALTER TABLE users DROP COLUMN tags;
Eine Tabelle leeren und ihre Identity zurücksetzenTRUNCATE orders, users RESTART IDENTITY;
Eine Tabelle löschen, falls vorhandenDROP TABLE IF EXISTS products;
Exakte Dezimalzahl für Geldbeträgenumeric(10,2)
Datum und Uhrzeit mit Zeitzonetimestamptz
Zufällige UUIDSELECT gen_random_uuid();
Enum-TypCREATE TYPE mood AS ENUM ('happy', 'sad');

Constraints und Schlüssel

Was es tutSQL
Einen Primärschlüssel hinzufügenALTER TABLE logs ADD PRIMARY KEY (id);
Fremdschlüssel, der Kindzeilen mitlöschtCREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE);
Einen CHECK-Constraint hinzufügenALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0);
Unique, bei dem NULLs als gleich gelten (15+)CREATE TABLE emails (address text UNIQUE NULLS NOT DISTINCT);
Eine Spalte zur Pflicht machenALTER TABLE users ALTER COLUMN country SET NOT NULL;
Einen Constraint löschenALTER TABLE users DROP CONSTRAINT users_email_key;

Abfragen (PostgreSQL-Extras)

Standard-SELECT, WHERE und JOIN stehen im SQL-Spickzettel; hier sind die PostgreSQL-Varianten.

Was es tutSQL
Vergleich ohne Beachtung der Groß-/KleinschreibungSELECT * FROM users WHERE name ILIKE 'a%';
Vergleich mit regulärem AusdruckSELECT * FROM users WHERE email ~ '@example\.com$';
Erste Zeile pro GruppeSELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC;
Limit und OffsetSELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
Zeilenlimit nach SQL-StandardSELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY;
Umwandlung mit ::SELECT '42'::integer + 1;
Ersatzwert für NULLSELECT name, COALESCE(email, 'none') FROM users;
Vergleichen, wobei NULLs als gleich geltenSELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com';
Eine Zahlenreihe erzeugenSELECT * FROM generate_series(1, 5);
Zu einem String aggregierenSELECT string_agg(name, ', ' ORDER BY name) FROM users;
Bedingte AggregationSELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders;

Einfügen, Aktualisieren, Löschen und Upsert

Was es tutSQL
Einfügen und die neue id zurückbekommenINSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id;
Mehrere Zeilen einfügenINSERT INTO users (name) VALUES ('Eli'), ('Femi');
Upsert (einfügen oder aktualisieren)INSERT INTO users (name, email) VALUES ('Asha K', 'asha@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
Einfügen, Duplikate ignorierenINSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING;
Aktualisieren und die geänderten Zeilen sehenUPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary;
Aus einer anderen Tabelle aktualisierenUPDATE orders SET status = 'vip' FROM users WHERE orders.user_id = users.id AND users.country = 'IN';
Löschen und sehen, was gelöscht wurdeDELETE FROM orders WHERE status = 'refunded' RETURNING id;
Mithilfe einer anderen Tabelle löschenDELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false;
Zeilen in eine neue Tabelle kopierenCREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid';

JSONB

Was es tutSQL
Ein Feld als JSON lesenSELECT profile -> 'plan' FROM users;
Ein Feld als Text lesenSELECT profile ->> 'plan' FROM users;
Ein verschachteltes Feld als Text lesenSELECT profile #>> '{address,city}' FROM users;
Zeilen, deren JSON einen Wert enthältSELECT * FROM users WHERE profile @> '{"plan": "pro"}';
Zeilen mit einem bestimmten SchlüsselSELECT * FROM users WHERE profile ? 'city';
Einen Schlüssel setzenUPDATE users SET profile = jsonb_set(profile, '{plan}', '"team"') WHERE id = 2;
Objekte zusammenführenUPDATE users SET profile = profile || '{"beta": true}';
Einen Schlüssel entfernenUPDATE users SET profile = profile - 'city';
JSON aus Spalten bauenSELECT jsonb_build_object('id', id, 'name', name) FROM users;
Zeilen als JSON-ArraySELECT jsonb_agg(users) FROM users;
Index für Abfragen mit @> und ?CREATE INDEX users_profile_idx ON users USING gin (profile);

Arrays

Was es tutSQL
Array-LiteralSELECT ARRAY[1, 2, 3];
Zeilen, deren Array einen Wert enthältSELECT * FROM users WHERE 'sql' = ANY(tags);
Zeilen, deren Array alle diese Werte enthältSELECT * FROM users WHERE tags @> ARRAY['sql', 'python'];
Eine Zeile pro ElementSELECT name, unnest(tags) FROM users;
Ein Element anhängenUPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2;
Array-LängeSELECT name, cardinality(tags) FROM users;

Datum und Uhrzeit

Was es tutSQL
Jetzt, mit ZeitzoneSELECT now();
HeuteSELECT current_date;
Ein Intervall addierenSELECT current_date + INTERVAL '7 days';
Zeilen der letzten 30 TageSELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days';
Auf den Monat abschneidenSELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1;
Einen Teil extrahierenSELECT EXTRACT(YEAR FROM hired_on) FROM employees;
Differenz zwischen Daten (Tage)SELECT DATE '2026-12-25' - DATE '2026-11-17';
Alter zwischen zwei DatenSELECT name, age(current_date, hired_on) FROM employees;
Ein Datum als Text formatierenSELECT to_char(hired_on, 'DD Mon YYYY') FROM employees;
Text in ein Datum umwandelnSELECT to_date('17/11/2026', 'DD/MM/YYYY');
In eine Zeitzone umrechnenSELECT now() AT TIME ZONE 'Asia/Kolkata';

Textfunktionen

Was es tutSQL
Verketten (NULL ergibt NULL)SELECT name || ' <' || email || '>' FROM users;
Verketten und NULLs überspringenSELECT concat_ws(' ', name, email) FROM users;
Groß- und KleinbuchstabenSELECT upper(name), lower(name) FROM users;
LängeSELECT name, length(name) FROM users;
Teil eines StringsSELECT substring(name FROM 1 FOR 2) FROM users;
ErsetzenSELECT replace(email, '@example.com', '') FROM users;
Aufteilen und einen Teil nehmenSELECT split_part(email, '@', 2) FROM users;
Ersetzen mit regulärem AusdruckSELECT regexp_replace('a1b22c', '[0-9]+', '', 'g');
Leerzeichen entfernenSELECT trim(' hi ');

CTEs und Window-Funktionen

Was es tutSQL
Eine Unterabfrage mit WITH benennenWITH paid AS (SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id) SELECT * FROM paid WHERE total > 50;
Rekursive CTE (bis 5 zählen)WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n;
Zeilen pro Gruppe nummerierenSELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;
Rang mit GleichständenSELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees;
Laufende SummeSELECT id, amount, sum(amount) OVER (ORDER BY id) FROM orders;
Wert der vorherigen ZeileSELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees;
Jede Zeile mit ihrer eigenen Unterabfrage joinenSELECT 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;

Indizes

Was es tutSQL
B-Tree-Index (der Standard)CREATE INDEX orders_user_idx ON orders (user_id);
Unique-IndexCREATE UNIQUE INDEX employees_name_idx ON employees (name);
Mehrspaltiger IndexCREATE INDEX orders_user_created_idx ON orders (user_id, created_at);
Partieller IndexCREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid';
AusdrucksindexCREATE INDEX users_lower_email_idx ON users (lower(email));
Covering Index (Index-Only-Scans)CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount);
Erstellen, ohne Schreibzugriffe zu blockierenCREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
Einen Index löschenDROP INDEX IF EXISTS orders_user_idx;
Den Plan ansehenEXPLAIN SELECT * FROM orders WHERE user_id = 1;
Den Plan mit echten Laufzeiten ansehenEXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

Transaktionen und Sperren

Was es tutSQL
Starten, dann behalten oder rückgängig machenBEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK;
Einen Teil einer Transaktion rückgängig machenBEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT;
Strengere IsolationBEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT;
Die gelesenen Zeilen sperrenBEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT;
Job-Queue: gesperrte Zeilen überspringenBEGIN; SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; COMMIT;

Benutzer, Rollen und Rechte

Was es tutSQL
Eine Login-Rolle erstellenCREATE ROLE app LOGIN PASSWORD 'change-me';
Nur-Lese-Zugriff auf eine TabelleGRANT SELECT ON users TO app;
Lesezugriff auf alle Tabellen eines SchemasGRANT SELECT ON ALL TABLES IN SCHEMA public TO app;
Ein Recht wieder entziehenREVOKE SELECT ON users FROM app;
Ein Passwort ändernALTER ROLE app PASSWORD 'new-secret';
Den aktuellen Benutzer anzeigenSELECT current_user;

Wartung und Monitoring

Was es tutSQL
Laufende AbfragenSELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle';
Eine Abfrage abbrechenSELECT pg_cancel_backend(12345);
Eine Verbindung beendenSELECT pg_terminate_backend(12345);
Größe einer Tabelle samt IndizesSELECT pg_size_pretty(pg_total_relation_size('orders'));
Größe der DatenbankSELECT pg_size_pretty(pg_database_size(current_database()));
Planer-Statistiken aktualisierenANALYZE orders;
Tote Zeilen freigebenVACUUM orders;
Eine Tabelle auf minimale Größe neu schreiben (sperrt sie)VACUUM FULL orders;
Eine Einstellung anzeigenSHOW work_mem;
Eine Einstellung für diese Sitzung ändernSET work_mem = '64MB';

Backup und Wiederherstellung

Shell-Befehle aus den PostgreSQL-Client-Tools.

Was es tutBefehl
Eine Datenbank in eine SQL-Datei dumpenpg_dump -U postgres shop > shop.sql
Im Custom-Format dumpenpg_dump -Fc -U postgres shop > shop.dump
Eine einzelne Tabelle dumpenpg_dump -t orders shop > orders.sql
Einen SQL-Dump wiederherstellenpsql -d shop -f shop.sql
Einen Dump im Custom-Format wiederherstellenpg_restore -d shop shop.dump
Alle Datenbanken und Rollen dumpenpg_dumpall > cluster.sql
Eine Abfrage als CSV exportieren (aus psql)\copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER
Eine CSV importieren (aus psql)\copy orders (user_id, amount, status) FROM 'orders.csv' CSV HEADER

Jeder psql-Befehl und jede PostgreSQL-Anweisung, die du brauchst, in Tabellen zum Kopieren: zuerst das Verbinden und die \d-Befehle, dann Tabellen, Upserts, JSONB, Datum und Uhrzeit, Window-Funktionen, Indizes, Rollen, Backups und Wartung.

Das SQL hier ist PostgreSQL-17-Syntax, und das meiste davon funktioniert auch in älteren Versionen. Die SQL-Anweisungen wurden auf PostgreSQL 17 geprüft; um eine auszuprobieren, füge sie in den PostgreSQL-Playground ein, der Beispieltabellen mitbringt.

PostgreSQL-Spickzettel-FAQ

Was sind psql-Befehle?
psql ist der Terminal-Client von PostgreSQL. Neben SQL versteht er Meta-Befehle, die mit einem Backslash beginnen: \l listet Datenbanken auf, \c verbindet mit einer, \dt listet Tabellen auf, \d users beschreibt eine Tabelle und \q beendet psql. Sie laufen in psql selbst, brauchen deshalb kein Semikolon und funktionieren nicht in anderen Clients.
Wie liste ich alle Tabellen in PostgreSQL auf?
Tippe in psql \dt (oder \dt *.* für alle Schemas). Per SQL, aus jedem Client: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
Unterscheidet sich das SQL von PostgreSQL von MySQL?
Der Kern (SELECT, JOIN, GROUP BY) ist gleich. PostgreSQL ergänzt ILIKE, Casts mit ::, RETURNING, DISTINCT ON, Upserts mit ON CONFLICT, Arrays und JSONB und verwendet Identity-Spalten, wo MySQL AUTO_INCREMENT nutzt.
Was ist der Unterschied zwischen JSON und JSONB?
json speichert den Text so, wie er geschrieben wurde; jsonb speichert eine geparste Binärform, verwirft doppelte Schlüssel und Leerzeichen und unterstützt Indizes (GIN) und Operatoren wie @> und ?. Nimm jsonb, außer du brauchst genau den Originaltext.
Kann ich diese Abfragen online üben?
Ja. Der PostgreSQL-Playground führt jede Abfrage auf einer echten PostgreSQL-17-Datenbank in deinem Browser aus, mit Beispieltabellen, die du bearbeiten kannst. Anweisungen, die Serverrechte brauchen, wie CREATE DATABASE oder CREATE ROLE, erfordern dein eigenes PostgreSQL.
Ist dieser PostgreSQL-Spickzettel kostenlos?

Ja. Du kannst ihn kostenlos lesen, kopieren und ausdrucken, ohne Anmeldung.

Illustration der Programmiersprachen bei Coddy

Lerne PostgreSQL mit Coddy

Los geht's