SQL Triky

SQL je ukázkou jazyka, kdy jeden problém můžeme vyřešit deseti různými způsoby. Různě efektivně. Zde uvedené příklady jsou ukázkou, jak efektivně využívat možností PostgreSQL.

Starších dvacet triků

Zpracování logů ve formátu JSON s nástrojem jq

Autor: Pavel Stěhule

Bez dalšího zpracování nejsou logy ve formátu JSON extra čitelné. Díky nástroji jq filtrování, transformace do formátu tsv nebo csv je jednoduché.

cat postgresql-Sun.json | jq 'select(.error_severity=="ERROR").message'|sort -n | uniq -c
   1 "canceling statement due to user request"
   1 "column \"de.id\" must appear in the GROUP BY clause or be used in an aggregate function"
   1 "column reference \"modify_time\" is ambiguous"
   3 "column \"us.show_name\" must appear in the GROUP BY clause or be used in an aggregate function"
  24 "current transaction is aborted, commands ignored until end of transaction block"
   3 "deadlock detected"

cat postgresql-Sun.json | \
  jq -r 'select(.error_severity=="ERROR") | [.timestamp, .user, .ps, .error_severity, .message ] |  @csv' \
| pspg --csv

Použití clipboardu v psql

Autor: Pavel Stěhule

psql jako terminálová aplikace clipboard nativně nepodporuje, nicméně díky možnosti přesměrování výstupu a speciálním terminálovým aplikacím pro práci se schránkou (wlclipboard, xclip, pbcopy), můžeme přesměrovat výsledek dotazu do clipboardu, a ten pak vložit například do LibreOffice:

select * from obce \g (format=csv) | cat > tmpfile.csv; libreoffice --view --calc tmpfile.csv "--infilter='Text - txt - csv (StarCalc)':44,34,0,1"; rm tmpfile.csv

select * from pg_class limit 10 \g (format=csv tuples_only=off csv_fieldsep='\t') | wl-copy -t application/x-libreoffice-tsvc

\set gclip '\\g (format=csv tuples_only=off csv_fieldsep=\'\\t\') | wl-copy -t application/x-libreoffice-tsvc'
select * from pg_class limit 10 :gclip

Pole dokumentů typu jsonb

Autor: Pavel Stěhule

V Postgresu můžeme pracovat s poli libovolných datových typů, které nejsou samy poli. Pro přístup se používá zápis indexu v hranatých závorkách. Počínaje PostgreSQL 14 můžeme tento zápis použít i pro adresování hodnoty v jsonb dokumentu. Tady ovšem začíná schizofrénní situace - potřebujeme zápisem oddělit indexy pole, a indexy (pozicování) v dokumentu. To bohužel Postgres neumí (ani na úrovni parseru, ani interně - pole polí nejsou podporována). Můžeme si ale pomoci vložením kompozitní hodnoty. Tento trik se používá i v případě, že chceme pracovat s polem polí. Přímo to možné není, ale je možné pracovat s polem kompozitních hodnot, které obsahují pole:

CREATE TYPE jsonb_singleval AS (v jsonb);

DO $$
  DECLARE a jsonb_singleval[];
BEGIN
  a[1].v['x1'] = jsonb '100';
  RAISE NOTICE 'first item %', a[1].v;
END; 
$$;

NOTICE:  first item {"x1": 100}
DO

Přenesení hodnoty z klienta na server

Autor: Daniel Verite

Proměnnou PGOPTION lze pouužít i pro zákaznické proměnné:

$ env PGOPTIONS="-c os.user=$USER" psql -d postgres
psql (12.4 (Debian 12.4-1.pgdg90+1))
Type "help" for help.

postgres=> show "os.user";
 os.user
---------
 daniel
(1 row)

případně s escapeovanými hodnotami:

$ env PGOPTIONS="-c os.user=user\\ is\\ $USER" psql -d postgres
psql (12.4 (Debian 12.4-1.pgdg90+1))
Type "help" for help.

postgres=> show "os.user";
    os.user     
----------------
 user is daniel
(1 row)

Logování všech příkazů z aplikace

Autor: Pavel Stěhule

Nastavte systémovou proměnnou PGOPTIONS - vynucený login všech příkazů pg_dump(u):

PGOPTIONS="-c log_min_duration_statement=0" pg_dump -s postgres > /dev/null

Vacuuování pouze systémových tabulek

Autor: Pavel Stěhule

psql -At  -c  "select 'VACUUM ANALYZE pg_catalog.'||table_name from information_schema.tables where table_schema = 'pg_catalog' and table_type <> 'VIEW'" db_test \
 | psql -S db_test

Nastavení položek DETAIL a HINT u vyjímky z PLPythonu

Autor: Pavel Stěhule PLPythonu neumožňuje nastavit všechny položky, které lze nastavit u vyjímek PostgreSQL. Nicméně položky DETAIL, HINT nastavit lze:

postgres=# do $$
x = plpy.SPIError('Nazdarek'); 
x.spidata = (100, "Some detail", "some hint", None, None); 
raise x;
$$ language plpythonu;
ERROR:  plpy.SPIError: Nazdarek
DETAIL:  Some detail
HINT:  some hint
CONTEXT:  Traceback (most recent call last):
  PL/Python anonymous code block, line 4, in <module>
    raise x;
PL/Python anonymous code block

Aliasování psql

Autor: Pavel Stěhule

Na psql nemám rád jeho defaultní přihlašování k databázi stejného jména jako je účet uživatele. Pomocí funkce v bashi lze toto chování změnit:

