A legnagyobb egység az adatbázis manager, ami egy adott porton fut.
Lehetséges hogy egy gépen a 9-es verziójú adatbázis manager az 5432-es porton,
a 12-es verziójú adatbázis manager pedig az 5433-as porton fut.
Egy adatbázis manager telepítésekor rögtön létrejön egy postgres role,
és egy postgres adatbázis a manager-en belül.
Egy adatbázis manager-en belül vannak a role-ok, melyek nem az adatbázishoz kötődnek !
A role nevével és jelszavával lehet bejelentkezni az adatbázisokhoz.
Egy adatbázis manager-en belül vannak az adatbázis-ok.
Egy adatbázison belül vannak a sémák.
Telepítés MacOS-re:
Erről a címről kell
letölteni
a MacOS-hez tartozó ZIP fájlt.
A ZIP-et kicsomagolva keletkezik egy "app" kiterjesztésű fájl, azt kell elindítani. A beállítások:
usernév: postgres
password: (bejegyezni a Hidden-be!)
port: 5433 (ha az 5432-t már foglalja a régi postgres)
locale: hu_HU.UTF-8
Fontos SQL-parancsok:
Adatbázisok listája:
Adatbázisok kapcsolatok listája, az aktuális adatbázishoz:
Role-ok listája:
Sémák listája az aktuális adatbázisban:
Adatbázis létrehozása
"demo" adatbázis létrehozása, melynek tulajdonosa a "xesj" role:
Adatbázis törlése
"demo" adatbázis törlése:
adatbázis export
A pg_dump programmal exportot (mentést) tudunk készíteni egy adatbázisról, vagy az adatbázis egy sémájáról.
Fontos hogy SUPERUSER jogú legyen a mentést végző role,
hiszen minden adatbázis objektumhoz hozzá kell férnie !
Ha több adatbázis manager fut egy gépen, akkor nem az dönt melyik pg_dump programot indítjuk,
hanem hogy melyik manager portját szólítjuk meg. Ha nem adjuk meg a port számot, akkor a default az 5432.
Export készítése az "eles" adatbázisról, melyet "postgres" role-lal hajtjuk végre:
Export készítése az 5433-as porton futó adatbázis manager "fejleszto" adatbázisának "gepard" sémájáról,
melyet "postgres" role-lal hajtunk végre:
A psql programmal vissza tudunk állítani egy adatbázist, vagy az adatbázis sémáit.
Visszaállításra azokat a fájlokat tudjuk használni, melyeket a pg_dump program készített.
Az import előtt:
Az adatbázisnak (melybe importálunk) LÉTEZNIE KELL!
Az összes role-nak LÉTEZNIE KELL!
TILOS LÉTEZNIE az importálandó sémáknak!
Fontos hogy SUPERUSER jogú legyen az importálást végző role,
hiszen minden adatbázis objektumhoz hozzá kell férnie !
Importálni nemcsak abba az adatbázisba lehet, melyből exportáltunk.
Tehát ha rendszeresen exportáljuk az éles adatbázist, és meg akarunk győződni hogy az exportálás helyes,
akkor importáljuk be egy másik próba adatbázisba.
Ha az import során hibaüzenetet kapunk, töröljük az adatbázist, hozzuk létre újra,
majd ismételjük a visszaállítás műveletét !
Ha csak sémákat importálunk, akkor azokat töröljük.
Importálás az "eles" adatbázisba, "postgres" role-lal végezve a műveletet,
és előírjuk, hogy hiba esetén álljon meg a végrehajtás:
Importálás az 5433-as porton futó adatbázis manager "teszt" adatbázisába,
"postgres" role-lal végezve a műveletet,
és előírjuk, hogy hiba esetén álljon meg a végrehajtás:
adatbázis link
Az adatbázis linket a dblink() függvény biztosítja.
Ha ez a függvény nincs telepítve, akkor ezt kell végrehajtani:
A dblink() függvénynek 2 paramétere van:
A távoli adatbázis kapcsolat leírása.
A távoli adatbázis kapcsolaton végrehajtandó SQL-parancs.
Ha ez nem fix, hanem paramétere is van, akkor érdemes az SQL-parancsot a format() függvénnyel előállítani
szöveg összefűzés helyett.
Példa: a helyi adatbázis osszead(x,y) függvénye a távoli adatbázis osszead(x,y) függvényét hívja meg:
DbVisualizer Pro:
MacOS-en, linuxon és windows-on is működik, de fizetős.
Csak a fizetős Pro változatát érdemes használni,
sajnos a free-változatban még az eljárások forráskódja sem látható.
SQL Workbench:
Ingyenes, MacOS-en, linuxon és windows-on is működik.
date
Dátum, időpont nélkül, tehát csak évet, hónapot, és napot tartalmaz, lásd: date
bytea
Bájtsorozat tárolása
case
Egy kifejezés értékének átkonvertálása más értékre.
A case formátuma tetszőleges feltételhez:
Az első igaz feltételhez tartozó eredménnyel tér vissza
Ha nincs igaz feltétel, akkor az ELSE eredményével tér vissza
Ha nincs igaz feltétel, és ELSE sincs, akkor null-t ad vissza
A case formátuma egyenlőséghez:
Az első olyan eredménnyel tér vissza, ahol a kifejezés = érték
Ha sehol sem egyenlő a kifejezés az értékkel, akkor az ELSE eredményével tér vissza
Ha sehol sem egyenlő a kifejezés az értékkel, és ELSE sincs, akkor null-t ad vissza
cast()
A cast() függvény típus kényszerítésre szolgál.
Akkor használjuk, ha egy adat típusát direkt meg szeretnénk változtatni, például számról string-re.
Haználjuk akkor is, amikor egy adat lehet null, és emiatt a postgres nem tudná meghatározni a típusát,
és hibát jelezne.
Például a WHERE :1 is null nem megfelelő kifejezés ha a java jdbc-t használjuk és a paraméter null,
ugyanis a postgres nem tudja az adattípust.
Ezért a cast() függvénnyel definiálni kell az adat típusát, de ez csak szűrőfeltételek esetén szükséges:
A cast()-nak van egy rövidebben leírható formája is, melyben a kifejezés után kell írni a típus kényszerítést
dupla kettősponttal, például:
coalesce()
Az Oracle nvl()-hez hasonló függvény.
Azt a legelőszőr felsorolt kifejezés értéket adja vissza amelyik nem null. Ha mindegyik null akkor null-t ad vissza:
date
date: dátum, időpont nélkül, tehát csak évet, hónapot, és napot tartalmaz.
Adatbázis kapcsolat felépítése Java-ból a DriverManager osztállyal.
Kapcsolódás a lokális gép 5432-es porton futó adatbázis manager "main_db" adatbázisához
"teszt_role" role-lal, "t34" jelszóval:
job
Postgres-ben nincs lehetőség időzített módon adatbázis függvényeket futtatni.
jogok beállítása
A "main" adatbázisban séma létrehozási jog kiosztása a "teszt" role-nak:
join
A postgres nem ismeri az outer join (+) jellel történő megadási formáját.
Ezért a join művelet 4 lehetséges formája: JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN. Ezek a join műveletek az Oracle alatt is működnek!
JOIN esetén inner join történik, vagyis csak a kapcsolódó sorok jelennek meg.
LEFT JOIN esetén a bal oldalon lévő tábla minden sora megjelenik.
RIGHT JOIN esetén a jobb oldalon lévő tábla minden sora megjelenik.
FULL JOIN esetén a bal, és jobb oldalon lévő tábla minden sora megjelenik.
A JOIN után meg kell adni ON-nal a kapcsolódás feltételét.
Például, minden személy megjelenjen, de mellettük csak tulajdonukban lévő gmail-es címeik:
Nem mindegy hogy egy feltételt az ON vagy WHERE után írunk.
Ugyanis az ON feltétel a join-t vezérli, így tehát egy LEFT JOIN esetén akármi van az ON feltételnél,
a bal oldali tábla minden sora biztos megjelenik (hacsak egy where feltétel a végén le nem szűri).
Azonban amit a WHERE-be írunk az már a kész join művelet után fut, és vele bármilyen sor eltüntethető.
Például, így meg sem jelenik olyan személy akinek nincs gmail-es címe:
limit & offset
A LIMIT megadja maximum hány sort kapjunk vissza, az OFFSET pedig hogy honnan kezdje (0-val indul).
ORDER BY használatával működik helyesen, hiszen ekkor van értelme megmondani
az elemek helyét az OFFSET és LIMIT által.
Például, az első 2 rekord kihagyása után maximum 5 rekordot kérünk:
materializált view
Materializált view létrehozása:
Materializált view frissítése:
order by
MacOS-en, UTF-8 kódkészlet használatakor nem megfelelő a magyar sorbarendezés!
A sorbarendezést az LC_COLLATE beállítás szabályozza.
A show all SQL-paranccsal ki lehet íratni a beállításokat,
de hiába hu_HU.UTF-8 az LC_COLLATE beállítás a magyar ékezetes karakterek a sor végére kerülnek,
sőt a nagybetűk is előrébb vannak mint a kisbetűk ! A hibát így lehet kitesztelni:
Megoldás:
Legalább 10-es verziójú postgres-t kell használni, melyben már van ICU támogatás.
Az ICU támogatás ellenőrzése, melynek eredményében találni kell "icu" jelölést:
Az "icu" jelölést felhasználva ki kell egészíteni az SQL-select ORDER BY részét
COLLATE-tel ott ahol az oszlop szöveges:
Nem lehet mindenhol az ORDER BY-t kiegészíteni, például ahol SELECT DISTINCT van.
Ott ez a módszer alkalmazható:
Arra nem találtam megoldást, hogy az adatbázis létrehozásakor meg lehessen adni a "hu-x-icu" beállítást default-nak,
hogy ne kelljen az SQL-parancsokban beírni a collate részt. Ez a parancs invalid locale name: hu-x-icu
hibát ír:
package
Package nem létezik, a függvények teljesen önállóak, nem lehet őket összevonni egy csomagba.
pg_hba.conf
A pg_hba.conf fájlban állítható be, hogy milyen IP-címről van engedélyezve a bejelentkezés az adatbázishoz.
A pg_hba.conf fájl helyének kiiratása:
Új IP-cím (új sor) hozzáadása a pg_hba.conf fájlhoz:
Ezután nem szükséges az adatbázist újraindítani, hogy a módosítás érvényre jusson,
hanem csak reload kell, például SQL-parancsként:
vagy unix shell-ből:
A reload nem szakítja meg az adatbázis kapcsolatokat, vagy a futó lekérdezéseket,
tehát szinte észrevétlen marad az adatbázis felhasználók számára.
pl/pgsql
Az adatbázisban csak function létezik, procedure nem, de nem kötelező hogy a function értéket adjon vissza.
Ha a function nem ad vissza értéket akkor RETURNS void szerepel a fejrészében.
A function nem kezelhet tranzakciót, tehát nem lehet benne commit vagy rollback.
A tranzakció kezelésről a függvényt hívónak kell gondoskodni.
A function átalános formája:
$$
Ha a fenti formában definiáljuk a függvényt, akkor a "$$" szövegnek sehol sem szabad szerepelni a
DECLARE - END blokkon belül.
Ha mégis szükség van rá, akkor a kezdő, és záró "$$"-t másra kell kicserélni, ami nem fordul elő,
például: "$function$", vagy "$kisnyul$".
Futás közben keletkező hiba sorának megtalálása
A hibaüzenet tartalmazza, hogy a function melyik sorában keletkezett a hiba.
Viszont figyelembe kell venni, hogy a function forráskódjában a "$$"-ral jelzett sor számít az 1. sornak!
psql
A psql programmal karakteres felületen adhatunk ki SQL parancsokat.
Belépés a "main" adatbázishoz "xesj" role-lal:
Kilépés: \q
SQL-script futtatása, például:
raise
A raise exception paranccsal exception-t lehet kiváltani egy pl/pgsql blokk-ban.
A raise más formája, például a raise notice nem vált ki exception-t, logoláshoz hasznos.
Példák:
Az errcode értékének 5 karakternek kell lennie, melyből az első karakter betű vagy számjegy,
majd ezt 4 számjegynek kell követnie.
role
Role létrehozása
Meg lehet adni a role-hoz tartozó jogosultságokat is, például hogy be is tudjon jelentkezni:
Role törlése
Fontos, hogy ennél a műveletnél postgres role-lal legyünk bejelentkezve postgres adatbázishoz,
különben lehet, hogy nem tudjuk a role-t törölni.
Role módosítása
Lehet változtatni a jogosultságain, vagy például a jelszaván:
row_number()
A SELECT-tel lekérdezett eredmény sorokat megszámozza 1-től kezdődően.
Megadható az is, hogy milyen sorrend alapján számozzon, melynek nem kell megegyeznie az ORDER BY sorrendjével:
Ha egy SELECT nem tartalmaz ORDER BY-t, de a benne lévő ROW_NUMBER OVER() része tartalmaz, akkor
az eredmény sorrendjét az OVER() határozza meg.
Tehát így is le lehet kérdezni felhasználókat név szerint fordított ABC rendben:
eredménye:
search path
Ha az SQL-parancsban nem írjuk ki a séma nevét, akkor a search_path beállítás dönti el,
hogy az adatbázis hol keresse a hivatkozott adatbázis objektumot.
A default érték a bejelentkezett role nevével azonos séma, és a "public" séma:
A search_path értéke módosítható:
Az aktuális search_path kiiratása:
szekvencia
Szekvencia létrehozása:
Az "idseq" szekvenciából a következő még nem kiadott érték lekérése:
Az "idseq" szekvenciából utoljára már kiadott érték lekérése:
timestamp
timestamp: dátum + idő ezredmásodperc pontossággal.
Ha nem szeretnénk, hogy a timestamp konvertálódjon az időzónától függően, akkor így hozzuk létre az adatbázisban
a tábla oszlopot:
A tranzakció kezdet timestamp-je: Ez az érték nem változik a tranzakció alatt, tehát nem az aktuális időpont!
Ugyanezt az értéket szolgáltatja a localtimestamp és a current_timestamp is !
Az aktuális timestamp:
Konstans értékek:
Timestamp eltolása interval segítségével:
Két timestamp különbsége interval adattípus. Ha az egyik timestamp null, akkor az eredmény is null.
Tranzakció izoláció
A tranzakció izolációs szint akkor jó, ha más tranzakciók műveleteit nem látjuk
amíg azok folyamatban vannak, viszont rögtön látjuk ha azok commit-álva lettek.
Ez a jó szint, mely egyben a default is:
Az izolációs szint kiiratása:
View-k esetében meg lehet csinálni, hogy látszólag nem látják más tranzakciók eredményét read committed szinten
akkor sem ha azok commit-álva lettek. Ha a view hülyén van megalkotva és belül használ current_timestamp-et,
mely nem változik a tranzakció alatt, és így a valótlan időt where-be rakva nem mutatja meg az adatot, mely
a mögötte lévő táblában már rég ott van :)
Ha egy tranzakció közben hiba lép fel (pl. hibás SQL-insert), akkor az ezután következő többi helyes
parancs sem tud már lefutni ebben a hibás tranzakcióban.
A kapott hibaüzenet:
Ekkor a hibás tranzakciót le kell zárni commit-tal vagy rollback-kel, hogy a további SQL-parancsok lefuthassanak.
trigger
A trigger definíció nem tartalmazza a forráskódját, csak egy trigger-függvény -re hivatkozik.
A trigger-függvény az old és new kulcsszavakkal hivatkozik a rekord régi, és új értékére.
Az old és new előtt nincs kettőspont.
A trigger-függvény nem kap paramétert, visszatérési értéke mindig "RETURNS trigger".
A belsejében kötelező ezzel visszatérnie: "return new".
A trigger, és a hozzátartozó trigger-függvény neve megegyezhet, sőt ajánlott,
így egyértelmű a megfeleltetés.