Vytvoření sloupce typu text ve Spring Data a PostgreSQL

Pokud necháváte databázové tabulky vytvářet Springem, následující entita

@Entity
@Table(name = "t_my_entity")
class MyEntity (
    @Id
    var id: Long,
    var name: String
)

se vytvoří jako tabulka t_my_entity se sloupcem name typu varchar o maximální velikosti 256 znaků. Pokud potřebujete typ text (retězec o neomezené velikosti) je třeba přidat annotaci.

@Column(columnDefinition = "TEXT")
var input: String

Informace o dotazech v PostgreSQL

Modul pg_stat_statements umožňuje sledovat statistiku vykonávání SQL příkazů. K získání těchto dat poskytuje databázové view (pohled) pg_stat_statements.

Query vrací dané sql, calls počet volání daného sql a mean_time průměrnou dobu vykonání sql. Další zajímavé sloupce jsou max_time a mean_time a další.

select query, calls, mean_time from pg_stat_statements where mean_time > 1 order by calls desc;
+---------------------------------------------------------------------------------------------------+-------+------------------+
|query                                                                                              |calls  |mean_time         |
+---------------------------------------------------------------------------------------------------+-------+------------------+
|select subscripti0_.id as id1_13_, subscripti0_.created_at as created_2_13_, subscripti0_.updated_a|1683638|1.6858550426386856|
|select userfilter0_.id as id1_15_0_, filterdiff2_.id as id1_3_1_, filterkitc4_.id as id1_6_2_, filt|46466  |7.517142569714144 |
+---------------------------------------------------------------------------------------------------+-------+------------------+

Zdroj: postgresql.org/docs/current/pgstatstatements.html

Zobrazení data v PostgreSQL v určité časové zóně

K zobrazení času v určité časové zóně se v PostegreSQL používá at time zone + zóna.

select created_at at time zone 'CEST' as cest, created_at at time zone 'GMT' as gmt, created_at at time zone 'EST' as est from table_name;

+--------------------------+--------------------------+--------------------------+
|cest                      |gmt                       |est                       |
+--------------------------+--------------------------+--------------------------+
|2021-05-06 13:13:44.360009|2021-05-06 11:13:44.360009|2021-05-06 06:13:44.360009|
|2021-05-06 13:13:43.676227|2021-05-06 11:13:43.676227|2021-05-06 06:13:43.676227|
|2021-05-06 13:13:43.578580|2021-05-06 11:13:43.578580|2021-05-06 06:13:43.578580|
+--------------------------+--------------------------+--------------------------+

Zdroj: popsql.com/learn-sql/postgresql/how-to-convert-utc-to-local-time-zone-in-postgresql

Vytvoření uživatele v PostgreSQL

Po instalaci PostgreSQL se do databáze přihlásíte pod uživatelem postgres.

sudo su - postgres
psql -U postgres

Vytvoříte nového uživatele.

create user username password 'password' createdb;

To že byl uživatel vytvořen a jaké má role si v psql zobrazíte příkazem \du.

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 username  | Create DB                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Pro přihlášení do databáze je ještě potřeba, aby uživatel s daným jménem existoval i v systému.

Peer authentication funguje dle dokumentace následovně. Z operačního systému se získá uživatelské jméno a to se považuje za povoleného databázové uživatele. V operačním systému musí existovat uživatel stejného jména jako v databázi a musíme být pod daným uživatelem přihlášeni.
www.vitfo.cz/2018/09/15/prvni-prihlaseni-do-postgresql-v-ubuntu/

K tomu slouží příkaz adduser.

sudo adduser username

Zakázání přihlášení pod daným uživatelem.

sudo passwd -l username

Přihlášení pod novým uživatelem do databáze postgres.

psql -U username-d postgres

Užitečné příkazy psql

Připojení k databázi
psql -d databáze -U uživatel (připojí se do postgres databáze pod uživatelem)
psql -U uživatel -h url -p port (připojí se do postgres databáze, která je na url a portu pod uživatelem)

