alapok dokumentáció adatbázis adatbázis export adatbázis import adatbázis link adatbázis tool cast coalesce date jdbc driver jogok beállítása join limit & offset order by pg_hba.conf

psql raise role search path szekvencia timestamp tranzakció

• 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:
• Sorok számozása plpgsql blokk esetén:
  Ha exception keletkezik egy blokkban, akkor megkapjuk a hibát okozó sor sorszámát is, ahol az 1-es sor = $function$ sora!

• tutorial
• manuals
• postgresql.org

• 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:

• 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:
• Adatbázis exportáló python script

• 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:

• 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:
  1. A távoli adatbázis kapcsolat leírása.
  2. 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:

• DBeaver (community edition):
  Ez a legjobb! MacOS-en, linuxon és windows-on is működik, ingyenes.
• pgAdmin:
  Ezt az adatbáziskezelőhöz kapjuk.
• 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.
• SQL manager:
  Van ingyenes "lite" verziója.

• 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:

• 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:

• Aktuális dátum lekérdezése:
• Hány naposak vagyunk (1-től indul):

• JDBC driver letöltése
  A driver függ az adatbázis verziótól, és a java verziótól.
• 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:

• A "main" adatbázisban séma létrehozási jog kiosztása a "teszt" role-nak:

• 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:

• 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:

• 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:

• A pg_hba.conf fájlban állítható be, hogy milyen IP-címről van engedélyezve a bejelentkezés az adatbázishoz.
• Ú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.

• 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:

• A raise exception paranccsal exception-t lehet kiváltani egy plpgsql 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 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:

• 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:

• 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:
  

• Az aktuális dátum + időpont (timestamp) lekérdezése.
  
• A tranzakció kezdet dátumának + időpontjának (timestamp) lekérdezése.
  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 now() is !
• Két timestamp különbsége interval adattípus.
  Az interval adattípusból az extract + epoch + from segítségével lehet kinyerni a köztes időt másodpercben, mely törtszám: 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.