SQL Triky I.

Z PostgreSQL
(rozdíl) ← Starší verze | zobrazit aktuální verzi (rozdíl) | Novější verze → (rozdíl)
Skočit na navigaci Skočit na vyhledávání

Dvacet novějších triků

Nepřehánějte to s výjimkami

Zachycení chyby má nemalou režii. Proto preferujte takový způsob psaní procedur, kdy se zachycení výjimek vyhnete. Samozřejmě, že jsou případy, kdy to není možné - v řadě případů je úspora času tak malá, že se nevyplatí řešit jestli psát s nebo bez výjimek.

V příkladu mám dvě varianty funkce která přidává do tabulky unikátních celých čísel nový záznam. V případě, že přidávaný záznam dosud v tabulce neexistuje, vrací true, jinak hodnotu false. 

CREATE OR REPLACE FUNCTION addnew1(integer) 
RETURNS boolean AS $$
BEGIN
  -- test(a) .. primarni klic
  INSERT INTO test(a) VALUES($1);
  RETURN true;
EXCEPTION WHEN OTHERS
  RETURN false
END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION addnew2(integer)
RETURNS boolean AS $$
BEGIN
  IF NOT EXISTS(SELECT a FROM test WHERE a = $1) THEN
    INSERT INTO test(a) VALUES($1);
    RETURN true;
  END IF;
  RETURN false;
END; $$ LANGUAGE plpgsql;

Procedura addnew1 je zhruba 2x pomalejší než procedura addnew2, na druhou stranu je oprati addnew2 robustnější. V plpgsql každý chráněný blok (umožňuje zachytit výjimku) generuje subtransakci. V tomto případě t_nova_transakce + INSERT > SELECT + INSERT.

Rychlé porovnání proměnných NEW a OLD v těle triggeru

Provádění triggeru lze zeefektivnit tak, že výkonnou část triggeru voláme pouze tehdy, když došlo ke změně určitých sloupců. Úspěšně můžeme použít nově (od 8.2) zavedený operátor IS DISTINCT FROM pro typ řádek. 

IF ROW(NEW.*) IS DISTINCT FROM ROW(OLD.*) THEN
  -- pokracuji pouze pokud doslo ke skutecne zmene
  IF NEW.a1 IS DISTINCT FROM OLD.a1 
     OR NEW.a2 IS DISTINCT FROM OLD.a2 THEN
     -- doslo ke zmene dulezitych sloupcu
     ...
  END IF;
END IF;
RETURN NEW;

nebo ještě efektivněji 

IF ROW(NEW.a1, NEW.a2) IS DISTINCT FROM ROW(OLD.a1, OLD.a2) THEN
  -- doslo ke zmene dulezitych sloupcu
  ...
END IF;
RETURN NEW;

Velice rychlé odstranění prvních N řádků

PostgreSQL nepodporuje operace UPDATE LIMIT a DELETE LIMIT, které mohou být v určitých případech potřeba (například pro simulaci fronty). Klasické řešení skrze IN a poddotazu není příliš efektivní: 

postgres=# explain analyze delete from del where ctid  in (select ctid from del limit 10 offset 0);
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Merge IN Join  (cost=804.79..854.94 rows=10 width=6) (actual time=63.392..63.464 rows=10 loops=1)
   Merge Cond: (public.del.ctid = "IN_subquery".ctid)
   ->  Sort  (cost=804.39..829.39 rows=10000 width=6) (actual time=63.205..63.217 rows=11 loops=1)
         Sort Key: public.del.ctid
         Sort Method:  quicksort  Memory: 646kB
         ->  Seq Scan on del  (cost=0.00..140.00 rows=10000 width=6) (actual time=0.045..27.366 rows=9970 loops=1)
   ->  Sort  (cost=0.41..0.43 rows=10 width=6) (actual time=0.172..0.187 rows=10 loops=1)
         Sort Key: "IN_subquery".ctid
         Sort Method:  quicksort  Memory: 17kB
         ->  Limit  (cost=0.00..0.14 rows=10 width=6) (actual time=0.021..0.127 rows=10 loops=1)
               ->  Seq Scan on del  (cost=0.00..140.00 rows=10000 width=6) (actual time=0.016..0.030 rows=10 loops=1)
 Total runtime: 63.661 ms
(12 rows)

Poznámka: V případě partitiovaných tabulek není ctid unikátní, a je nutné používat vždy dvojici (ctid, tableoid).

