PostgreSQL 12 až 14; základní příkazy pgsql a správa databáze

PostgreSQL (pgsql) je pokročilá open source relační databáze podnikové třídy, která podporuje dotazování v jazyce SQL (relační) i JSON (nerelační). Jedná se o vysoce stabilní systém pro správu databází, za kterým stojí více než 20 let komunitního vývoje, který přispěl k jeho vysoké úrovni odolnosti, integrity a správnosti. Postgresql se používá jako primární datové úložiště nebo datový sklad pro mnoho webových, mobilních, geoprostorových a analytických aplikací. Nejnovější hlavní verzí (podzim 2022) PostgreSQL je verze 12, lze ale upgradovat až na verzi 14.

PostgreSQL příkazy SQL

Vytvoření uživatele (role) s heslem

CREATE ROLE muj_ucet WITH LOGIN CREATEDB PASSWORD 'moje_heslo';

nebo se zašifrovaným heslem

CREATE ROLE muj_ucet with LOGIN ENCRYPTED PASSWORD 'moje_heslo';

Vytvoření databáze a přiřazení vlastníka databáze

CREATE DATABASE moje_databaze WITH OWNER muj_ucet;

Chcete-li vypsat všechny databáze na aktuálním databázovém serveru PostgreSQL, použijte příkaz \l nebo \l+

\l+

Chcete-li vypsat všechny uživatele použijte příkaz \du nebo \du+

\du+

Vypsat velikost databáze lze pomocí příkazu

SELECT pg_size_pretty( pg_database_size('moje_databaze') );

Změna oprávnění pro uživatele muj_ucet na superuser

ALTER USER muj_ucet WITH SUPERUSER;

Změna oprávnění uživatele muj_ucet na standardní oprávnění

ALTER DEFAULT PRIVILEGES FOR USER muj_ucet;

Ukončení relace (připojení k databáze)

\q

Zobrazit historii příkazů, použijte příkaz

\s

Jakmile jste připojeni k databázi, můžete přepnout připojení na novou databázi pod uživatelem zadaným uživatelem. Předchozí připojení bude uzavřeno. Pokud parametr user vynecháte, předpokládá se aktuální uživatel.

\c muj_ucet

Připojit k databáze a vypsat všechny tabulky

\c moje_databaze \dt

Vyprázdnit obsah tabulek v databáze moje_databáze včetně navazujících tabulek

TRUNCATE TABLE moje_databaze_entries CASCADE;

Reset poslední aktualizace v budoucnosti = neaktualizuje se aplikace Tiny Tiny RSS

\c ttrss
TRUNCATE TABLE ttrss_feed_categories CASCADE;

Vytvořit tabulku (extension) v databáze

CREATE EXTENSION pg_trgm;

PostgreSQL příkazy pgsql

