db.dobo.sk

lets go back to roots because roots mean culture

pridat stlpec s autoinkrementalnou hodnotou a PK

ALTER TABLE tablename ADD COLUMN id_aut SERIAL PRIMARY KEY;

updajtni tabulku na zaklade 2 parametroveho subselectu

UPDATE tab_finds SET datation = 4000 WHERE id_aut IN
(SELECT id_aut FROM tab_finds where material = ‘stavebni keramika’ AND datation < 3100);

 texty, stringy char a varchar

lowercase/uppercase

UPDATE tabulka SET column=lower(column);

updatovanie tabuľky – concatenácia dvoch polí, s tým, že číselné polia sú prevedené na characters, všimni si parameter formátovania (FM)

UPDATE tab_polygony_sezony SET concat_season_polygon = (TO_CHAR(season,’FM9′) || TO_CHAR(r,’FM999′)) AS ddd  FROM probe;

prehľad stingových funkcií

matematické operácie

pomerne intuitívne

UPDATE tabulka SET calcul = datace/(hustota*koeficient);

UPDATE tab_polygony_sezony
SET koeficient_rozestupy = ROUND
(rozestup_linii/(SELECT AVG(rozestup_linii)
FROM tab_polygony_sezony),2)

window funkcie

SELECT distinct(id_polygon), sum(“vaha”) OVER (PARTITION BY id_polygon) AS Vaha_overall
FROM tab_finds
where season=1
ORDER BY id_polygon;

joiny

poznáme nasledovné typy joinov v postgrese:

• cross join
• lateral join (nové od 9.3.)
• inner join
• left oute rjoin
• right outer join
• full outer join

left join abstraktny priklad

SELECT A.pka, A.c1, B.pkb, B.c2
FROM A
LEFT JOIN B ON A .pka = B.fka;

Lateral join

Chápem to tak, že je to nahradenie subselectu (resp. ešte lepšie – correlated subquery), ktorý má tú nevýhodu, že vracia len jeden record. Lateral join vracia sadu recordov, tak, akoby sa nad result setom loopovalo a zároveň hneď spojí (join) výsledky a hlavným dotazom. Pekné vysvetlenie na stackoverflow.

Fyzické joiny

Hore uvedené typy joinov sú logické typy – abstrakcie, ako spájať dáta v tabuľkách. Samotné joiny na fyzickej vrstve sa realizujú pomocou nested loop join, merge join, alebo hash join

Nested loop join

Nested lop join je operácia “každý s každým” – z jednej tabuľky sa vyberajú postupne hodnoty a loopom sa skúša, ktoré pasujú na hodnoty v druhej tabuľke. Náročné na CPU, pokiaľ je recordov v oboch tabuľkách hodne. Pekné vysvetlenie tu na YT.

Nested loop má niekoľko “podtypov”, podľa toho, aká presná implementácia sa použije (napr. materialized nested loop používa materialized views; indexed nested loop používa indexy a pod.)

Merge join

V princípe podobný ako nested loop join (porvnávanie zhody hodnôt), ale algoritmus nemusí iterovať (loopovať), pretože hodnoty sú zoradené. Z toho však vyplýva, že musí existovať buď index, alebo sa najprv musia hodnoty zoradiť. Pekné vysvetlenie tu na YT.

Merge join má tiež niekoľko podtypov v závislosti na presnej implementácii (materialized, indexed podľa toho, na ktorú tabuľku sa indexy využívajú a pod….).

Hash join

Hash join môže byť použitý len na equi-joins.

Podstatou je prehnanie hodnoty z 1. tabuľky hashovaciou funkciou a vytvorenie hash bucketu (chlieviky pre hashe, alebo hashová mapa). Následne sa hashovaciou funkciou porovnávajú hodnoty z 2. tabuľky a tým priradzujú do správnych bucketov. Je to pomerne rýchle a stavané aj na veľké tabuľky. Je to všem rýchle, len pokiaľ sa hashovacia tabuľka vojde do pamäte, inak sa bude tempovať na disk a komplikuje sa to. Vysvetlenie na YT tu.

Postgres to robí rýchlo, pokiaľ je to in memory a pokiaľ to nie je všetko in-memory, tak používa hybridnú hash table s šikmým vyhľadávaním dát. In-memory znamená, že sa to vojde do work_mem a postgres sa na začiatku rozhodne, že použije in-memory hash join vtedy, pokiaľ inner table je menšia, než 25% work_mem.

Efektivita rôznych typov joinov

Video s potrhlým chlapíkom od Oraclu ukazuje ešte raz a na príklade hracích kariet celý proces. Tento obrázok z videa jasne ukazuje efektivity jednotlivých typov joinov (počet fyzických operácií). No a prečo sa nepoužíva výlučne hash join? Pretože to platí len pre preliezanie celých oboch datasetov s operátorom “=”, čož nie je vždy náš prípad…

 