Tom Lane navrhl berličku; použití pole: 

postgres=# explain analyze delete from del where (ctid, tableoid)  = any (array(select (ctid, tableoid) from del limit 10));
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Tid Scan on del  (cost=0.14..40.37 rows=10 width=6) (actual time=0.200..0.237 rows=10 loops=1)
   TID Cond: (ctid = ANY ($0))
   InitPlan
     ->  Limit  (cost=0.00..0.14 rows=10 width=6) (actual time=0.046..0.139 rows=10 loops=1)
           ->  Seq Scan on del  (cost=0.00..140.00 rows=10000 width=6) (actual time=0.040..0.065 rows=10 loops=1)
 Total runtime: 0.376 ms

Jádro pudla je v tom, že v prvém případě se PostgreSQL snaží o join, což v tomto případě zdaleka není efektivní jako podmíněné sekvenční čtení. Tento trik se dá použít i v dalších případech. Obecně se dá říci, že v případě malých N (N < 100) je predikát = ANY efektivnější predikát IN. Platí pro PostgreSQL 8.2, 8.3 Nicméně ani tato rada neplatí vždy. Existence nebo neexistence indexů způsobí, že toto doporučení nelze chápat absolutně.

Vytvoření seznamu hodnot oddělených čárkou

D. Dante Lorenso poslal do konference návod jak vytvořit seznam hodnot z tabulky: 

SELECT a.id, a.name,
    array_to_string(ARRAY(
      SELECT b.name
         FROM b
        WHERE b.id = a.id
        ORDER BY b.name ASC
    ), ',') AS b_names
   FROM a
  ORDER BY a.id ASC;

[table a]
 id | name
----+------
1   | one
2   | two
3   | three
4   | four

[table b]
 id | name
----+------
1   | pizza
1   | hot dog
2   | gorilla
2   | monkey
3   | apple
4   | cheese
4   | milk
4   | eggs

--result
 id | name  | b_names
----+-------+---------
1   | one   | pizza,hot dog
2   | two   | gorilla,monkey
3   | three | apple
4   | four  | cheese,milk,eggs

Využití funkcí pgstattuple as pgstatindex

PostgreSQL 8.2 a vyšší obsahují velice užitečný doplněk pgstattuple. Tento doplněk obsahuje diagnostické funkce zobrazující podíl mrtvých záznamů v tabulce a fragmentaci indexu. Používat tyto funkce samostatně není příliš praktické, proto jsem si napsal následující funkce: 

CREATE OR REPLACE FUNCTION print_table_dead_tp(OUT table_name varchar, OUT tuple_count int8, OUT table_len varchar, OUT free_space varchar,
                                               OUT dead_tuple_percent numeric(5,2), OUT dead_tuple_count integer) 
RETURNS SETOF RECORD AS $$
DECLARE r record; s record; 
BEGIN 
  FOR r IN SELECT c.oid, n.nspname || '.' || c.relname as "tablename"
              FROM pg_catalog.pg_class c
                   LEFT JOIN 
                   pg_catalog.pg_namespace n 
                   ON n.oid = c.relnamespace
             WHERE c.relkind = 'r'
               AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
               AND pg_catalog.pg_table_is_visible(c.oid)
             ORDER BY 2
  LOOP
    s := pgstattuple(r.oid);
    table_name := r.tablename;
    tuple_count := s.tuple_count; dead_tuple_percent :=  s.dead_tuple_percent; 
    dead_tuple_count := s.dead_tuple_count;
    table_len :=  pg_size_pretty(s.table_len);
    free_space := pg_size_pretty(s.free_space);
    RETURN NEXT; 
  END LOOP; 
  RETURN;
END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION print_indexes_info(OUT index_name varchar, OUT indexsize varchar, 
                                              OUT leaf_pages integer, OUT deleted_pages integer, 
                                              OUT leaf_fragmentation double precision, OUT avg_leaf_density double precision) 
RETURNS SETOF RECORD AS $$
DECLARE r record; o pgstatindex_type;
BEGIN
  FOR r IN SELECT schemaname || '.' || indexname AS indexname FROM pg_indexes LOOP
    BEGIN
      o := pgstatindex(r.indexname);
      index_name := r.indexname; 
      indexsize := pg_size_pretty(o.index_size); deleted_pages := o.deleted_pages;
      leaf_pages := o.leaf_pages; avg_leaf_density := o.avg_leaf_density;
      leaf_fragmentation := o.leaf_fragmentation; 
  
      RETURN NEXT;
    EXCEPTION WHEN OTHERS THEN
      indexsize := NULL; deleted_pages := NULL; leaf_fragmentation := NULL;
      index_name := r.indexname;
      RETURN NEXT;
    END;
  END LOOP;
  RETURN;