Hlavní
\copyright	zobrazí podmínky použití a distribuce PostgreSQL
\crosstabview [SLOUPCE]	spustí dotaz a zobrazí výsledek přes crosstab
\errverbose	zobrazí polední chybovou hlášku s maximem podrobností
\g [(VOLBA)] [SOUBOR]	provést dotaz (a odeslat výsledky do souboru nebo |pipe);
\g [BEZ VOLBY]	bez argumentů je ekvivalentní středníku
\gdesc	popíše výsledek dotazu, bez spuštění
\gexec	spustí dotaz, poté spustí každou hodnotu z jeho výsledku
\gset [PREFIX]	spustí dotaz a uloží výsledky v psql proměnných
\gx [(VOLBY)] [SOUBOR]	jako \g, ale vynucuje rozšířený mód výstupu
\q	ukončí psql
\watch [SEKUND]	každých SEKUND spusť dotaz
Nápověda
\? [příkaz]	zobrazí nápovědu k interním příkazům
\? volba	zobrazí nápovědu k psql parametrům psql pro příkazovou řádku
\? proměnná	zobrazí nápovědu ke speciálním proměnným
\h [JMÉNO]	nápověda syntaxe SQL příkazů, * pro všechny příkazy
Paměť dotazu
\e [SOUBOR] [ŘÁDEK]	editace aktuálního dotazu (nebo souboru) v externím editoru
\ef [JMENO FUNKCE [ŘÁDEK]]	editace definice funkce v externím editoru
\ev [ZOBRAZITJMÉNO [ŘÁDEK]]	editace definice pohledu v externím editoru
\p	ukázat současný obsah paměti s dotazem
\r	vyprázdnění paměti s dotazy
\s [SOUBOR]vytiskne historii nebo ji uloží do souboru	vytiskne historii nebo ji uloží do souboru
\w SOUBOR	zapsání paměti s dotazem do souboru
Vstup/Výstup
\copy …	provede SQL COPY s tokem dat na klienta
\echo [-n] [ŘETĚZEC]	vypsání textu na standardní výstup (-n pro potlačení nového řádku)
\i SOUBOR	provedení příkazů ze souboru
\ir SOUBOR	jako \i, ale relativně k pozici v aktuálním skriptu
\o [SOUBOR]	přesměrování výsledků dotazu do souboru nebo |roury
\qecho [ŘETĚZEC]	vypsání textu na \o výstup dotazů (-n pro potlačení nového řádku)
\warn [-n] [TEXT]	vypsání textu na standardní výstup (-n pro potlačení nového řádku)
Podmínka
\if VÝRAZ	začne podmíněný blok
\elif VÝRAZ	alternativa v současném podmíněném bloku
\else	poslední alternativa v současném podmíněném bloku
\endif	ukončí podmíněný blok
Informační	(volby: S = zobraz systémové objekty, + = další detaily)
\d[S+]	seznam tabulek, pohledů a sekvencí
\d[S+] JMÉNO	popis tabulky, pohledů, sekvence nebo indexu
\da[S] [VZOR]	seznam agregačních funkcí
\dA[+] [VZOR]	seznam přístupových metod
\dAc[+] [AMPTRN [TYPEPTRN]]	vypíše třídy operátorů
\dAf[+] [AMPTRN [TYPEPTRN]]	vypíše rodiny operátorů
\dAo[+] [AMPTRN [OPFPTRN]]	vypíše operátory pro rodiny operátorů
\dAp[+] [AMPTRN [OPFPTRN]]	seznam podpůrných funkcí rodin operátorů
\db[+] [VZOR]	seznam prostory tabulek
\dc[S+] [VZOR]	seznam konverzí
\dC[+] [VZOR]	seznam přetypování
\dd[S] [VZOR]	zobrazí popis objektů nezobrazených jinde
\dD[S+] [VZOR]	seznam domén
\ddp [VZOR]	seznam implicitních privilegií
\dE[S+] [VZOR]	seznam foreign tabulek
\des[+] [VZOR]	seznam foreign serverů
\det[+] [VZOR]	seznam foreign tabulek
\deu[+] [VZOR]	seznam mapování uživatelů
\dew[+] [VZOR]	seznam foreign-data wrapperů
\df[anptw][S+] [FUNCPTRN [TYPEPTRN …]]	seznam [pouze funkce agg/normal/procedura/spuštěč/okno]
\dF[+] [VZOR]	seznam konfigurací fulltextového vyhledávání
\dFd[+] [VZOR]	seznam slovníků fulltextového vyhledávání
\dFp[+] [VZOR]	seznam parserů fulltextového vyhledávání
\dFt[+] [VZOR]	seznam šablon fulltextového vyhledávání
\dg[S+] [VZOR]	seznam rolí
\di[S+] [VZOR]	seznam indexů
\dl	seznam „large object“ stejné jako \lo_list
\dL[S+] [VZOR]	seznam procedurálních jazyků
\dm[S+] [VZOR]	seznam materializovaných pohledů
\dn[S+] [VZOR]	seznam schémat
\do[S+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]	seznam operátorů
\dO[S+] [VZOR]	seznam collations
\dp [VZOR]	seznam přístupových práv tabulek, pohledů a sekvencí
\dP[itn+] [VZOR]	seznam [pouze index/table] partitioned relations [n=nested]
\drds [ROLEPTRN [DBPTRN]]	seznam nastavení rolí pro jednotlivé databáze
\dRp[+] [VZOR]	seznam replikačních publikací
\dRs[+] [PATTERN]	seznam replikačních subskripcí
\ds[S+] [VZOR]	seznam sekvencí
\dt[S+] [VZOR]	seznam tabulek
\dT[S+] [VZOR]	seznam datových typů
\du[S+] [VZOR]	seznam rolí
\dv[S+] [VZOR]	seznam pohledů
\dx[+] [VZOR]	seznam rozšíření
\dX [VZOR]	seznam rozšířených statistik
\dy[+] [VZOR]	seznam spouštěčů událostí
\l[+] [VZOR]	seznam databází
\sf[+] FUNCNAME	zobrazí definici funkce
\sv[+] VIEWNAME	zobrazí definici pohledu
\z [VZOR]	stejné jako \dp
Formátování
\a	přepíná mezi ‚nezarovnaným‘ a ‚zarovnaným‘ modem výstupu
\C [ŘETĚZEC]	nastaví titulek tabulky nebo odnastaví pokud není definován řetězec
\f [ŘETĚZEC]	nastaví nebo zobrazí oddělovače polí pro nezarovnaný výstup dotazů
\H	zapne HTML mód výstupu (nyní vypnuto)
\pset [JMÉNO [HODNOTA]]	nastavit možnost výstupu tabulky
	(border|columns|csv_fieldsep|expanded|fieldsep|
	fieldsep_zero|footer|format|linestyle|null|
	numericlocale|pager|pager_min_lines|recordsep|
	recordsep_zero|tableattr|title|tuples_only|
	unicode_border_linestyle|unicode_column_linestyle|
	unicode_header_linestyle)
