PostgreSQL для Oracle разработчика. Краткий FAQ.

Если вы работали с ораклом и вам посчастливилось(а может и нет) встретится с PostgreSQL, то знайте..

Пожалуй это самая приятная для ораклиста из "чужих" СУБД.

//страница пополняется..

---------------------------
1. Пользователи
---------------------------

Аналог ораклового sys as sysdba - пользователь postgres. это рут без ограничений прав. Создается при установке постгри.

dbcreate - утилита создания БД

dbdrop - утилита удаления БД

! В том случае, когда коннект к постгресу идет без явного указания имени пользователя через -U , то берется текущее имя пользователя из ОС.

Создание:

Также как в оракле - CREATE USER, но в постгресс это алиас для CREATE ROLE. Роль=Юзер. Вот такая херь. Пользователь создается не для базы, а для всего кластера. Преимущество команды create user в том, что она сразу выдает право на login, а также более чисто выглядит с точки зрения синтаксиса.

 CREATE USER name [ [ WITH ] option [ ... ] ]  

опции здесь это: пароль, максимальное кол.коннектов, срок действия, признак суперюзера, права админа и пр.

 Пример: 

 CREATE USER davide WITH PASSWORD 'jw8s0F4';
 CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';

либо

 CREATE USER miriam WITH PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';

Удаление:

DROP USER cashier; -- при этом CASCADE как в оракле нету. Ибо Юзер=Роль<>Схема.

DROP SCHEMA test CASCADE; -- а вот у схемы cascade есть

Гранты и Ревоки:

Могут выдаваться пользователю или роли.(что одно и тоже). Примеры:

-- даем все возможные привилегии.Такое может дать только пользователь postgres

GRANT ALL PRIVILEGES ON kinds TO manuel;

-- дает право with admin options. Т.е. может раздавать гранты с админскими опциями, как в оракле.

GRANT admins TO joe;

Еще пример:

GRANT ALL PRIVILEGES ON DATABASE money TO cashier;
REVOKE ALL PRIVILEGES ON money FROM cashier;

Итого простейший скрипт создания удаления:

 CREATE USER test WITH PASSWORD 'test';
 GRANT ALL PRIVILEGES ON DATABASE DEMO TO test;
 DROP USER test;

В целом похоже на оракл.

Таблицы группируются в базы данных, а коллекция баз данных, управляемая одной копией сервера PostgreSQL называется кластером баз данных.

Внутри базы данных может быть одна или несколько схем.

Пользователь != Схема. При этом пользователь создается сразу для всего кластера, а не дял БД. Права пользователя настраиваются уже более детально.

Есть схема public. Также как в оракле ее данные доступны всем. Если создавать таблицу без указания схемы, то она создастся в public.

---------------------------------
2. Консоль, IDE и пр
---------------------------------

psql - консольная утилита, аналог sqlplus

- \h - help

- \q - exit

- \i basics.sql - Для накатки скриптов. Аналог ораклового sqlplus> @basics.sql

Там есть собственный pgAdmin. В принципе подходит.

А так можно использовать AquaStudio - неплохая альтернатива как для ER так и для разработки.

DBEaver тоже хорошо умеет работать с постгрёй.

------------------------------------------------
3. Работа с датами и строками
------------------------------------------------

----- Formatting

to_char(current_timestamp, 'DD.MM.YYYY HH24:MI:SS')	-- из даты в строку.  аналог ораклового
to_timestamp('05 Dec 2000', 'DD Mon YYYY')	-- из строки в дату.  аналог ораклового
to_number('12,454.8-', '99G999D9S')	-- из строки в число. аналог ораклового

! fm модификаторы также есть.

----- Даты

Текуща дата:

SELECT current_timestamp;	-- аналог SELECT sysdate from dual, но с таймзон
SELECT current_date;	-- аналог SELECT sysdate from dual, но без времени
SELECT LOCALTIMESTAMP;	-- аналог SELECT sysdate from dual

Функции дат:

date_trunc('hour', timestamp '2001-02-16 20:38:40') = 2001-02-16 20:00:00 -- аналог ораклового trunc(date,precision)

Сложения дат:

date '2001-09-28' + integer '7' = date '2001-10-05'	-- как и в оракле 1 = 1 дню.
date '2001-09-28' + interval '1 hour' = timestamp '2001-09-28 01:00:00'	-- как и в оракле интервал позволяет прибавлять часы/минуты и т.д.

----- Строки

||	-- конкатенация двух строк. Причем также как и в оаркле действует автоматическое приведение типов.
length	-- аналог ораклового length
substr	-- аналог ораклового substr
upper/lower/initcap/	-- тоже аналоги оракловых
trim/rtrim/ltrim/	-- тоже аналоги оракловых
translate/replace/regexp_replace/еtс..	-- тоже аналоги оракловых. Регулярки такие же как в оракле и даже лучше. Есть больше функций.
ascii	-- аналог ораклового. также возвращает код символа.

----------------------------------------------------------------------------------------------------
4. IsNull, Boolean operators, math functions, limit, sql and dual
----------------------------------------------------------------------------------------------------

"выражение" IS NULL / IS NOT NULL -- все также как в оракле. Смысл тот же и работает также, но есть особенности в виде настроек которые могут неработющие "выражение"=null преобразовывать в is null на лету.

Все сравнения также идут как в оракле, включая !=, <>, Between, Not Between и т.д.

Есть еще дополнительный сахарок в виде IS TRUE и другой хуеты.

Математические ф-ции:

ceil/mod/pi/sqrt/abs/power/sin/cos/atan/ еtс.. -- вся математика как в оракле!

!Есть еще функция random - аналог dbms_random

!также есть груповые функции и пр.

limit - аналог mysql. либо rownum, но без гемороя оракловского.
with - аналог ораклового.
SELECT 1; -- аналог SELECT 1 from dual
coalesce -- аналог ораклового decode
nullif -- аналог ораклового nvl

----------------------------------------------
5. Кодировка и типы данных
----------------------------------------------

Основные типы, определяются SQL: 

--- Numbers ---

bigint	-- 8 байт целое. самое большое число тут. Знаковое.
bigserial	-- 8 байт целое с автоинкрементом. Тут диапазон от 1 до max. Аналог mysql-го автоинкремента(по ум. генерится дебильный сиквенц с единичным кешем. В принципе его потом можно заальтерить).
integer	-- 4 байт целое.
smallint	-- 2 байта знаковое целое.
double precision	-- двойная точность с плавающей точкой. 8 байт. можно называть float8/double
decimal / numeric	-- число с заданной точностью. Аналого ораклового number, но медленнее. Для ключей его использовать не надо!
real	-- 4 байта вещественное

--- Dates ---

date	-- только дата!
time (с или без часового пояса)	-- только время!
timestamp (с или без часового пояса)	-- дата + время. Аналог ораклового date если без часового пояса.
interval	-- аналог ораклового interval. Для сложения и вычитая с датами.

!PostgreSQL использует для всех вычислений даты/времени Юлианский календарь. Отсчет от 4713 до н.э.

--- Strings ---

char	-- аналого ораклового char
varchar	-- переменная длина. Аналог ораклового varchar2. Без указаняи точности - становится как аналог text.
text	-- переменная длина. Родной тип для постгрес. Без ограничения длины. Максимум для строки - 1Гб.

!! Длинные строки лежат отдельно от исходной таблицы. Вернее так - до определенной длины(внутрен.реализация постгрес) лежат в табличке, а после превышения для них создается отдельная таблица. А в старой табличке только ссылка(как оракловый CLOB). Это делается чтобы не фрагментировать поля в исходной таблице.

!! Важно - разницы в скорости работы между этими 3-мя текстовыми типами нет! Фактически Постгрес рекомендует использовать varchar/text вместо char.

Ибо char - тупо дороже в обслуживании в базе. Это отличие от остальных СУБД, где как правило чар быстрее пашет.

--- Other ---

boolean	-- логическое
bytea	-- двоичные типы данных. Аналог Blob Oracle. Состоит из ссылки размером до 4 байт и самих данных.

Есть еще куча др. Реально много. Есть и полезные типа Point - помогают хранить широту/долготу.