END; $$ LANGUAGE plpgsql;

Odstranění diakritiky

PostgreSQL umožňuje voláním funkce to_ascii odstranit diakritiku. Tato funkce nepodporuje kódování utf8, které se pomalu začíná rozšiřovat. Nicméně k dispozici je ještě funkce convert, kterou používáme pro převod znaků z jedné znakové sady do druhé. Kombinací volání těchto funkcí dokážeme odstranit text i v utf8 databázích. 

postgres=# select to_ascii(convert('Příliš žlutý kůň' using utf8_to_iso_8859_2),'latin2');
     to_ascii     
------------------
 Prilis zluty kun
(1 row)

Pozn: pro 7.4 se konverze jmenuje "utf_8_to_iso_8859_2". V 8.3 je nutné definovat vlastní funkci to_ascii a odlišný zápis: 

CREATE FUNCTION to_ascii(bytea, name) 
RETURNS text AS 'to_ascii_encname' LANGUAGE internal IMMUTABLE STRICT;

SELECT to_ascii(convert_to('Příliš žlutý kůň', 'latin2'),'latin2');

Optimalizace predikátu IN

V konferenci pg_performance se objevil příspěvek o optimalizaci predikátu IN, kdy argument predikátu představuje konstantní výčet hodnot. Pokud je seznam hodnot delší než přibližně osmdesát prvků, pak se vyplatí výčet hodnot převést na konstantní poddotaz použitím tzv. multivalues). 

SELECT * 
   FROM tab 
  WHERE x IN (1,2,3,..n); -- n > 70

-- rychlejší varianta
SELECT * 
   FROM tab
  WHERE x IN (VALUES(10),(20));

Zápis s VALUES je rychlejší až při větším počtu prvků, jinak je klasický (přirozený) zápis prováděn efektivněji.

Validace BAR kódu

SQL Guru Joe Celko ve svém sloupku v DBAzinu ukazuje zajímavou techniku jak validovat BAR kódy pomocí MULTI VALUE. Na rozdíl od DB2 PostgreSQL nepodporuje dotazy v CHECK omezeních, proto je nutné navrhnout SQL funkci: 

CREATE FUNCTION barcode_sum(text)
RETURNS bigint AS 
$$
  SELECT ABS(SUM(CAST(SUBSTRING($1 FROM Weights.seq FOR 1) AS INTEGER) * Weights.wgt))
     FROM (VALUES (CAST(1 AS INTEGER), CAST(-1 AS INTEGER)),
                  (2, +1), (3, -1), (4, +1), (5, -1), 
                  (6, +1), (7, -1), (8, +1), (9, -1), (10, +1), 
                  (11,-1), (12, +1)) AS weights(seq, wgt)
$$ LANGUAGE sql;

Tuto funkci použijeme v definici tabulky: 

CREATE TABLE products(
  barcode char(13) NOT NULL
  CONSTRAINT valid_checkdigit
    check (MOD(barcode_sum(barcode),10) = CAST(substring(barcode from 13 for 1) AS integer))
  CONSTRAINT all_numeric_checkdigit CHECK(barcode NOT SIMILAR TO '%[^0-9]%')
);

INSERT INTO products VALUES('2837232811227');

Omezení se v PostgreSQL vyhodnocují odzadu. V případě, že všechna jsou splněná, PostgreSQL dovolí vložit záznam. V případě, že řetězec s barcodem obsahuje méně než 13 znaků, je doplněn zleva mezerami (jelikož je použit typ char(13)). Proto test NOT SIMILAR TO zároveň zajistí správný počet číslic.

Unikátní hash index

Není tak úplně nestandardním požadavkem zajistit jednoznačnost textů v tabulce. Klasický index je pro delší textové pole nevhodný. Celkem přirozeně uvažujeme o použití některé hash funkce. Výsledkem je řetězec o 32 znacích. Jelikož tento řetězec vlastně obsahuje hexadecimální číslice, tak můžeme délku zkrátit převodem na typ bytea. Dostaneme se na 16 bajtů pro libovolně dlouhý řetězec, což již můžeme považovat za efektivní. Pozor, toto řešení nebere ohled na případné možné kolize. 

