Chmelej's Blog

Aktualně používáme postgres 11 a 13

Instalace a nastavení

• instalaci mam zvěčněnou v ansible playbook.
• pro praci v konzoli je dobré se přepnout na uživatele posgres sudo su - postgres
• pak spustit psql
• napoveda ? a pak spoustu operaci lze delat pres psql!

Seznam databazi:

psql=# SELECT dataname FROM pg_database;
psql=# CREATE DATABASE xxx;

Seznam tabulek

devel=# \dp
devel=# SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; 

Nastav default schema

SET client_encoding TO UTF8;
SET search_path = cda, pg_catalog; 

Uzivatele a prava

create user george with password 'password';
grant all privileges on database money to cashier;

Anonymni funkce

do $$
    declare v_ad_id bigint;
begin 
    call ips_v8_0.ad_insert ( now()::TIMESTAMP, 999, 'NEW', v_ad_id); 
    RAISE NOTICE 'procedure returns %', v_ad_id; 
end; 
$$

UPSERT

INSERT INTO uni_listing_substitute (master_uni_id, slave_uni_id, meta_modified_when, meta_modified_by)
    VALUES (p_master, p_slave, p_modified_when, p_modified_by)
    ON CONFLICT (slave_uni_id)
    DO UPDATE 
    SET master_uni_id = p_master, meta_modified_when = p_modified_when, meta_modified_by = p_modified_by;
-- pri upsert se muzu odkazovat na hodnoty pouzite v insert pomoci alias EXCLUDED!

cols2rows

SELECT unnest('{sl_no, username, designation, salary}'::text[]) AS col
     , unnest('{10,Joe,Music,1234}'::text[]) AS row1
     , unnest('{11,Bob,Movie,2345}'::text[]) AS row2
     , unnest('{12,Dave,Theatre,2356}'::text[]) AS row3
     , unnest('{4,D,-,HGD}'::text[]) AS row4

Operator IS NOT DISTINCT

porovnani na shodu: A is not distinct from B je to delsi nez A = B ale funguje to i s NULL

Datový typ ltree a gist index

Ještě jsem nenašel správnou konstalaci pro vyzkoušení

Kopirovani dat mezi servery

pomoci pg_dump a ncat

server1 $ ncat -l <port> -4 --recv-only | gzip -dc | psql <dbname>
server2 $ pg_dump --if-exists --no-owner --disable-triggers -c -x <dbname> | gzip -c | ncat <server1> <port> -4 --send-only

Nastaveni prav pro cteni na vsech tabulkach a view

SELECT 'grant select on '||table_name||' to ips_reader;' cmd FROM information_schema.tables where table_schema = 'public'
union all
SELECT 'grant select on '||table_name||' to ips_reader;' cmd FROM information_schema.views where table_schema = 'public'
union all
SELECT 'grant select on '||matviewname||' to ips_reader;' cmd FROM pg_matviews where schemaname = 'public'

Migrace Mysql -> Postgresql

šlo by k tomu použít

mysqldump --compatible=postgresql

Divnosti

(zejmena po nekolika letech prace s Oracle)

• Funkce lower() a upper() ignorují písmenka s nabodeníčky (ěščřžýáíé). Napsal jsem si vlastní lower2(), ale nadefinoval jsem tam pouze česká písmena. Kupodivu oracle tohle dělá líp, i když asi taky neumí kompletně všechna písmena. Nakonec byl problem ze databaze byla nastavena jako ASCII, takze kdyz jsem vytvoril UTF8 tak lower()/upper() fungoval podle představ.

• Další moje funkce bude convert_ascii() coby nahrada za oracle funkci convert(, ‘US7ASCII’) implementovano pomoci funkce translate()

• v pgsql když je tabulka zamčená, tak zní nelze ani číst a to jsem snad ani nezapisoval! v oracle se to nedělo pokud jsem nezapisoval do zamknuté tabulky.

• pgsql řadí jinak než ORACLE a opět záleží na locales.

• V Oracle jsem mel v clientu (squirrel-sql) nastaveny manualni commit a fungovalo to pekne, bohuzel v Postgresql jsem musel prepnout na auto commit protoze i pripadny select v clientu zamknul tabulku ostatnim procesum a pomohl az commit/rollback coz je dost casto pruser.

Postgres vs Oracle syntaxe

• nvl(A,B) -> COALESCE(A,B)
• regexp_like(col, regexp) -> col ~ regexp
• AND rownum <= x -> LIMIT x
• rekurzivni dotaz

SELECT def_id FROM ad_definition 
    START WITH def_id = ? CONNECT BY PRIOR par_def_id = def_id;

-> 
WITH RECURSIVE cte AS ( 
    SELECT df.def_id FROM ad_definition df WHERE df.def_id = ? 
    UNION ALL 
    SELECT df.par_def_id FROM ad_definition df, cte WHERE cte.def_id = df.def_id AND df.par_def_id > 0
) SELECT * FROM cte;

Tags: PostgreSQL