\t [on|off]	ukazovat pouze řádky (nyní vypnuto)
\T [ŘETĚZEC]	nastavení atributů HTML tagu <table>
\x [on|off|auto]	zapne rozšířený mód výstupu (nyní vypnuto)
Spojení
\c[onnect] [DATABÁZE|- UŽIVATEL|- HOST|- PORT|-] | conninfo]	připojí se do nové databáze (současná „postgres“)
\conninfo	zobrazí informace o aktuálním spojení
\encoding [KÓDOVÁNÍ]	zobrazení nebo nastavení kódování klienta
\password [UŽIVATEL]	bezpečná změna hesla uživatele
Operační systém
\cd [ADRESÁŘ]	změna aktuálního pracovního adresář
\setenv NAME [HODNOTA]	nastaví nebo zruší proměnnou prostředí
\timing [on|off]	použít sledování času u příkazů (nyní vypnuto)
\! [PŘÍKAZ]	provedení příkazu v shellu nebo nastartuje interaktivní shell
Proměnné
\prompt [TEXT] PROMĚNÁ	vyzve uživatele, aby zadal hodnotu proměnné nastavení interní
\set [PROMĚNÁ [HODNOTA]]	proměnné nebo bez parametrů zobrazí seznam všech proměnných
\unset JMÉNO	zrušení interní proměnné
Velké objekty (LO)
\lo_export LOBOID SOUBOR
\lo_import SOUBOR [KOMENTÁŘ]
\lo_list
\lo_unlink LOBOID	operace s „large“ objekty

PostgreSQL seznam SQL příkazů