--------------------------------------------
6. Автоинкремент, PK, FK
--------------------------------------------

Автоинкремент делается либо через сиквенц, либо посредством типов serial / bigserial, что эквивалентно сиквенцу с таким использованием:

CREATE SEQUENCE имя_таблицы_имя_колонки_seq;  -- создается сиквенц аналогично оракловому. 1:1 - Там даже кэш есть))
CREATE TABLE имя_таблицы (
  colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')  -- автоинкремент делается также как в Оракл 12c. 
  -- Вернее в оракл как в постгрес))
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;  -- привязываем сиквенц к колонке. 
-- если грохнули колонку, то грохнули сиквенц.

! PK нужно всеравно указывать явно! Тип serial сам ПК не создает.

Функции сиквенца аналогичны оракловым, но вызываются по другому: nextval(name_sequence), currval(name_sequence) и т.д.

Cоздание PK/FK  - аналогично ораклу.

!Все констрейнты также как и в оракле делаются. Причем также можно задавать явное имя иначе постгрес будет сам именовать объекты.

-----------------------------------------------------
7. Файлы данных и кеширование
-----------------------------------------------------

Файлы данных это физический уровень.

создаются во время создания таблеспейса. Для него указываем какой файл данных он использует. Куда файлик положим там и будет лежать. Главное, чтобы права у постгреса были на его чтение и запись.

Таблеспейс - логический уровень.

Таблица/индекс и пр - тоже логический.

----------------------------------------------------------------------------------------
8. Мониторинг сессий, статистика и производительность
----------------------------------------------------------------------------------------

- TOP SQL

- Список сессий

SELECT * FROM pg_stat_activity;

- Как убить сессию

kill -9 pid -- через консоль linux

select pg_cancel_backend(pid) -- через запрос в постгрес

- trace SQL

EXPLAIN SELECT * FROM table where ....;

Для построения плана запросов постгрес использует статистику а'ля Оракл.

Собрать статистку можно планировщиком - это настройка default_statistics_target.

Либо по конкретной таблице с помощью:

ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]

Пример:

analyze table table_name;

VACUUM - мусорщик. Фишка в том что в постгресе данные не удаляются из БД сразу после коммита. Они просто помечаются как удаленные. Для окончательного удаления, а заодно и дефрагментации со сбором статистики запускают команду vacuum analyze. Лучше делать это ночью и инкрементально. 

vacuum analyze full - уберет мусор отовсюду и зверски тормозит базу.

--------------------------------------------------
9. Метаданные о объектах и БД
--------------------------------------------------

SELECT version(); -- версия СУБД

Каждая база данных содержит схему pg_catalog, которая содержит системные таблицы и все встроенные типы данных, функции и операторы.

---------------------------------------------
10. Экспорт/Импорт/BackUp
---------------------------------------------

Экспорт. 3 варианта:

1) pg_dump имя_БД > файл_дампа

утилита pg_dump создаст дамп целостным. Т.е. будет снимок на момент начала работы БД. И при том сделает это почти без блокировок.

Дамп не зависит от 32/64 бит ОС. Внутри дампа обычный SQL.

2) Если постгрес был откомпилен с опцией zlib, то он умеет сам сжимать файл во время экспорта не хуже gzip, да к тому же при этом из такого файлика можно будет вытаскивать потом таблички по одной, как в оракловом дампе. Такой дамап будет не совсем SQL.

pg_dump -Fc имя_БД > имя_файла

3) pg_dumpall > файл_дампа -- Экспорт всего кластера с табличными пространствами ролями и пр. Аналог full=y

Импорт. Тоже 3 варианта:

1) psql имя_БД < файл_дампа  -- если дамп делался с помощью pg_dump

2) pg_restore -d имя_БД имя_файла -- для дампа с собственным форматом хранения.

3) psql -f файл_дампа postgres -- если дамп делался с помощью pg_dumpall

На Linux можно применять обычный экспорт/импорт, архивируя/разархивируя его на лету с помощью gzip

pg_dump имя_БД | gzip > имя_файла.gz -- для экспорта

gunzip -c имя_файла.gz | psql имя_БД -- для импорта