PostgreSQL a základy

PostgreSQL

Open-source databázový systém. Pokrývá většinu klíčových vlastností SQL. Zkušenosti přenositelné i na jiné databázové systémy (Microsoft SQL Server, Oracle database, …). Více na jejich webu, zejména se podívejte na dokumentaci.

K dispozici na serveru database.inf.upol.cz. Přístup přes SSH - loginy a hesla v emailu. Jako první si po přihlášení změňte heslo - každý zodpovídá za zabezpečení svého účtu!

Připojení na server

Server database.inf.upol.cz.

Připojte se přes SSH nebo putty. Login jako do portálu, heslo jste obdrželi mailem. Heslo si ihned po přihlášení změňte!

K dispozici je standardní sada nástrojů. Pokud něco chybí, ozvěte se.

Každý má k dispozici svou databázi (jméno stejné jako login) a k tomu je společná databáze shared. V databázi shared můžete dělat cokoliv, co chcete. Stejně jako všichni ostatní, tedy ji nepoužívejte pro důležité věci, ale na experimentování. Do vaší databáze můžete jen vy.

PSQL

Klient k databázi PostgreSQL s konzolovým rozhraním se nazývá psql. Využijete je pro správu databáze v prostředí serveru. Login do databáze je stejný jako do systému, výchozí heslo taktéž. I toto heslo si ihned změňte!

Důležité příkazy PSQL

Klávesové zkratky:

• TAB – doplnění klíčového slova nebo identifikátoru
• 2x TAB – seznam možností, které lze doplnit
• šipky nahoru/dolů – procházení historie příkazů
• Ctrl+R – prohledávání historie
• Ctrl+C – zrušení aktuálně zadávaného příkazu

Interní příkazy:

• \? – zobrazení nápovědy
• \password - změna hesla
• \e – spustí externí editor (vhodné pro psaní delších SQL dotazů)
• \i soubor – načte SQL příkazy ze souboru a provede je
• \d – vypíše všechny tabulky, pohledy, indexy (viz nápověda)
• \d objekt – vypíše podrobnosti o daném objektu (např. tabulce)
• \c databaze – přihlášení do jiné databáze
• \q – ukončení klienta

Dostanete-li se do interaktivního módu, kdy místo promptu můžete procházet nějaký soubor/text (např. po použití \? ), tak jej můžete opustit stiskem q.

Budete-li v psql používat externí textový editor a neznáte žádnou z nabízených možností, tak vyberte nano. Je nejjednodušší na ovládání.

VS code s doplňky (či jiný editor)

Chcete-li se vyhnout pravidelnému používání terminálu a psql, lze se na server připojit i z různých editorů a pracovat vzdáleně. Nejsnazší je asi VS code s vhodným doplňkem pro správu PostgreSQL, či JetBrains DataGrip, na který byste měli mít dostupnou studentskou licenci. Používání vhodného editou pro vzdálenou práci většině studentů vyhovuje více. Přesto doporučuji alespoň jednou strávit nějaký čas i v serverovém prostředí a nástroji psql.

Napojení na databázi z programu

K databázi se samozřejmě lze připojit i přímo z nějakého námi vytvářeného programu. Snad každý dostatečně zavedený jazyk má nějakou knihovnu umožňující pohodlnou práci s PostgreSQL.

• Server: database.inf.upol.cz:5432
• Login, databáze a heslo stejně jako při přímém přístupu.

Práce s PostgreSQL

Základní ukázka a komentáře na přednášce, zde jen shrnutí.

Datové typy

• smallint – 2 bytové celé číslo se znaménkem (-32768 až +32767)
• integer nebo int – 4 bytové celé číslo se znaménkem (-2147483648 až +2147483647)
• bigint – 8 bytové celé číslo se znaménkem (-9223372036854775808 až 9223372036854775807)
• NUMERIC(cifry,desetinne-cifry) – čísla s přesně definovanou přesností. cifry = celkový počet cifer v daném čísle (před i za desetinnou čárkou), desetinne-cifry = počet cifer za desetinnou čárkou.
• real, double precision – float a double známý z jazyka C
• varchar(n) – řetězec proměnlivé velikosti mající maximálně n znaků
• char(n) – řetězec pevné velikosti mající právě n znaků (chybějící znaky jsou doplněny mezerami)
• text – text libovolné velikosti (specifikum PostgreSQL)
• boolean – dvouhodnotový datový typ
• date, time, timestamp – datové typy pro reprezentaci data, času a data+času

Pokud není uvedeno jinak, tak každý sloupec může obsahovat ještě hodnotu NULL reprezentující neexistující hodnotu. Více se dozvíte na přednáškách.

Více k typům v PostgreSQL zde.

Příkazy CREATE TABLE, ALTER TABLE, INSERT INTO, DROP TABLE

Příkazy pro základní manipulaci s tabulkovými proměnnými - vytváření, plnění daty, mazání.

V dokumentaci je vždy sekce s příklady použití.

Dokumentace ke CREATE TABLE.
Dokumentace ke ALTER TABLE.
Dokumentace k INSERT INTO.
Dokumentace k DROP TABLE.

Seznam všech příkazů PostgreSQL.

Problémy na které možná narazíte:

• Kolem řetězců patří apostrofy ’ ne uvozovky “.
• Po změně hesla se z psql odhlaste a znovu přihlaste, jinak se nepůjde připojit k jiné databázi.
• Formáty data v typu date zde.
• Základní příkazy v Linuxové příkazové řádce:
  • ls - vypíše obsah adresáře
  • cd arg - změní adresář na arg
  • nano soubor - otevře textový soubor v editoru nano
  • rm soubor - smaže soubor
  • mv soubor kam - přesune/přejmenuje soubor
  • cp soubor kam - zkopíruje soubor
  • logout - odhlásí se z relace
  • less soubor - zobrazí textový soubor pro čtení
  • passwd - změnit heslo

Všechny příkazy mají přepínač --help (tj. např ls --help), který ukáže nápovědu k danému příkazu.

Pokud je výstup nějakého příkazu moc dlouhý a nevleze se na obrazovku, můžete přidat | less na konec příkazů (např ls --help | less). Poté se lze výstupem interaktivně pohybovat. Interaktivní mód opustíte stiskem q.

Něco chybí? Ozvěte se!

Úkoly k procvičení

1. Vytvořte si tabulku pro jednoduchý seznam zaměstnanců obsahující následující informace:
   • jméno
   • příjmení
   • datum narození
   • datum nástupu do zaměstnání
   • číslo kanceláře
   • email
   • web
   • počet dětí
   • pohlaví
2. Předchozí tabulku naplňte alespoň pěti záznamy.
3. Zkuste se připojit k databázi některého kolegy.
4. Vytvořte nějakou tabulku v databázi shared.
5. Vypište si seznam všech tabulek databáze shared.
6. Přidejte nějaké záznamy do nějaké tabulky v databázi shared.