ABORT	ALTER TEXT SEARCH TEMPLATE	CREATE PUBLICATION	DROP FUNCTION	IMPORT FOREIGN SCHEMA
ALTER AGGREGATE	ALTER TRIGGER	CREATE ROLE	DROP GROUP	INSERT
ALTER COLLATION	ALTER TYPE	CREATE RULE	DROP INDEX	LISTEN
ALTER CONVERSION	ALTER USER	CREATE SCHEMA	DROP LANGUAGE	LOAD
ALTER DATABASE	ALTER USER MAPPING	CREATE SEQUENCE	DROP MATERIALIZED VIEW	LOCK
ALTER DEFAULT PRIVILEGES	ALTER VIEW	CREATE SERVER	DROP OPERATOR	MOVE
ALTER DOMAIN	ANALYZE	CREATE STATISTICS	DROP OPERATOR CLASS	NOTIFY
ALTER EVENT TRIGGER	BEGIN	CREATE SUBSCRIPTION	DROP OPERATOR FAMILY	PREPARE
ALTER EXTENSION	CALL	CREATE TABLE	DROP OWNED	PREPARE TRANSACTION
ALTER FOREIGN DATA WRAPPER	CHECKPOINT	CREATE TABLE AS	DROP POLICY	REASSIGN OWNED
ALTER FOREIGN TABLE	CLOSE	CREATE TABLESPACE	DROP PROCEDURE	REFRESH MATERIALIZED VIEW
ALTER FUNCTION	CLUSTER	CREATE TEXT SEARCH CONFIGURATION	DROP PUBLICATION	REINDEX
ALTER GROUP	COMMENT	CREATE TEXT SEARCH DICTIONARY	DROP ROLE	RELEASE SAVEPOINT
ALTER INDEX	COMMIT	CREATE TEXT SEARCH PARSER	DROP ROUTINE	RESET
ALTER LANGUAGE	COMMIT PREPARED	CREATE TEXT SEARCH TEMPLATE	DROP RULE	REVOKE
ALTER LARGE OBJECT	COPY	CREATE TRANSFORM	DROP SCHEMA	ROLLBACK
ALTER MATERIALIZED VIEW	CREATE ACCESS METHOD	CREATE TRIGGER	DROP SEQUENCE	ROLLBACK PREPARED
ALTER OPERATOR	CREATE AGGREGATE	CREATE TYPE	DROP SERVER	ROLLBACK TO SAVEPOINT
ALTER OPERATOR CLASS	CREATE CAST	CREATE USER	DROP STATISTICS	SAVEPOINT
ALTER OPERATOR FAMILY	CREATE COLLATION	CREATE USER MAPPING	DROP SUBSCRIPTION	SECURITY LABEL
ALTER POLICY	CREATE CONVERSION	CREATE VIEW	DROP TABLE	SELECT
ALTER PROCEDURE	CREATE DATABASE	DEALLOCATE	DROP TABLESPACE	SELECT INTO
ALTER PUBLICATION	CREATE DOMAIN	DECLARE	DROP TEXT SEARCH CONFIGURATION	SET
ALTER ROLE	CREATE EVENT TRIGGER	DELETE	DROP TEXT SEARCH DICTIONARY	SET CONSTTRAINS
ALTER ROUTINE	CREATE EXTENSION	DISCARD	DROP TEXT SEARCH PARSER	SET ROLE
ALTER RULE	CREATE FOREIGN DATA WRAPPER	DO	DROP TEXT SEARCH TEMPLATE	SET SESSION AUTHORIZATION
ALTER SCHEMA	CREATE FOREIGN TABLE	DROP ACCESS METHOD	DROP TRANSFORM	SET TRANSACTION
ALTER SEQUENCE	CREATE FUNCTION	DROP AGGREGATE	DROP TRIGGER	SHOW
ALTER SERVER	CREATE GROUP	DROP CAST	DROP TYPE	START TRANSACTION
ALTER STATISTICS	CREATE INDEX	DROP COLLATION	DROP USER	TABLE
ALTER SUBSCRIPTION	CREATE LANGUAGE	DROP CONVERSION	DROP USER MAPPING	TRUNCATE
ALTER SYSTEM	CREATE MATERIALIZED VIEW	DROP DATABASE	DROP VIEW	UNLISTEN
ALTER TABLE	CREATE OPERATOR	DROP DOMAIN	UPDATE	UPDATE
ALTER TABLESPACE	CREATE OPERATOR CLASS	DROP EVENT TRIGGER	EXECUTE	VACUUM
ALTER TEXT SEARCH CONFIGURATION	CREATE OPERATOR FAMILY	DROP EXTENSION	EXPLAIN	VALUES
ALTER TEXT SEARCH DICTIONARY	CREATE POLICY	DROP FOREIGN DATA WRAPPER	FETCH	WITH
ALTER TEXT SEARCH PARSER	CREATE PROCEDURE	DROP FOREIGN TABLE	GRANT

Užitečné

Návod PostgreSQL + Nginx + Debian 11.04
Domovská stránka projektu pgsql