{"id":13,"date":"2024-04-04T06:16:28","date_gmt":"2024-04-04T03:16:28","guid":{"rendered":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/uhetabelilise-andmebaasi-loomine-postgresqlis\/"},"modified":"2024-04-04T06:16:44","modified_gmt":"2024-04-04T03:16:44","slug":"uhetabelilise-andmebaasi-loomine-postgresqlis","status":"publish","type":"page","link":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/uhetabelilise-andmebaasi-loomine-postgresqlis\/","title":{"rendered":"\u00dchetabelilise andmebaasi loomine PostgreSQLis"},"content":{"rendered":"<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\tAlusta andmebaasihaldustarkvara PostgreSQL installimisest.\r\n<\/p>\r\n\r\n<p>\r\n\t<a data-fid=\"55604\" href=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/postgres_installimisjuhis_0.docx\">PostgreSQL installimisjuhend.<\/a>\r\n<\/p>\r\n\r\n<p>\r\n\tKui arvutisse on server loodud, saab serverisse luua andmebaasi.\r\n<\/p>\r\n\r\n<p>\r\n\tTee paremkl\u00f5ps serveri nimetusel, see peaks meil olema PostgreSQL\u00a0<span>\u2192<\/span> Create <span>\u2192 <\/span>Database\r\n<\/p>\r\n\r\n<p>\r\n\t<span>Pane andmebaasile nimeks Test, muud parameetrid j\u00e4ta nii nagu on. <\/span>\r\n<\/p>\r\n\r\n<p>\r\n\t<img loading=\"lazy\" decoding=\"async\" width=\"699\" height=\"775\" class=\"alignnone wp-image-47\" src=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/create_database.png\" title=\"create_database.png\" alt=\"looab\" srcset=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/create_database.png 699w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/create_database-271x300.png 271w\" sizes=\"auto, (max-width: 699px) 100vw, 699px\">\r\n<\/p>\r\n\r\n<p>\r\n\tAndmebaas ilmub vasakul sirvimise puus serveri all:\r\n<\/p>\r\n\r\n<p>\r\n\t<img loading=\"lazy\" decoding=\"async\" width=\"528\" height=\"417\" class=\"alignnone wp-image-48\" src=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/sirvimispuus.png\" title=\"sirvimispuus.png\" alt=\"sirvimispuu\" srcset=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/sirvimispuus.png 528w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/sirvimispuus-300x237.png 300w\" sizes=\"auto, (max-width: 528px) 100vw, 528px\">\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\tUue andmebaasi loomisega luuakse alati <i>public schema<\/i>. Skeem koondab k\u00f5ik selle andmebaasi objektid, sh tabelid \u00fchte gruppi ja aitab neid loogilisemalt hallata. \u00dcksk\u00f5ik millise andmebaasi objekti loome, kui me t\u00e4psemalt ei m\u00e4\u00e4ratle, pannakse loodud tabelid alati jaotusesse <i>public schema <\/i>alla.\r\n<\/p>\r\n\r\n<p>\r\n\t<img loading=\"lazy\" decoding=\"async\" width=\"526\" height=\"740\" class=\"alignnone wp-image-49\" src=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/tabelpuus.png\" title=\"tabelpuus.png\" alt=\"tabelpuus\" srcset=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/tabelpuus.png 526w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/tabelpuus-213x300.png 213w\" sizes=\"auto, (max-width: 526px) 100vw, 526px\">\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\tTabeli lisamiseks tee paremkl\u00f5ps s\u00f5nal \u201eTables\u201c. Tables <span>\u00ae<\/span> Create <span>\u00ae<\/span> Table\r\n<\/p>\r\n\r\n<p>\r\n\tPane tabelile nimeks \u201eIsik\u201c. Tabelite nimed v\u00f5iksid olla suure t\u00e4hega, v\u00f5imalikult l\u00fchidad ja lihtsad, sest p\u00e4ringute tegemisel on vaja t\u00e4pseid nimesid kasutada. Kui tabeli nimi siiski peaks koosnema kahest s\u00f5nast, lisa s\u00f5nade vahele alumine kriips, nt \u201eIsik_esialgne\u201c.\r\n<\/p>\r\n\r\n<p>\r\n\t\u00c4ra veel salvesta, liigu sakke m\u00f6\u00f6da edasi, sakil Columns saad lisada k\u00f5igi veergude (tunnuste) nimetused ja andmet\u00fc\u00fcbid. Lisa v\u00e4lju plussm\u00e4rgist. Igal tabelil peab olema tunnus v\u00f5i tunnused, mis tagaks, et iga rida on unikaalne. Kui unikaalseid andmev\u00e4lju ei ole (nt nimed, ka perenime ja eesnime kombinatsioon v\u00f5ivad korduda), on otstarbekas teha eraldi tunnus \u201eid\u201c. PostgreSQL, nagu k\u00f5ik teisedki andmebaasihaldustarkvarad lubavad lasta tarkvaral seda v\u00e4lja automaatselt t\u00e4ita. Andmet\u00fc\u00fcbiks peab PostgreSQLis panema selleks \u201eserial\u201c. Postgres pakub ka variante \u201ebigserial\u201c ja \u201esmallserial\u201c, need eristuvad andmebaasis andmev\u00e4lja jaoks reserveeritavate bittide arvu poolest. Kui on teada, et andmebaasis saab olema palju andmeid ja id v\u00f5b kasvada ka 11 kohaliseks, siis tuleks kasutada \u201ebigserial\u201c. Meil on k\u00fcll v\u00e4ike n\u00e4idisandmebaas, aga valime ikkagi \u201eserial\u201c\r\n<\/p>\r\n\r\n<p>\r\n\t<img loading=\"lazy\" decoding=\"async\" width=\"970\" height=\"767\" class=\"alignnone wp-image-50\" src=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/atyybid.png\" title=\"atyybid.png\" alt=\"andmet\u00fc\u00fcbid\" srcset=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/atyybid.png 970w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/atyybid-300x237.png 300w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/atyybid-768x607.png 768w\" sizes=\"auto, (max-width: 970px) 100vw, 970px\">\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\tNot NULL tuleks panna \u201eYes\u201c juhul kui v\u00e4li ei tohi j\u00e4\u00e4da t\u00fchjaks. Primary key kitsenduse (constraint) seamegi sellele \u00fchele v\u00e4ljale, mille j\u00e4rgi tahame ridu\/kirjeid hiljem \u00fcksteisest eristada.\r\n<\/p>\r\n\r\n<p>\r\n\tLisa ka k\u00f5ik teised tunnused, et tekkiks sellised andmebaasi v\u00e4ljad:\r\n<\/p>\r\n\r\n<p>\r\n\t<img loading=\"lazy\" decoding=\"async\" width=\"1334\" height=\"103\" class=\"alignnone wp-image-51\" src=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/abvaljad.png\" title=\"abvaljad.png\" alt=\"AB v\u00e4ljad\" srcset=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/abvaljad.png 1334w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/abvaljad-300x23.png 300w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/abvaljad-1024x79.png 1024w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/abvaljad-768x59.png 768w\" sizes=\"auto, (max-width: 1334px) 100vw, 1334px\">\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\tAndmet\u00fc\u00fcpide selgituseks: <b>character varyin<\/b>g (m\u00f5nes andmebaasis\u00fcsteemis ka varchar) t\u00e4histab erineva pikkusega tekstiv\u00e4lja, v\u00e4lja t\u00e4hem\u00e4rkide arvu peame ise lahtris length\/precision t\u00e4psustama, pere- ja eesnime puhul v\u00f5iks see olla 50 v\u00f5i 100.\r\n<\/p>\r\n\r\n<p>\r\n\t<b>Char<\/b> t\u00e4histab \u00fche t\u00e4hem\u00e4rgi pikkust v\u00e4lja. See sobib\u00a0 sellisel juhul kui tahame sellele v\u00e4ljale m\u00e4rkida kas \u201em\u201c v\u00f5i \u201en\u201c v\u00f5i j\u00e4tta selle v\u00e4lja m\u00f5ne kirje puhul t\u00fchjaks (n\u00e4iteks ei saa allikast aru, kummaga on tegu).\r\n<\/p>\r\n\r\n<p>\r\n\tAndmet\u00fc\u00fcp<b> real<\/b> sobib siis kui sellele v\u00e4ljale on vaja sisestada komakohtadega numbreid, allikates v\u00f5ib olla vanus m\u00e4rgitud sageli ka kujul 34.5, selleks et seda saaks andmebaasi m\u00e4rkida, tuleb andmet\u00fc\u00fcbiks m\u00e4\u00e4rata komaarv. Kui andmet\u00fc\u00fcbiks m\u00e4rkida <b>integer<\/b> (t\u00e4isarv), siis ei saaks sellele v\u00e4ljale enam komaga vanuseid sisestada.\u00a0\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\tV\u00e4lja kirje_lisamisaeg saab samuti automatiseerida, st et andmete sisestamisel v\u00f5ib selle v\u00e4lja t\u00fchjaks j\u00e4tta ja muude v\u00e4ljade t\u00e4itmisel lisatakse see andmebaasi automaatselt. Selleks m\u00e4rgi k\u00f5igepealt selle v\u00e4lja andmet\u00fc\u00fcbiks <b>timestamp with time zone<\/b>. Seej\u00e4rel kl\u00f5psa v\u00e4lja nime ees oleval pliiatsi ikoonil.\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\t<img loading=\"lazy\" decoding=\"async\" width=\"907\" height=\"710\" class=\"alignnone wp-image-52\" src=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/klopsapliiatsiikoonil.png\" title=\"klopsapliiatsiikoonil.png\" alt=\"Pliiatsi ikoonil\" srcset=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/klopsapliiatsiikoonil.png 907w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/klopsapliiatsiikoonil-300x235.png 300w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/klopsapliiatsiikoonil-768x601.png 768w\" sizes=\"auto, (max-width: 907px) 100vw, 907px\">\r\n<\/p>\r\n\r\n<p>\r\n\t<span>Avanenud vaates vali \u201eConstraints\u201c ehk piirangud ja kirjuta v\u00e4ljale \u201eDefault\u201c \u201enow()\u201c<\/span>\r\n<\/p>\r\n\r\n<p>\r\n\t<img loading=\"lazy\" decoding=\"async\" width=\"974\" height=\"765\" class=\"alignnone wp-image-53\" src=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/constraints.png\" title=\"constraints.png\" alt=\"constraints\" srcset=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/constraints.png 974w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/constraints-300x236.png 300w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/constraints-768x603.png 768w\" sizes=\"auto, (max-width: 974px) 100vw, 974px\">\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\tSeej\u00e4rel salvesta. Kui tahad midagi muuta veel siis vasakul sirvimispuus paremkl\u00f5ps tabelil Isik ja Properties annab sama vaate tagasi.\r\n<\/p>\r\n\r\n<p>\r\n\tV\u00f5i kui soovid muuta konkreetse v\u00e4lja andmet\u00fc\u00fcpi v\u00f5i v\u00e4lja nimetust, siis paremkl\u00f5ps selle v\u00e4lja nimetusel avab aknakese, kust tuleks valida Properties.\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p style=\"margin-left: 80px\">\r\n\t<img loading=\"lazy\" decoding=\"async\" width=\"279\" height=\"307\" class=\"alignnone wp-image-54\" style=\"width: 239px;height: 263px;float: left\" src=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/tabelike.png\" title=\"tabelike.png\" alt=\"Tabelike\" srcset=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/tabelike.png 279w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/tabelike-273x300.png 273w\" sizes=\"auto, (max-width: 279px) 100vw, 279px\"><img loading=\"lazy\" decoding=\"async\" width=\"254\" height=\"251\" class=\"alignnone wp-image-55\" style=\"float: left;width: 211px;height: 209px;margin-left: 100px;margin-right: 100px\" src=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/properties.png\" title=\"properties.png\" alt=\"Properties\">\r\n<\/p>\r\n\r\n<p style=\"margin-left: 80px\">\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p style=\"margin-left: 80px\">\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p style=\"margin-left: 80px\">\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p style=\"margin-left: 80px\">\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p style=\"margin-left: 80px\">\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p style=\"margin-left: 80px\">\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p style=\"margin-left: 80px\">\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p style=\"margin-left: 80px\">\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\tN\u00fc\u00fcd sisesta tabelisse andmed. Siseta t\u00e4pselt need kuus rida, mida n\u00e4ed, siis tulevad p\u00e4rast p\u00e4ringute tegemisel samad tulemused kui n\u00e4idetes, see pole t\u00e4htis kas perenime ja eesnime lahtrites on lubatud 50 v\u00f5i 100 t\u00e4hem\u00e4rki v\u00f5i milline on veergude j\u00e4rjestus:\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\t<img loading=\"lazy\" decoding=\"async\" width=\"1329\" height=\"346\" class=\"alignnone wp-image-56\" src=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/andmed.png\" title=\"andmed.png\" alt=\"Andmed\" srcset=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/andmed.png 1329w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/andmed-300x78.png 300w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/andmed-1024x267.png 1024w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/andmed-768x200.png 768w\" sizes=\"auto, (max-width: 1329px) 100vw, 1329px\">\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\tAndmete sisestamiseks liigu sirvimise puus tabeli nimetusele (Isik) ja vali \u00fclevalt men\u00fc\u00fcribalt View (ruudustik). Tabelisse andmete lisamisel kl\u00f5psa vastaval lahtril ja siseta sinna info, \u00e4ra t\u00e4ida v\u00e4lju id ja kirje_lisamisaeg, need t\u00e4idetakse automaatselt p\u00e4rast teiste andmete sisestamist ja salvestamist.\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\t<img loading=\"lazy\" decoding=\"async\" width=\"522\" height=\"899\" class=\"alignnone wp-image-57\" src=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/salvesta.png\" title=\"salvesta.png\" alt=\"Salvesta\" srcset=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/salvesta.png 522w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/salvesta-174x300.png 174w\" sizes=\"auto, (max-width: 522px) 100vw, 522px\">\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\tL\u00f5pus kl\u00f5psa \u201eSalvesta\u201c. Salvestamisnupp asub p\u00e4ringuakna kohal. Salvestada saab ka klahviga F6.\r\n<\/p>\r\n\r\n<p>\r\n\t<img loading=\"lazy\" decoding=\"async\" width=\"716\" height=\"85\" class=\"alignnone wp-image-58\" src=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/salvesta2.png\" title=\"salvesta2.png\" alt=\"Salvesta\" srcset=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/salvesta2.png 716w, https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/salvesta2-300x36.png 300w\" sizes=\"auto, (max-width: 716px) 100vw, 716px\">\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n\r\n<p>\r\n\t\u00a0\r\n<\/p>\r\n<br><div class=\"wp-block-group attached-files-group is-layout-constrained wp-block-group-is-layout-constrained\"><div class=\"wp-block-file\"><a href=\"https:\/\/sisu.ut.ee\/wp-content\/uploads\/sites\/444\/postgres_installimisjuhis.docx\" target=\"_blank\" rel=\"noreferrer noopener\">postgres_installimisjuhis.docx<\/a><\/div><\/div>","protected":false},"excerpt":{"rendered":"<p>\u00a0 Alusta andmebaasihaldustarkvara PostgreSQL installimisest. PostgreSQL installimisjuhend. Kui arvutisse on server loodud, saab serverisse luua andmebaasi. Tee paremkl\u00f5ps serveri nimetusel, see peaks meil olema PostgreSQL\u00a0\u2192 Create \u2192 Database Pane andmebaasile nimeks Test, muud parameetrid j\u00e4ta nii nagu on. Andmebaas ilmub &#8230;<\/p>\n","protected":false},"author":231,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"class_list":["post-13","page","type-page","status-publish","hentry"],"acf":[],"_links":{"self":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages\/13","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/users\/231"}],"replies":[{"embeddable":true,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/comments?post=13"}],"version-history":[{"count":2,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages\/13\/revisions"}],"predecessor-version":[{"id":150,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages\/13\/revisions\/150"}],"wp:attachment":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/media?parent=13"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}