root=# CREATE TABLE test(a text);
CREATE TABLE

root=# CREATE UNIQUE INDEX uidx ON test((decode(md5(a),'hex')));
CREATE INDEX

Ukázka by šla ještě vylepšit převodem na malá písmena a odstraněním počátečních mezer (případně všech mezer). 

root=# CREATE UNIQUE INDEX uidx ON test((decode(md5(lower(replace(a,' ',''))),'hex')));
CREATE INDEX
root=# INSERT INTO test VALUES('příliš žluťoučký kůň');
INSERT 0 1
root=# INSERT INTO test VALUES('příliš žluťoučký kůň ');
ERROR:  duplicate key violates unique constraint "uidx"
root=# INSERT INTO test VALUES('Příliš žluťoučký kůň');
ERROR:  duplicate key violates unique constraint "uidx"

-- mozne vylepseni, snizeni rizika kolize, pouziti dvou ruznych hash. funkci
-- CREATE UNIQUE INDEX uidx ON TEST((decode(md5(a),'hex')),(hashtext(a)));

Získání OID funkce

Oid má v PostgreSQL význam univerzálního jednoznačného identifikátoru databázových objektů. Relativně jednoduchou cestou jak získat oid je použít přetypování: 

CREATE OR REPLACE FUNCTION a(integer, varchar) RETURNS void AS 
$$
  BEGIN
  END;
$$ LANGUAGE plpgsql;

root=# SELECT 'a'::regproc::int;
ERROR:  more than one function named "a"
root=# SELECT 'a(integer, varchar)'::regprocedure::int;
 int4  
-------
 57507
(1 row)

root=# SELECT 57507::regprocedure;
         regprocedure         
------------------------------
 a(integer,character varying)
(1 row)

Oid tabulky získáme přetypováním na typ regclass: 

SELECT 'oo'::regclass::int;

Univerzální řazení pole

Užasnou ukázkou možností PostgreSQL je následující univerzální procedura pro seřazení pole Davida Fettera 

CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(
    SELECT $1[s.i] AS "foo"
    FROM
        generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
    ORDER BY foo
);
$$;

Mimochodem, tato rychlost této funkce je srovnatelná a vyšší než vlastní implementace v plpgsql. Už jen z toho důvodu, že PostgreSQL používá qsort. Pro vyšší počet prvků (10 a více) je rychlostní rozdíl znatelný (bublesort v plpgsql pro N100 27ms, array_sort 3ms, bsort pro N1000 4000ms, array_sort 7ms). Kromě rozdílu v implementaci je to samozřejmě i rozdíl mezi quick sortem a bublesortem.

Transformace pole v Perlu na pole PostgreSQL

Plperl ve verzi 8.1 nepodporuje automatickou transformaci polí v OUT parametrech. Můžeme ale použít nedokumentovanou proceduru '_plperl_to_pg_array', která pole převede. 

CREATE OR REPLACE FUNCTION foo(OUT a integer[], OUT b integer[]) AS $$
  return { a=> _plperl_to_pg_array([1,2,3]),
           b=> _plperl_to_pg_array([4,5,6]) };

Validace UPC kódu

Následující příklad je ukázkou validace údaje s kontrolní číslicí v Perlu, resp. v PL/Perl-u. Tato funkce posloužila i jako testovací příklad pro srovnání rychlosti PL/pgSQL, PL/Perl a C. Procedura v Perlu je cca o 20% rychlejší než v PL/pgSQL, nicméně implementace funkce v C byla zhruba 10x rychlejší. 

CREATE OR REPLACE FUNCTION ean_perl (
    TEXT
) RETURNS boolean AS $_$
    my $ean = length $_[0] == 12 ? "0$_[0]" : $_[0];
    # Make sure we really have an EAN.
    return 'false' unless $ean =~ /^\d{13}$/;
    my @nums = split '', $ean;
    return 10 - (
        # Sum even numerals.
        (   (   $nums[1] + $nums[3] + $nums[5] + $nums[7] + $nums[9]
                    + $nums[11]
            ) * 3 # Multiply total by 3.
        # Add odd numerals except for checksum (12).
        ) + $nums[0] + $nums[2] + $nums[4] + $nums[6] + $nums[8] + $nums[10]
    # Compare to the checksum.
    ) % 10 == $nums[12] ? 'true' : 'false';
$_$ LANGUAGE plperl immutable;

Zajímavé řešení navrhl Joe Celko ve svém článku. Po přepisu do PostgreSQL jeho řešení vypadá následovně: 