function psql { 
if [[ $# -eq 0 ]]; then
   env psql postgres
else
  env psql "$@"
fi
}

Poznámka - téhož lze dosáhnout nastavením systémové proměnné PGDATABASE.

Nastavení kódování výstupu z příkazové řádky

[pavel@localhost regress]$ PGCLIENTENCODING='latin2' psql -c "SHOW client_encoding" postgres
 client_encoding 
-----------------
 LATIN2
(1 row)

Generování pěkně (pretty) formátovaného xml dokumentu

Autor: Pavel Stěhule (s využitím zdrojů z internetu - xslt šablona)

Extenze xml2 obsahuje podporu xslt transformací - funkci xslt_process. S touto funkcí lze jednoduše přeformátovat generovaný xml dokument:

create or replace function xml_pretty(xml)
returns xml as $$
  select xslt_process($1,
'<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:strip-space elements="*" />
<xsl:output method="xml" indent="yes" />
<xsl:template match="node() | @*">
<xsl:copy>
<xsl:apply-templates select="node() | @*" />
</xsl:copy>
</xsl:template>
</xsl:stylesheet>')::xml
$$ language sql immutable strict;

Nezbytná ukázka:

postgres=# select xml_pretty('<root><a><b>some text</b></a></root>');
      xml_pretty      
----------------------
 <root>              +
   <a>               +
     <b>some text</b>+
   </a>              +
 </root>             +
 
(1 row)

Logování komunikace mezi klientem a Postgresem

Autor: Pavel Stěhule

Expertem na monitoring protokolu PostgreSQL je pgShark.

unbuffer ./pgs-debug --host 172.30.34.72 -i lo --port 6432 | while read line; do echo `date +"%T.%3N"` $line; done | gzip > /mnt/ebs/pgsharklog.gz

Rychlé nahrazení indexu v omezení primárního klíče

Autor: Petr Novák

Pokud potřebujete na produkčním serveru rychle reindexovat indexy primárních klíčů větších tabulek, může Vám pomoci následující postup:

BEGIN;
CREATE UNIQUE INDEX CONCURRENTLY tab_pkey_idx2 ON tab(id);
ALTER TABLE tab
   DROP CONSTRAINT tab_pkey CASCADE, 
   ADD CONSTRAINT tab_pkey PRIMARY KEY USING INDEX tab_pkey_idx2;
ALTER TABLE second_tab
   ADD CONSTRAINT second_tab_fkey FOREIGN KEY (tab_id) REFERENCES tab(id) NOT VALID;
COMMIT;

V dalším kroku lze zvalidovat omezení (constraint) s cizím klíčem.

Parametrizace shellového skriptu obsahující příkaz DO

Autor: Pavel Stěhule

Příkaz DO nepodporuje parametry - prostřednictvím psql proměnných je ale určitou parametrizaci provést:

bash-4.1$ cat test.sh
echo "
set myvars.msgcount TO :'msgcount'; 
DO \$\$ 
BEGIN 
  FOR i IN 1..current_setting('myvars.msgcount')::int LOOP 
    RAISE NOTICE 'Hello';
  END LOOP; 
END \$\$" | psql postgres -v msgcount=$1

bash-4.1$ sh test.sh 3
SET
Time: 0.341 ms
NOTICE:  Hello
NOTICE:  Hello
NOTICE:  Hello
DO
Time: 2.415 ms

Test zda připojený server běží jako slave

[postgres@localhost:/opt/PostgreSQL/9.3~]$ psql -p 5433 -c "select pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 f
(1 row)

Redukce časové řady s použitím analytických funkcí

Autor: Pavel Stěhule

Představme si, že máme následující knihu jízd, a chceme ji zredukovat tak, že pokud následují jízdy za stejným účelem, jak je nahradíme jednou jízdou odpovídající součtu nahrazených jízd. K tomu můžeme s výhodou použít analytické funkce:

postgres=# select * from jizdy order by kdy;
         kdy         │   proc   │ kolik
═════════════════════╪══════════╪═══════
 2014-01-01 08:00:00 │ soukrome │    10
 2014-01-01 09:00:00 │ firemne  │     1
 2014-01-01 10:00:00 │ firemne  │     1
 2014-01-01 10:10:00 │ firemne  │    15
 2014-01-01 11:00:00 │ soukrome │    10
 2014-01-01 12:10:00 │ firemne  │    20
(6 rows)

postgres=# SELECT min(kdy) AS kdy, min(proc) AS proc, sum(kolik) AS kolik 
              FROM (SELECT kdy, proc, kolik, sum(start) OVER (ORDER BY kdy) AS grnum
                       FROM (SELECT *, CASE WHEN proc IS DISTINCT FROM lag(proc, 1) OVER (ORDER BY kdy) THEN 1 ELSE 0 END AS start
                                FROM jizdy) s) s2
             GROUP BY grnum
             ORDER BY kdy;
         kdy         │   proc   │ kolik 
═════════════════════╪══════════╪═══════
 2014-01-01 08:00:00 │ soukrome │    10
 2014-01-01 09:00:00 │ firemne  │    17
 2014-01-01 11:00:00 │ soukrome │    10
 2014-01-01 12:10:00 │ firemne  │    20
(4 rows)

Součet vektorů - (jednodimenzionálních polí)

Autor: Erwin Brandstetter

PostgreSQL nemá speciální funkce pro operace s poly jako vektory. Nicméně díky lateral joinu (PostgreSQL 9.3) není problém základní operace realizovat. Součet vektorů by mohl být implementován následujícím dotazem:

postgres=# SELECT * FROM xx;
    a    
---------
 {1,2,3}
 {3,4,5}
(2 rows)

postgres=# SELECT array_agg(sum) 
              FROM (SELECT sum(a[i]) 
                       FROM xx, 
                            generate_series(1,3) g(i) 
                      GROUP BY i 
                      ORDER BY i) x;
 array_agg 
-----------
 {4,6,8}
(1 row)

Případne s použitím klauzule WITH ORDINALITY (PostgreSQL 9.4):

postgres=# SELECT array(SELECT sum(el) 
              FROM xx, 
                   unnest(a) WITH ORDINALITY x(el, r) 
             GROUP BY r 
             ORDER BY r);
  array  
---------
 {4,6,8}
(1 row)

Určení velikosti zpoždění (posunu) mezi replikou a replikovaným serverem (slaveem a masterem)

Autor: Josh Berkus http://www.databasesoup.com/2014/04/simplifying-replication-position.html#comment-form

Základem je použití funkce pg_xlog_location_diff(loc1, loc2). loc obsahuje descriptor (stav) transakčního logu: '0/0000000' - absolutní začátek, pg_current_xlog_location(), pg_last_xlog_receive_location() nebo pg_last_xlog_replay_location():

-- stáří transakčního logu
SELECT pg_xlog_location_diff ( pg_current_xlog_location(), '0/0000000' );

-- zpoždění v MB v aplikaci transakčního logu - kvuli snížení rizika replikačních kolizí může být replika oddálit aplikaci transakčního logu
SELECT pg_xlog_location_diff ( pg_last_xlog_receive_location(),  pg_last_xlog_replay_location() ) / 1000000;

-- vrátí true, pokud všechny přijaté segmenty transakčního logu jsou aplikovány
SELECT pg_xlog_location_diff ( pg_last_xlog_receive_location(),  pg_last_xlog_replay_location() ) = 0;

Rozbalení pole s přidáním indexu

V některých případech by se hodilo mít funkci, která rozbalí pole, ale k vlastní hodnotě přidá ještě pořadové číslo (index), který je možný použít pro řazení nebo spojování tabulek. Možností jak to udělat je několik:

Autor: Pavel Stěhule (PostgreSQL 9.1)

CREATE OR REPLACE FUNCTION unnest_rownum(anyarray)
RETURNS TABLE (id int, element anyelement) AS $$
BEGIN
  id := 1;
  FOREACH element IN array $1
  LOOP
    RETURN NEXT;
    id := id + 1;
  END LOOP;
  RETURN;
END
$$ LANGUAGE plpgsql; 

postgres=# select * from unnest_rownum(ARRAY['A','B','C']);
 id | element
----+---------
  1 | A
  2 | B
  3 | C
(3 rows)

Autor: Tom Lane (PostgreSQL 9.3)

SELECT i, arraycol[i]
   FROM tab,
        LATERAL generate_subscripts(arraycol, 1) as i;

V 9.4 je možné použít klauzuli WITH ORDINALITY:

postgres=# SELECT * FROM unnest(ARRAY['A','D','C']) WITH ORDINALITY;
 unnest | ordinality 
--------+------------
 A      |          1
 D      |          2
 C      |          3
(3 rows)

Omezení na pouze jednu hodnotu NULL ve sloupci

Omezení UNIQUE zajišťuje unikátní hodnoty v daném sloupci (sloupcích), nad kterým je deklarováno. NULL se ovšem nebere jako hodnota, která by byla porovnatelná s jakoukoliv jinou hodnotou a proto omezení UNIQUE vůbec neřeší hodnoty NULL. Což se nám někdy nehodí. Jedinečnost hodnoty NULL lze jednoduše zajistit podmíněným funkcionálním indexem (podmíněným - aby byl index malý, funkcionální - NULL by bylo ignorováno:

postgres=# CREATE TABLE omega(a int UNIQUE);
CREATE TABLE

postgres=# INSERT INTO omega VALUES(NULL);
INSERT 0 1

postgres=# INSERT INTO omega VALUES(NULL);
INSERT 0 1

postgres=# TRUNCATE omega;
TRUNCATE TABLE

postgres=# CREATE UNIQUE INDEX ON omega ((1)) WHERE a IS NULL;
CREATE INDEX

postgres=# INSERT INTO omega VALUES(NULL);
INSERT 0 1

postgres=# INSERT INTO omega VALUES(NULL);
ERROR:  duplicate key value violates unique constraint "omega_expr_idx"
DETAIL:  Key ((1))=(1) already exists.

postgres=# UPDATE omega SET a = 10;
UPDATE 1

postgres=# INSERT INTO omega VALUES(NULL);
INSERT 0 1
Time: 7.601 ms

Konverze bytea do blobu

Autor: unknown

Uloží bytea řetězec do lo objektu.

CREATE OR REPLACE FUNCTION make_lo(bytea)
RETURNS oid AS $$
DECLARE
  loid oid;
  fd integer;
  bytes integer;
BEGIN
  loid := lo_creat(-1);
  fd := lo_open(loid, 131072);
  bytes := lowrite(fd, $1);
  IF (bytes != LENGTH($1)) THEN
    RAISE EXCEPTION 'Not all data copied to blob';
  END IF;
  PERFORM lo_close(fd);
  RETURN loid;
END;
$$ LANGUAGE plpgsql STRICT;

Pouze jeden sloupec z vybrané skupiny sloupců může být NULL

Autor: Daniel Vérité

Jak jednoduše zjistit kolik hodnot je NULL?

CREATE OR REPLACE FUNCTION public.null_count(VARIADIC anyarray)
 RETURNS integer
 LANGUAGE sql
AS $function$
  SELECT sum(CASE WHEN v IS NULL THEN 1 ELSE 0 END)::int FROM unnest($1) g(v)
$function$

pak použití:

CREATE TABLE xxx(
  a int,
  b int,
  c int,
  CHECK (null_count(a,b,c) <= 1))

Nastavení application_name pro jednoúčelové skripty využívající psql

Autor: Erik Rijkers

Application_name je atribut připojení, který můžeme připojit k záznamům v logu a podle něj identifikovat zdroj SQL příkazu. Možností je více

[pavel@localhost ~]$ PGAPPNAME=splunk psql -c "select application_name from pg_stat_activity where pid = pg_backend_pid();" postgres
 application_name 
------------------
 splunk
(1 row)

[pavel@localhost ~]$ psql -c "select application_name from pg_stat_activity where pid = pg_backend_pid();" "dbname=postgres application_name=splunk"
 application_name 
------------------
 splunk
(1 row)

[pavel@localhost ~]$ psql -c "select application_name from pg_stat_activity where pid = pg_backend_pid();" postgresql://localhost/postgres?application_name=splunk
 application_name 
------------------
 splunk
(1 row)

Získání N unikátních nejpodobnějších hodnot s využitím KNNsearch a modulu pg_tgrm

Autor: Tom Lane

SELECT DISTINCT ON (nazobce <-> 'Benešov') nazobce, nazobce<->'Benešov' 
   FROM obce 
   ORDER BY nazobce <-> 'Benešov' 
   LIMIT 10;

        nazobce        | ?column? 
-----------------------+----------
 Benešov               |        0
 Benešovice            | 0.416667
 Dolní Benešov         | 0.428571
 Benešov u Semil       |      0.5
 Benešov nad Černou    | 0.578947
 Benešov nad Ploučnicí | 0.636364
 Benecko               | 0.666667
 Benetice              | 0.692308
 Bečov                 | 0.727273
 Bezkov                |     0.75
(10 rows)

postgres=# EXPLAIN SELECT DISTINCT ON (nazobce <-> 'Benešov') nazobce, nazobce<->'Benešov' 
   FROM obce 
   ORDER BY nazobce <-> 'Benešov' 
   LIMIT 10;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..2.76 rows=10 width=10)
   ->  Unique  (cost=0.00..1474.75 rows=5341 width=10)
         ->  Index Scan using obce_nazobce_idx on obce  (cost=0.00..1433.14 rows=16644 width=10)
               Order By: (nazobce <-> 'Benešov'::text)
(4 rows)

Využití vlastního přetypování při migraci Oracle do PostgreSQL

Autor: Pavel Stěhule Oracle nepodporuje datový typ boolean, který se obvykle nahrazuje typem varchar(1) nebo number(1). Při změně JDBC driveru dochází k problémům, neboť do varcharu o šířce jednoho znaku není možné uložit hodnoty true nebo false. V rámci dané databáze lze nahradit výchozí konverzi boolean->varchar vlastní konverzní funkcí (UPDATE musí být proveden pod uživatelem s právy superusera) a tím problém vyřešit bez nutnosti změny tabulek - jakkoliv změna typu v tabulce je čistší řešení:

CREATE OR REPLACE FUNCTION public.boolcast_varchar(boolean)
 RETURNS character varying
 LANGUAGE sql
AS $$
  SELECT CASE WHEN $1 THEN 't' ELSE 'f' END 
$$

UPDATE pg_cast SET castfunc='boolcast_varchar'::regproc, 
                   castcontext='i' 
  WHERE castsource=16 and casttarget=1043;

CREATE TABLE test(a varchar(1));
INSERT INTO test VALUES(true);

Generování JSONu

Autor: Merlin

select
  row_to_json(t1)
from (
  select
    'joe' as username,
    (select project from (values(1, 'prj1')) as project(project_id,
project_name)) as project
) t1;

Trimování hodnot v sloupci s typem pole

Autor: a_horse_with_no_name

postgres=# select * from g;
               a                
--------------------------------
 {" ssss ssss","sss  ssss    "}
 {" ssss ssssaaaa "}
(2 rows)

postgres=# select row_number() over(), unnest(a) e
            from g;
 row_number |        e        
------------+-----------------
          1 |  ssss ssss
          1 | sss  ssss    
          2 |  ssss ssssaaaa 
(3 rows)

postgres=# select array_agg(trim(x.e)) 
              from (select row_number() over() rn, unnest(a) e
                       from g) x 
             group by rn;
         array_agg         
---------------------------
 {"ssss ssss","sss  ssss"}
 {"ssss ssssaaaa"}
(2 rows)

Tento trik by bylo možné použít i jinde - místo chybějící klauzule LATERAL - jinak tenhle trik je implementačně závislý - záleží na pořádí vyhodnocování SRF a analytických funkcí. Počínaje 9.3 používejte raději klauzuli LATERAL.

Jednuduché zpracování výsledku dotazu v Bashi

Autor: Pavel Stěhule

I v Bashi lze relativně snadno zpracovat výsledek SQL dotazu:

pavel ~ $ psql postgres -A -t --field-separator=" " \
>   -c "copy (select 1,'Ahoj Svete', i 
>                from generate_series(1,3) g(i)) 
>         to stdout delimiter ' '" | \
> while read var1 var2 var3; 
> do 
>    echo "a=$var1,b=$var2,c=$var3 "; 
> done
a=1,b=Ahoj Svete,c=1 
a=1,b=Ahoj Svete,c=2 
a=1,b=Ahoj Svete,c=3 

Podobné řešení navrh Hubert Depesz Lubaczewski:

psql -qAtX -c "copy (select * from t) to stdout" | while IFS=$'\t' read -r a b c; do echo -e "a=[$a] b=[$b] c=[$c]"; done

Přilepení komentáře k SQL příkazu

Autor: Thomas Kellerer

V některých případech bychom potřebovali okomentovat SQL příkaz takovým způsobem, abychom v logu PostgreSQL uviděli SQL příkaz včetně komentáře. Řešením je vložený komentář.

SELECT /* my comments, that I would to see in PostgreSQL log */
       a, b, c
   FROM mytab;

Odhlášení všech přihlášených uživatelů

Autor: Pavel Stěhule

Jako uživatel postgres nebo jiný superuser proveďte dotaz:

postgres=# select * from pg_stat_activity ;
 datid | datname  |  pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |         
-------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+
 12894 | postgres | 21091 |       10 | postgres | psql             |             |                 |          -1 | 2012-03-06 09:16:02.466983+01 |
 12894 | postgres | 21103 |    16384 | pavel    | psql             |             |                 |          -1 | 2012-03-06 09:17:02.827352+01 |         
 12894 | postgres | 21104 |    16384 | pavel    | psql             |             |                 |          -1 | 2012-03-06 09:17:12.176979+01 |         
(3 rows)

postgres=# select pg_terminate_backend(pid) 
               from pg_stat_activity 
              where pid <> pg_backend_pid() ;
 pg_terminate_backend 
----------------------
 t
 t
(2 rows)

postgres=# select * from pg_stat_activity ;
 datid | datname  |  pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |         
-------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+
 12894 | postgres | 21091 |       10 | postgres | psql             |             |                 |          -1 | 2012-03-06 09:16:02.466983+01 |
(1 row)

Dohledání děr v časové posloupnosti s využitím window funkcí

Zdroj: Magnus Hagander http://blog.hagander.net/archives/203-Finding-gaps-in-partitioned-sequences.html

Funkci lag je možné použít na libovolnou seřazenou posloupnost:

SELECT * FROM (
   SELECT
      gropid,
      year,
      month,
      seq, 
      seq-lag(seq,1) OVER (PARTITION BY groupid, year, month ORDER BY seq) AS gap FROM mytable
) AS t
WHERE NOT (t.gap=1)
ORDER BY groupid, year, month, seq

Konverze BLOBu do Bytea

Zdroj: http://snipplr.com/view/62576/convert-oid-to-bytea/

CREATE OR REPLACE FUNCTION merge_oid(val oid)
RETURNS bytea AS $$
DECLARE 
  merged bytea; 
  arr bytea;
BEGIN
  FOR arr IN SELECT DATA 
                FROM pg_largeobject 
               WHERE loid = val 
               ORDER BY pageno 
  LOOP
    IF merged IS NULL THEN
      merged := arr;
    ELSE
      merged := merged || arr;
    END IF;
  END LOOP;
  RETURN merged;
END
$$ LANGUAGE plpgsql;

Získání prvního nezamčeného záznamu z tabulky

Při implementaci fronty, která má více konzumentů se muže hodit následující trik:

postgres=# select * from queue;
 id | processed |  cmd  
----+-----------+-------
  1 | f         | task1
  2 | f         | task2
  3 | f         | task2
  4 | f         | task3
(4 rows)

Použití:

-- konzument1
postgres=# begin;
BEGIN
postgres=# select * from queue where not processed and pg_try_advisory_xact_lock(id) for update limit 1;
 id | processed |  cmd  
----+-----------+-------
  1 | f         | task1
(1 row)

postgres=# update queue set processed = true where id = 1;
UPDATE 1
postgres=# commit;
COMMIT

-- konzument2
postgres=# begin;
BEGIN
postgres=# select * from queue where not processed and pg_try_advisory_xact_lock(id) for update limit 1;
 id | processed |  cmd  
----+-----------+-------
  2 | f         | task2
(1 row)

postgres=# update queue set processed = true where id = 2;
UPDATE 1
postgres=# commit;
COMMIT

Pomocí zámků lze realizovat neblokující se paralelní zpracování fronty.

Funkce pro dokódování url kódování

Autor: Marti Raudsepp

CREATE OR REPLACE FUNCTION url_decode(input text) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
 bin bytea = '';
 byte text;
BEGIN
 FOR byte IN (select (regexp_matches(input, '(%..|.)', 'g'))[1]) LOOP
   IF length(byte) = 3 THEN
     bin = bin || decode(substring(byte, 2, 2), 'hex');
   ELSE
     bin = bin || byte::bytea;
   END IF;
 END LOOP;
 RETURN convert_from(bin, 'utf8');
END
$$;

Použití:

ohs=# select url_decode('Hell%C3%B6%20World%21');
  url_decode  
──────────────
 Hellö World!
(1 row)

Konfigurace emacsu pro zvýraznění syntaxe při editaci PL/pgSQL funkce aktivovanou z konzole

Autor: Peter Eisentraut

do souboru .profile

PSQL_EDITOR=emacs;
export PSQL_EDITOR.

do souboru .emacs

(add-to-list 'auto-mode-alist
            '("/psql.edit.[0-9]+\\'" . sql-mode))

Vyhledávání prefixu pomocí fulltextu s českou konfigurací

Tento blok vlastně není trik, ale varování - při vyhledávání prefixu jsem byl překvapen chováním:

postgres=# select to_tsvector('cs', 'Stěhule') @@ to_tsquery('cs','Stě:*') ;
 ?column? 
──────────
 f
(1 row)

Očekával jsem true - tj. "Stě" je prefixem "Stěhule". Po chvíli zkoumání a zjišťování, jestli se nejedná o chybu PostgreSQL, jsem zjistil, že se o chybu PostgreSQL nejedná - je to chování TSearch, které se snaží použít stejnou transformaci i na prefixy. V tomto případě se "Stě" transformuje na "sto", a zákonitě se nic nenajde. Pro vyhledávání prefixu proto doporučuji nepoužívat konfiguraci cs, ale konfiguraci simple:

postgres=# select to_tsvector('simple', 'Stěhule') @@ to_tsquery('simple','Stě:*') ;
 ?column? 
──────────
 t
(1 row)

Odstranění duplicit s použitím window funkcí

Window funkce nám umožňují velice efektivně odstranit duplicitní funkce:

DELETE FROM tab 
  WHERE id IN (SELECT id 
                  FROM (SELECT row_number() OVER (PARTITION BY duplicitni_sloupec), id 
                           FROM tab) x 
                 WHERE x.row_number > 1);

Optimalizovaná verze pro PostgreSQL s použitím ctid:

DELETE FROM tab 
  WHERE (ctid, tableoid) = ANY(ARRAY(SELECT (ctid, tableoid) 
                  FROM (SELECT row_number() OVER (PARTITION BY duplicitni_sloupec), ctid 
                           FROM tab) x 
                 WHERE x.row_number > 1));

Převod bytea do typu text

Pokud potřebujeme obsah typu bytea převést na odpovídající textovou hodnotu (za předpokladu, že obsah bytea odpovídá přímo kódům znaků), lze použít funkci:

CREATE OR REPLACE FUNCTION bytea_to_text(bytea) 
RETURNS text AS $$
SELECT convert_from($1, current_setting('server_encoding'))
$$ LANGUAGE sql;

xxx=# SELECT bytea_to_text('žluťoučký kůň se napil žluté vody'::bytea);
           bytea_to_text           
───────────────────────────────────
 žluťoučký kůň se napil žluté vody
(1 row)

Dynamické modifikování položky záznamu

V PL/pgSQL není možné (jednoduše) modifikovat položku kompozitního typu, v případě, že položka je určená až v době běhu funkce. K dispozici je několik řešení v C, které jsou velice rychlé. Ty ovšem v některých případech nemusí být k dispozici, případně je nelze použít. V takovém případě lze použít následující funkci:

CREATE OR REPLACE FUNCTION x.setfield2(anyelement, text, text)
 RETURNS anyelement
 LANGUAGE plpgsql
AS $function$
DECLARE 
  _name text;
  _values text[];
  _value text;
  _attnum int;
BEGIN
  FOR _name, _attnum
     IN SELECT a.attname, a.attnum
           FROM pg_catalog.pg_attribute a 
          WHERE a.attrelid = (SELECT typrelid
                                 FROM pg_type
                                WHERE oid = pg_typeof($1)::oid) 
  LOOP
    IF _name = $2 THEN
      _value := $3;
    ELSE
      EXECUTE 'SELECT (($1).' || quote_ident(_name) || ')::text' INTO _value USING $1;
    END IF;
    _values[_attnum] :=  COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', '');
  END LOOP;
  EXECUTE 'SELECT (' || pg_typeof($1)::text || '''(' || array_to_string(_values,',') || ')'').*' INTO $1; 
  RETURN $1;
END;
$function$;

postgres=# select setfield2(mypoint '(10,)', 'b', '33');
 setfield2 
───────────
 (10,33)
(1 row)

Time: 9,480 ms

Autor: Pavel Stěhule

Erwin Brandstetter prišel ještě s lepším řešením, které je jednodušší a rychlejší:

-- Erwin 1
CREATE OR REPLACE FUNCTION x.setfield3(anyelement, text, text)
RETURNS anyelement
AS $body$
DECLARE
 _list text;

BEGIN
_list := (
   SELECT string_agg(x.fld, ',')
   FROM   (
      SELECT CASE WHEN a.attname = $2
              THEN quote_literal($3)
              ELSE quote_ident(a.attname)
             END AS fld
      FROM   pg_catalog.pg_attribute a 
      WHERE  a.attrelid = (SELECT typrelid
                           FROM   pg_type
                           WHERE  oid = pg_typeof($1)::oid) 
      ORDER BY a.attnum
   ) x
);

EXECUTE '
SELECT ' || _list || '
FROM   (SELECT $1.*) x'
USING  $1
INTO   $1;

RETURN $1;
END;
$body$ LANGUAGE plpgsql;

a tuto funkci ještě dokázal zrychlit:

CREATE FUNCTION f_setfield(INOUT _comp_val anyelement, _field text, _val text)
  RETURNS anyelement AS
$func$
BEGIN

EXECUTE 'SELECT ' || array_to_string(ARRAY(
      SELECT CASE WHEN attname = _field
                THEN '$2'
                ELSE '($1).' || quote_ident(attname)
             END AS fld
      FROM   pg_catalog.pg_attribute
      WHERE  attrelid = pg_typeof(_comp_val)::text::regclass
      AND    attnum > 0
      AND    attisdropped = FALSE
      ORDER  BY attnum
      ), ',')
USING  _comp_val, _val
INTO   _comp_val;

END
$func$ LANGUAGE plpgsql;

Snadné určení věku z datumu narození

Pomocí funkce age lze snadno určit věk z dne narození:

postgres=# SELECT CURRENT_DATE;
    date    
------------
 2011-09-20
(1 row)

postgres=# SELECT EXTRACT(YEAR FROM age('1972-08-20'::date));
 date_part 
-----------
        39
(1 row)

Identifikace master/slave serveru

K získání stavu serveru lze použít následující funkci:

SELECT pg_is_in_recovery();

Autor: Simon Riggs

Určení md5 obsahu tabulky

Pro porovnání dvou hodnot lze použít funkci md5. V psql lze pro tento případ použít systémovou proměnnou PAGER:

[robert@client-168] export PAGER=md5
-=[11:40:25 Thu Sep 01]=---=[ pagila-0.10.1 ]=-
 [robert@client-168] psql -hlocalhost -dpagila
psql (9.0.4, server 9.1beta3)
pagila=# select * from actor order by actor_id;
f381ebdefe0aada9c0bc14e657962c1f

Autor: Robert Treat

LIKE vůči seznamu hodnot

PostgreSQL (stejně tak SQL) neumožňuje zadat operátoru (I)LIKE více než jeden vzorový řetězec. Takže

-- this is not valid
WHERE sloupec LIKE ('%neco%','%necojineho%') 

není syntakticky správný. Požadovaný test lze zapsat pomocí polí (a nebo vyřešit regulárním výrazem):

-- this is valid
WHERE slopec ~~* any(array['%neco%', '%necojineho']));

Autor: Tom Lane

Vynucení použití unikátního názvu kurzoru v PL/pgSQL

Za normálních okolností vede použití PL/pgSQL kurzoru k vytvoření SQL kurzoru se stejným názvem, jako má PL/pgSQL kurzor. Toto chování v určitých případech nemusí být žádoucí. Řešením je přiřazení hodnoty NULL PL/pgSQL kurzoru. V takovém případě je otevřen SQL kurzor s unikátním názvem:

DECLARE
  mycursor CURSOR FOR SELECT * FROM int4_tbl WHERE f1 > id;
  newid INTEGER;
  out  TEXT;
BEGIN
  out := id::text || ' ';
  mycursor := null;
  OPEN mycursor;
  ..

Iterace napříč proměnnou typu RECORD v triggeru

Pro tuto funkci je vyžadována min. verze 8.4 - autor Tom Lane

CREATE OR REPLACE FUNCTION dynamic_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    ri RECORD;
    t TEXT;
BEGIN
    RAISE NOTICE E'\n    Operation: %\n    Schema: %\n    Table: %',
        TG_OP,
        TG_TABLE_SCHEMA,
        TG_TABLE_NAME;
    FOR ri IN
        SELECT ordinal_position, column_name, data_type
        FROM information_schema.columns
        WHERE
            table_schema = quote_ident(TG_TABLE_SCHEMA)
        AND table_name = quote_ident(TG_TABLE_NAME)
        ORDER BY ordinal_position
    LOOP
        EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO t USING NEW;
        RAISE NOTICE E'Column\n    number: %\n    name: %\n    type: %\n    value: %.',
            ri.ordinal_position,
            ri.column_name,
            ri.data_type,
            t;
    END LOOP;
    RETURN NEW;
END; $$;

pozn. Tato technika vyžaduje 1 dynamický SQL dotaz na každou položku záznamu - mnohem efektivnější je použití funkcí z knihovny PL Toolbox nebo contrib modulu Hstore

Zobrazení zdrojového kódu funkce

Pokud znáte oid funkce, je velice jednoduché získat její generující DDL příkaz:

postgres=# select oid from pg_proc where proname = 'fu';
  oid  
-------
 16389
(1 row)

postgres=# select pg_get_functiondef(16389);
                         pg_get_functiondef                          
---------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION public.fu(anyelement)                   
  RETURNS anyelement                                                
  LANGUAGE plpgsql                                                  
 AS $function$
begin 
  raise notice '%', $1; 
  return $1; 
end; $function$
 
(1 row)

Seznam souborů v rpm balíčku

Sice tento bod nesouvisí s PostgreSQL, nicméně věřím, že je přínosem pro každého hackera:

[pavel@pavel-stehule ~]$ rpm --query --filesbypkg perl-Net-SCP
perl-Net-SCP              /usr/share/doc/perl-Net-SCP-0.08
perl-Net-SCP              /usr/share/doc/perl-Net-SCP-0.08/Changes
perl-Net-SCP              /usr/share/doc/perl-Net-SCP-0.08/README
perl-Net-SCP              /usr/share/man/man3/Net::SCP.3pm.gz
perl-Net-SCP              /usr/share/perl5/Net
perl-Net-SCP              /usr/share/perl5/Net/SCP.pm

rpm -qa          zobrazi vsechny balicky
rpm -qf soubor   dohleda balicek obsahujici soubor

Operace UPSERT pomocí triggeru

Zdroj: http://database-programmer.blogspot.com/2009/06/approaches-to-upsert.html PostgreSQL nepodporuje příkaz MERGE, který spojuje funkce příkazů INSERT a UPDATE. Existuje několik řešení - představované řešení je založené na použití BEFORE TRIGGERU. Uvnitř triggeru si zjistíme, zda-li se jedná o existující řádek, a pokud ano, tak provedeme UPDATE a vrátíme NULL, čímž se zabrání zápisu, jinak vrátíme původní hodnotu, která se zapíše:

CREATE OR REPLACE FUNCTION upsert_body()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE tab SET val = new.val WHERE key = new.key;
  IF NOT FOUND THEN
    RETURN new;
  ELSE
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql;	

Zaokrouhlení času na nejblížší pětiminutovku

Autor: Pavel Stěhule

CREATE OR REPLACE FUNCTION round_time(TIMESTAMP WITH TIME ZONE) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
  SELECT date_trunc('hour', $1) + INTERVAL '5 min' * round(date_part('minute', $1) / 5.0) 
$$ LANGUAGE SQL;

postgres=# select now(), round_time(now());
              now              |       round_time       
-------------------------------+------------------------
 2010-09-19 09:59:17.053782+02 | 2010-09-19 10:00:00+02
(1 row)

Funkce to_string a to_array

Oblíbené funkce array_to_string a string_to_array nepodporují pole obsahující NULL, resp. hodnotu NULL tiše ignorují. Nové funkce to_string a to_array jsou obdobou výše uvedených funkcí, přičemž umožňují nahradit NULL definovaným řetězcem / definovaný řetězec hodnotou NULL:

CREATE OR REPLACE FUNCTION to_string(anyarray, sep text, nullstr text DEFAULT '') 
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT coalesce(v::text, $3) 
                                FROM unnest($1) g(v)),
                       $2)
$$ LANGUAGE sql;

postgres=# select to_string(array[1,2,3,4,null,5],','); 
 to_string  
------------
 1,2,3,4,,5
(1 row)

postgres=# select to_string(array[1,2,3,4,null,5],',','<NULL>');
    to_string     
------------------
 1,2,3,4,<NULL>,5
(1 row)

CREATE OR REPLACE FUNCTION to_array(text, sep text, nullstr text DEFAULT '') 
RETURNS text[] AS $$ 
  SELECT ARRAY(SELECT CASE 
                           WHEN v = $3 THEN NULL::text 
                           ELSE v END 
                  FROM unnest(string_to_array($1,$2)) g(v)) 
$$ LANGUAGE sql;

postgres=# select to_array('1,2,3,4,,5',',');
     to_array     
------------------
 {1,2,3,4,NULL,5}
(1 row)

postgres=# select to_array('1,2,3,4,<NULL>,5',',','<NULL>');
     to_array     
------------------
 {1,2,3,4,NULL,5}
(1 row)

Tichá instalace na MS Windows (neověřeno)

zdroj: http://www.dbforums.com/postgresql/1651789-postgres-silent-installation.html

REM create Windows user
net user postgres windows_password /ADD

REM create a PW file for the Postgres super user
echo my_secret_postgres_password > password.txt
unzip postgresql-8.4.2-1-windows-binaries.zip -d mypostgresinstalldir

REM run initdb mypostgresinstalldir\pgsql\bin\initdb -D mydatadir -U postgres --pwfile=password.txt -E UTF8 -A md5

REM register the Postgres windows service
pg_ctl register -N PostgreSQL -U postgres -P windows_password -D mydatadir

Případně další varianta (zdroj:http://code.google.com/p/mysde/source/browse/trunk/mysde/pgsql/install.bat?r=7):

@echo off
rem net user postgres pgsql8@tt /ADD /EXPIRES:NEVER /PASSWORDCHG:NO
rem net localgroup users postgres /delete
rem runas /noprofile /env /user:postgres "initdb -D data -E UTF8 --no-locale -A md5 -U root --pwfile=rootpass.txt"
call ../setEnv.bat
initdb -D data -E UTF8 --no-locale -A md5 -U root --pwfile=rootpass.txt
echo on

startup script:

@echo off
rem runas /noprofile /env /user:postgres "pg_ctl start -w -D data -l pgsql.log"
call ../setEnv.bat
pg_ctl start -w -D %WORK_HOME%\pgsql\data -l pgsql.log
echo on

stop script

@echo off
call ../setEnv.bat
pg_ctl -D data -l pgsql.log stop -m smart
echo on

Vynucení vytištění znaků v konverzní funkci to_char

V určitých případech můžeme požadovat zobrazení znaků, které se shodují s maskami specifikující formát. Text v uvozovkách se neinterpretuje, a tudíž se zobrazí, tak jak je:

postgres=# select to_char(current_timestamp,'YYYY-MM-DDTHH24:MI:SS');
        to_char        
-----------------------
 2010-02-07THH24:38:10
(1 row)

postgres=# select to_char(current_timestamp,'YYYY-MM-DD"T"HH24:MI:SS');
       to_char       
---------------------
 2010-02-07T07:38:22
(1 row)

Autor: Milen A. Radev

Ochrana před nechtěnou konverzí char(n) na text

Prakticky všechny funkce pro operace s řetězci mají parametry typu text. PostgreSQL zajišťuje automatickou konverzi z char(n) na varchar. V některých případech toto chování přináší problémy - zejména ve starších aplikacích, které datový typ char(n) používali (jasné doporučení - typu char(n) se vyhnout obloukem). Jedním z příkladů takového problému je portace starších aplikací a porovnávání char(n) hodnot n znaků:

SELECT ...
  WHERE substr(sloupec,1,4) = 'ab  ';

Tato konstrukce v PostgreSQL není funkční - substr má jako parametr text a vrací text, čímž pádem neplatí 'ab' = 'ab ' (což by platilo u typu char(n)). Naštěstí PostgreSQL dovoluje přetížení funkcí, takže si můžeme zaregistrovat vlastní funkci substr, kde nedojde k přímé konverzi z char(n) do textu:

create or replace function substr(character, int, int) returns character as $$
select substr($1::cstring::text,$2,$3)
$$ language sql;

O něco efektivnější je návrh Toma Lane:

create function substr(char,int,int) returns char
 strict immutable language internal as 'text_substr' ;

Což je o něco špinavější nicméně 100% funkční trik.

postgres=# create table f(a character(5));
CREATE TABLE
postgres=# insert into f values('a'),('ab'),('abc');
INSERT 0 3
postgres=# select * from f;
  a
-------
 a
 ab
 abc
(3 rows)

postgres=# select * from f where substr(a,1,3) = 'a  ';
  a
-------
 a
(1 row)

postgres=# select * from f where substr(a,1,3) = 'ab  ';
  a
-------
 ab
(1 row)

Vytvoření domény časová zóna

Další hezký trik zaslal David E. Wheleer. PostgreSQL nepodporuje žádný datový typ pro uložení časové zóny a neobsahuje ani žádnou funkci, kterou bychom mohli použít pro kontrolu, zda-li zadaný text je časovou zónou či není. Nicméně lze použít operátor AT:

CREATE OR REPLACE FUNCTION is_timezone( tz TEXT ) RETURNS BOOLEAN as $$
BEGIN
 PERFORM now() AT TIME ZONE tz;
 RETURN TRUE;
EXCEPTION WHEN invalid_parameter_value THEN
 RETURN FALSE;
END;
$$ language plpgsql STABLE;

CREATE DOMAIN timezone AS CITEXT
CHECK ( is_timezone( value ) );

Zadání hesla z příkazové řádky pro psql

psql nemá žádný přepínač pro zadání hesla. Proto se uživatelé relativně často ptají, jak neinteraktivně zadat heslo. Jednou z možností je nastavit systémovou proměnnou PGPASSWORD:

postgres=# CREATE USER tom;
CREATE ROLE

postgres=# ALTER USER tom PASSWORD 'tiger';
ALTER ROLE
...
postgres=> \q

[pavel@nemesis ~]$ PGPASSWORD=tiger psql postgres -U tom
Welcome to psql 8.3.8, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=> ...

Výhodou tohoto přístupu, a také smyslem, je skrytí, resp. neuvedení hesla ve výpisu procesů.

Urychlení výpisu seznamu tabulek

Pokud Vaše db obsahuje velký počet tabulek - tisíce až desetitisíce, oceníte následující trik pro verze 8.3 a 8.4:

alter function pg_table_is_visible(oid) cost 10;

Autorem je Tom Lane a v 8.5 bude vyšší cena u funkce pg_table_is_visible nastavena jako výchozí.

Pole polí

Přestože PostgreSQL podporuje n-rozměrné pole, nepodporuje pole polí, a tudíž nepodporuje kumulační agregační funkce nad poli libovolných typů - výsledkem by bylo pole polí, a tato struktura není podporována. Tuto chybějící funkci lze obejít zapouzdřením vstupních polí do kompozitního typu (pole kompozitních hodnot jsou podporovány):

postgres=# create type intarr as (f int[]);
CREATE TYPE

postgres=# select * from t;
      a      
─────────────
 {10,20,30}
 {1,2,3,4,5}
(2 rows)

postgres=# select array_agg(a) from t;
ERROR:  could not find array type for data type integer[]

postgres=# select array_agg(distinct row(a)::intarr) from t;
                array_agg                 
──────────────────────────────────────────
 {"(\"{1,2,3,4,5}\")","(\"{10,20,30}\")"}
(1 row)

postgres=# select (unnest(array_agg(distinct row(a)::intarr))::intarr).f from t;
      f      
─────────────
 {1,2,3,4,5}
 {10,20,30}
(2 rows)

Do konference pgsql-general zaslal Sam Mason.

Pozor na opakované volání funkce použité v rozvoji záznamu

PostgreSQL umožňuje rozvinutí typu record konstrukcí (..).*. Pozor! Pokud zdrojem rozvíjené hodnoty je funkce, pak se funkce volá tolikrát, kolik má rozvoj prvků. Je to z toho důvodu, že rozvoj znamená implicitní náhradu divokého znaku za jednotlivé prvky funkce. Tj. pro funkci foo:

create or replace function foo(out a int, out b int) 
returns record as $$ 
begin 
  raise notice 'start foo'; 
  a := 10; b := 20; 
  return; 
end; 
$$ language plpgsql;

pak SELECT (foo()).* je automaticky nahrazeno SELECT (foo()).a, (foo()).b, přičemž dochází k zbytečnému volání funkce foo. V jazyce SQL to nemusí být problém - dojde k vícenásobné substituci. Ta v plpgsql není možná, a tak může dojít k zbytečnému zpomalení.

postgres=# SELECT (foo()).*;
NOTICE:  start foo
NOTICE:  start foo
 a  │ b  
────┼────
 10 │ 20
(1 row)

--řešení
postgres=# SELECT * FROM foo();
NOTICE:  start foo
 a  │ b  
────┼────
 10 │ 20
(1 row)

postgres=# SELECT (f.foo).* FROM (SELECT foo()) f;
NOTICE:  start foo
 a  │ b  
────┼────
 10 │ 20
(1 row)

---

Starších dvacet triků