Pokud jste připojeni
\l (zobrazí seznam databází)
\c databáze (přepne se do databáze)
\c databáze uživatel (přepne se do databáze pod uživatelem)
\d (zobrazí seznam všech tabulek a sekvencí v dané databázi)
\dt (zobrazí seznam všech tabulek v dané databázi)
\ds (zobrazí seznam všech sekvencí v dané databázi)
\dn (zobrazí seznam schémat v dané databázi)
\df (zobrazí seznam funkcí v dané databázi)
\dv (zobrazí seznam views v dané databázi)
\du (zobrazí seznam uživatelů v dané databázi)
\d tabulka (zobrazí informace o dané tabulce)
\d sekvence (zobrazí informace o dané sekvenci)
\e (umožní vám zvolit editor pro psaní příkazu)

Nápověda
\? (zobrazí seznam psql příkazů)

Číst dálUžitečné příkazy psql

PostgreSQL v Dockeru

Chcete si vyzkoušet PostgreSQL, ale nechcete si jej instalovat? Řešením je použít Docker. Zde je jednoduchý postup, jak PostgreSQL v Dockeru spustit a připojit se.

docker pull postgres

Stáhne si image postgresu z úložiště (repozitory).

docker image ls

Zobrazí seznam stažených images.

docker ps

Zobrazí seznam aktuálně běžících kontejnerů.

docker run --rm --name my-postgres -e POSTGRES_PASSWORD=password -d -p 2345:5432 postgres

docker run spustí proces v novém kontejneru
-e nastaví proměnnou prostředí (v totmo případě heslo)
-p prováže veřejný port (nastaven na 2345) s portem postgresu (5432) hostPort:containerPort
-d detached mode (spustí kontejner na pozadí a zobrazí id kontejneru)
--rm automaticky odstraní kontejner pokud existuje
--name přiřadí kontejneru jméno

psql -h localhost -p 2345 -U postgres -d postgres

Připojí se k PostgreSQL databázi.

Spojení výsledků více SQL dotazů do jednoho sloupce

SQL umožňuje spojit výsledky více SQL dotazů do jediného sloupce. Nejdříve si vytvoříme tabulky a naplníme je daty.

drop table if exists t_tree;
create table t_tree (id bigint, text text);
insert into t_tree (id, text) values
(1, 'javor'),
(2, 'dub'),
(3, 'borovice');

drop table if exists t_car;
create table t_car (id bigint, text text);
insert into t_car (id, text) values
(1, 'škoda'),
(2, 'nisan'),
(3, 'ford');

drop table if exists t_sport;
create table t_sport (id bigint, text text);
insert into t_sport (id, text) values
(1, 'hokej'),
(2, 'judo'),
(3, 'biatlon');

Spojení výsledků ze tří tabulek:

Číst dálSpojení výsledků více SQL dotazů do jednoho sloupce

Instalace pgAdmin4 na Linux Mint

PgAdmin 4 je populární Open Source administrativní nástroj pro PostgreSQL. Jeho instalace na Linux Mint, má ale určitá úskalí. Pokud používáte Linux Ubuntu, stačí vám apt install. Pro Linux Mint, musíte nejdříve přidat klíč pro balíčky postgresu

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

a do Software Sources přidejte záznam,

Číst dálInstalace pgAdmin4 na Linux Mint

Řazení dat v PostgreSQL podle části řetězce

V tomto příspěvku ukáži, jak získat unikátní emaily ze sloupce databáze PostgreSQL seřazené podle domény. K tomu použijeme funkci substring(co, odkud, kam).

select email from  table_name order by substring(email, position('@' in email), length(email));

Uvedený select zobrazí záznamy ze sloupce email tabulky table_name a seřadí je dle domény (podle části řetězce, který vrátí substring). Pokud chceme unikátní, select změníme následovně:

select email from (select distinct email from table_name) as sub order by substring(email, position('@' in email), length(email));