SELECT (
   SELECT mod(
      sum(
         CAST(substring('2837232811227' from s.seq for 1) AS integer) * 
         CASE mod(s.seq,2) WHEN 0 THEN 1 ELSE -1 END),
      10) FROM generate_series(1,12) as s(seq)) =
   CAST(substring('2837232811227' FROM 13 FOR 1) AS integer);

Zobrazení každého n-tého řádku

Pokud potřebujeme získat z tabulky pouze každý n-tý řádek, můžeme použít dočasné sekvence a funkci modulo: 

  CREATE TEMP SEQUENCE number; 
  SELECT * FROM ( SELECT *, nextval('number') AS number FROM datatable ) foo 
    WHERE foo.number % 5 = 0;

Rozepsání hodnot z pole

K zobrazení obsahu pole můžeme použít funkci generate_series. Její výsledek aliasujeme na tabulku idx(n), tak abychomu mohli použít její výsledek jako proměnnou n. Případně si můžeme napsat vlastní funkci unpack: 

 SELECT (ARRAY[1,3,4,6,7])[idx.n] FROM generate_series(1,7) idx(n);

CREATE OR REPLACE FUNCTION unpack(anyarray)
RETURNS SETOF anyelement AS $$ 
SELECT $1[i] 
   FROM generate_series(array_lower($1,1), 
                        array_upper($1,1)) g(i);
$$ LANGUAGE sql STRICT IMMUTABLE;

postgres=# select unpack(array['a','b','c']);
 unpack 
--------
 a
 b
 c
(3 rows)

Optimalizace dotazu LIKE

PostgreSQL použije pro podmínku typu LIKE index pouze při splnění následujících podmínek:

  • maska nezačíná symboly % a _
  • databázový cluster je inicializován 'C' locales

Poslední podmínku můžeme lehce obejít vytvořením speciálního indexu: 

CREATE INDEX like_index ON lidi(prijmeni varchar_pattern_ops);

Diakritika nevadí, musíme si ale dát pozor na to, že like je case sensitiv. Pokud se do těchto podmínek vejdete, ostraníte jedno sekvenční čtení tabulky a stahujete provádění dotazu ze stovek milisekund na milisekundy.

Pokud má tabulka méně než 100 řádků, tak se pravděpodobně index nepoužije, i když existuje. Pro tak malou tabulku je režie na přečtení celé tabulky menší než režie spojená s použitím indexu. Pro větší tabulky už má smysl indexy sestavovat, zvláště pokud jsou větší než 10000 řádků. Použití indexů je třeba monitorovat, a nepoužívaný index (pokud je to možné) odstranit. S každým indexem je spojená určitá režie operací INSERT, UPDATE a DELETE nehledě na spotřebu diskového prostoru. Index používejte na sloupce s dostatečnou selektivitou, tj. indexy nepoužívejte na sloupce kde je relativně málo jednoznačných hodnot např. pohlaví, oddělení. Pokud už chcete něco takového udělat, zkuste si napřed, zda-li vám nepomůže částečný (partial) index nebo partitioning.

Pokud potřebujete použít masku ve tvaru '%neco' (např. pro hledání email. adres podle domén) můžete použít následující trik:

  • vytvořte funkcionální index ve tvaru reverse(hodnota)
  • operaci like proveďte s invertovanou maskou

PostgreSQL nepodporuje funkci reverse(varchar). Najdeme ji v doplňku Orafunc nebo ji přebereme z Perlu. PL/pgSQL není vhodný jazyk pro její implementaci. 

CREATE OR REPLACE FUNCTION reverse(varchar) RETURNS varchar AS $$ 
  $reversed = reverse $_[0]; 
  return $reversed; 
$$ LANGUAGE plperlu IMMUTABLE;

CREATE INDEX rev_email ON users( (reverse(email) ) varchar_pattern_ops );
SELECT * FROM _users WHERE reverse(email) LIKE reverse ('%.cz');

Pokud nemůže použít plperlu, zkuste SQL implementaci: 

CREATE OR REPLACE FUNCTION reverse(TEXT) RETURNS TEXT AS $$
 SELECT 
    array_to_string( 
      ARRAY
        ( SELECT substring($1, s.i,1) FROM generate_series(length($1), 1, -1) AS s(i) ), 
      '');
$$ LANGUAGE SQL IMMUTABLE;

