Datový typ enum v PostgreSQL vytvoříte příkazem CREATE TYPE ... AS ENUM ...
drop type if exists client;
create type client as enum ('UNKNOWN', 'PERSON', 'COMPANY');
Pro testování si vytvoříme tabulku, kde jeden ze sloupců bude nově vytvořeného typu a vložíme si do tabulky nějaká data.
PostgreSQL standardně poslouchá na portu 5432. Nastavení čísla portu je v souboru postgresql.conf, který v debian distribuci linuxu najdete v adresáři /etc/postgresql/XX/main (XX označuje verzi postgresu).
port = 5432
Stačí číslo změnit a restartovat postgres
sudo service postgresql restart
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
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 | +---------------------------------------------------------------------------------------------------+-------+------------------+
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
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
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ů)
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.
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:
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,