select na hodnoty inej tabulky cez M:N relation

Pri normalizácii sa často stane, že máme tabuľky poprepájané cez M:N reláciu (“pomocnú tabuľku”). Ako selektovať všetky záznamy z druhej tabuľky pre špecifický záznam z prvej tabuľky? Typický príklad z archelogickej opraxe: stratigrafické jednotky a fotky, každá entita má svoju vlastnú tabuľku. . Stratigrafická jednotka može byť na viacerých fotkách a fotka može obsahovať viac stratigrafických jednotiek ergo M:N. Robí sa to cez inner joiny na intermediate tabuľku a pekný rozklad je tu.

 

konverzia datových typov medzi Oracle a Postgres

 

	PostgreSQL		Oracle
	BIGINT	64-bit integer	NUMBER(19)
	BIGSERIAL	64-bit autoincrementing integer	Sequence and trigger
	BIT(n)	Fixed-length bit string	RAW(n/8)
	BIT VARYING(n)	Variable-length bit string	RAW(n/8)
	BOOLEAN, BOOL	True, false or NULL	CHAR(1)
	BYTEA	Variable-length binary data, ⇐ 2G	BLOB
	CHARACTER(n), CHAR(n)	Fixed-length string, 1 ⇐ n ⇐ 1G	CHARACTER(n), CHAR(n)
	CHARACTER VARYING(n)	Variable-length string, 1 ⇐ n ⇐ 1G	VARCHAR2(n)
	DATE	Date (year, month and day)	DATE	Includes time part
	DECIMAL(p,s), DEC(p,s)	Fixed-point number	NUMBER(p,s)
	DOUBLE PRECISION	Double-precision floating-point	BINARY_DOUBLE
	FLOAT4	Single-precision floating-point	BINARY_FLOAT
	FLOAT8	Double-precision floating-point	BINARY_DOUBLE
	INTEGER, INT	32-bit integer	NUMBER(10)
	INT2	16-bit integer	NUMBER(5)
	INT4	32-bit integer	NUMBER(10)
	INT8	64-bit integer	NUMBER(20)
	INTERVAL	Date and time interval	INTERVAL YEAR TO MONTH
			INTERVAL DAY TO SECOND
	INTERVAL unit	Date and time interval	NUMBER(5)
	INTERVAL YEAR TO MONTH	Date interval	INTERVAL YEAR TO MONTH
	INTERVAL DAY TO HOUR	Day and time interval	INTERVAL DAY(5) TO SECOND
	INTERVAL DAY TO MINUTE	Day and time interval	INTERVAL DAY(5) TO SECOND
	INTERVAL DAY TO SECOND[(p)]	Day and time interval	INTERVAL DAY(5) TO SECOND[(p)]
	INTERVAL HOUR TO MINUTE	Time interval	INTERVAL DAY(5) TO SECOND
	INTERVAL HOUR TO SECOND[(p)]	Time interval	INTERVAL DAY(5) TO SECOND[(p)]
	INTERVAL MINUTE TO SECOND[(p)]	Time interval	INTERVAL DAY(5) TO SECOND[(p)]
	MONEY	Currency amount	NUMBER(17,2)
	NUMERIC(p,s)	Fixed-point number	NUMERIC(p,s)
	REAL	Single-precision floating-point	BINARY_FLOAT
	SERIAL	32-bit autoincrementing integer	Sequence and trigger
	SERIAL2	16-bit autoincrementing integer	Sequence and trigger
	SERIAL4	32-bit autoincrementing integer	Sequence and trigger
	SERIAL8	64-bit autoincrementing integer	Sequence and trigger
	SMALLINT	16-bit integer	NUMBER(5)
	SMALLSERIAL	16-bit autoincrementing integer	Sequence and trigger
	TEXT	Variable-length character data, ⇐ 1G	CLOB
	TIME(p)	Time with fraction	TIMESTAMP(p)
	TIME(p) WITH TIME ZONE	Time with fraction and time zone	TIMESTAMP(p) WITH TIME ZONE
	TIMETZ(p)
	TIMESTAMP	Date and time with fraction	TIMESTAMP(p)
	TIMESTAMP(p) WITH TIME ZONE	Date and time with time zone	TIMESTAMP(p) WITH TIME ZONE
	TIMESTAMPTZ(p)
	UUID	Universally unique identifier	CHAR(36)
	VARBIT(n)	Variable-length bit string	RAW(n/8)
	VARCHAR(n)	Variable-length string, 1 ⇐ n ⇐ 1G	VARCHAR2(n)
	XML	XML data	XMLTYPE

pekný prehľad konverzií a funkcií i s linkami na konvertory tu