Pro zajímavost, implementace funce reverse rekurzí (vzhledem k nekešování prováděcího plánu nejpomalejší varianta): 

CREATE OR REPLACE FUNCTION reverse(varchar, int, int) RETURNS varchar AS $$
  SELECT CASE $2 
              WHEN 1 THEN $1
              WHEN 2 THEN SUBSTRING($1 FROM 2 FOR 1) || SUBSTRING($1 FROM 1 FOR 1) 
              WHEN 3 THEN SUBSTRING($1 FROM 3 FOR 1) || SUBSTRING($1 FROM 2 FOR 1) || SUBSTRING($1 FROM 1 FOR 1)
              ELSE reverse(SUBSTRING($1 FROM $3 + 1 FOR $2 - $3), $2 - $3, round(($2 - $3)/2.0)::integer)
                   || reverse(SUBSTRING($1 FROM 1 FOR $3), $3, round(($3)/2.0)::integer) END;
$$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION reverse(varchar) RETURNS varchar AS $$
  SELECT reverse($1, char_length($1), round(char_length($1)/2.0)::int);
$$ LANGUAGE sql IMMUTABLE;

Odložené omezení (deferred constraints)

Za normálních okolností se všechny testy provádí okamžitě. SQL umožňuje provedení testů odložit na okamžik před potvrzením transakce. Tuto možnost PostgreSQL nepodporuje úplně, lze odložit pouze kontrolu referenční integrity. Pokud použijeme nedokumentovanou, a tudíž i nezaručenou schopnost PostgreSQL vytvářet tzv. odložené spouště (defered triggers), můžeme implementovat i odložené testy. Syntaxe je podobná SQL registraci spouště: 

  CREATE CONSTRAINT TRIGGER sc
  AFTER INSERT 
  ON fieldtrip_students
  INITIALLY DEFERRED
  FOR EACH ROW EXECUTE PROCEDURE field_trip();

Smazání duplicitních řádek,

Pokud tabulka nebyla vytvořena s parametrem WITHOUT OIDS, má každý řádek v tabulce tzv. OID - identifikátor řádku. Tato vlastnost se nám hodí při mazání duplicit. Ty můžeme jednoduše zrušit následujícím příkazem (pro tabulku lidí) 

 DELETE FROM lidi WHERE 
   NOT oid IN (SELECT MIN(oid) FROM lidi GROUP BY jmeno, prijmeni);

Nejrobusnější variantu jsem našel na prezentaci Grega Mullanea. Používá ctid - tuple ID fyzický identifikátor záznamu, který vždy existuje. Pro ctid nejsou definovány agregační funkce, je třeba si pomoci souvztažným poddotazem. 

 DELETE FROM lidi WHERE
   NOT ctid = (SELECT ctid FROM lidi l WHERE 
     prijmeni=l.prijmeni AND jmeno=l.jmeno LIMIT 1);

Výběr prvních n řádků ze skupiny

   SELECT * FROM skupiny WHERE id IN (
     SELECT id FROM skupiny s 
       WHERE skupiny.zarazeni = s.zarazeni 
       ORDER BY vek LIMIT 2) 
     ORDER BY zarazeni, vek;

Dalším možným řešením je použít SELF JOIN. K tomu, abych mohl tento problém vyřešit, musím dotaz přeformulovat. Např. ve výše uvedeném modelu se musím ptát po osobách, pro které platí, že maximálně jedna osoba ve skupině je starší. 

SELECT s1.* 
   FROM skupiny s1
        LEFT JOIN
        skupiny s2
        ON s1.zarazeni = s2.zarazeni AND s1.vek < s2.vek
  GROUP BY s1.id, s1.zarazeni
  HAVING COUNT(s2.id) <= 1
  ORDER BY s1.zarazeni, COUNT(s2.id);

Přetypování na varchar

Každý datový typ v PostgreSQL musí mít zaregistrovanou input a output funkci pro převod z a do typu cstring (viz CREATE TYPE). Toho můžeme využít pro přetypování do textu, pokud implicitní přetypování neexistuje. Tento trik je počínaje verzí 8.3 zbytečný. 

 testdb011=# SELECT '(1,1)'::point::varchar;
 ERROR:  cannot cast type point to character varying

ale 

 testdb011=# SELECT textin(point_out('(1,1)'::point))::varchar;
  textin
 --------
  (1,1)
 (1 řádka)

Dvacet novějších triků

Citováno z „http://postgres.cz/index.php?title=SQL_Triky_I.&oldid=696“