Посібник з основних команд PostgreSQL та відмінностей від MariaDB (MySQL)

Як досвідчений користувач MariaDB, що переходить на PostgreSQL, важливо зрозуміти ключові відмінності в командах, утилітах та функціях між двома системами. Хоча обидві є потужними системами керування реляційними базами даних (СКРБД) і мають схожий синтаксис SQL, PostgreSQL вводить унікальні концепції та команди. Цей посібник зосереджений на цих відмінностях, щоб допомогти вам швидко розібратися.

1. Інструменти командного рядка

Доступ до бази даних

MariaDB/MySQL:

mysql -u username -p

PostgreSQL:

psql -U username -d database_name

Поширені мета-команди `psql`

В інтерактивному терміналі PostgreSQL (psql) мета-команди починаються зі зворотного слешу (\):

\l — Показати список усіх баз даних.
\c database_name — Підключитися до бази даних.
\dt — Показати список таблиць у поточній схемі.
\d table_name — Описати структуру таблиці.
\du — Показати список усіх ролей (користувачів).
\q — Вийти з psql.

2. Керування користувачами та ролями

Створення користувачів/ролей

MariaDB/MySQL:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

PostgreSQL:

CREATE ROLE username WITH LOGIN PASSWORD 'password';

Примітка: У PostgreSQL "роль" може діяти як користувач і як група. Додавання WITH LOGIN дозволяє ролі входити в систему.

Надання привілеїв

MariaDB/MySQL:

GRANT ALL PRIVILEGES ON database.* TO 'username'@'host';

PostgreSQL:

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

3. Керування базами даних

Створення та видалення баз даних

Створення бази даних:
MariaDB/MySQL:
```
CREATE DATABASE database_name;
```

PostgreSQL:

CREATE DATABASE database_name OWNER username;

Видалення бази даних:
Однаково в обох:
```
DROP DATABASE database_name;
```

Список баз даних

MariaDB/MySQL:

SHOW DATABASES;

PostgreSQL:
За допомогою мета-команди psql:
```
\l
```
Або SQL-запиту:
```
SELECT datname FROM pg_database;
```

4. Відмінності типів даних

Автоінкрементні стовпці

MariaDB/MySQL:

id INT AUTO_INCREMENT PRIMARY KEY

PostgreSQL:

id SERIAL PRIMARY KEY

SERIAL — це псевдотип, який створює цілочисельний стовпець з пов'язаною послідовністю.

Булевий тип

MariaDB/MySQL:
Немає нативного BOOLEAN; використовує TINYINT(1).
PostgreSQL:
Нативний тип BOOLEAN зі значеннями TRUE або FALSE.

Строкові типи даних

MariaDB/MySQL:
VARCHAR, TEXT, BLOB тощо.
PostgreSQL:
Схожі типи, але TEXT часто використовується замість великого VARCHAR.

5. Конкатенація рядків

MariaDB/MySQL:

SELECT CONCAT(first_name, ' ', last_name) FROM users;

PostgreSQL:

SELECT first_name || ' ' || last_name FROM users;

6. Умовні вирази

IF проти CASE

MariaDB/MySQL:

SELECT IF(condition, true_result, false_result);

PostgreSQL:

SELECT CASE WHEN condition THEN true_result ELSE false_result END;

7. Операції Upsert

Вставка або оновлення

MariaDB/MySQL:

INSERT INTO table_name (...) VALUES (...) ON DUPLICATE KEY UPDATE column=VALUE;

PostgreSQL:

INSERT INTO table_name (...) VALUES (...) ON CONFLICT (unique_column) DO UPDATE SET column=EXCLUDED.column;

EXCLUDED посилається на значення, запропоновані для вставки.

8. Limit та Offset

MariaDB/MySQL:

SELECT * FROM table_name LIMIT 10 OFFSET 20;

PostgreSQL:

Той самий синтаксис, але також підтримує FETCH:

SELECT * FROM table_name LIMIT 10 OFFSET 20;
SELECT * FROM table_name OFFSET 20 FETCH NEXT 10 ROWS ONLY;

9. Функції дати та часу

Поточна дата та час

MariaDB/MySQL:

SELECT NOW();

PostgreSQL:

SELECT CURRENT_TIMESTAMP;

Арифметика дат

MariaDB/MySQL:

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);

PostgreSQL:

SELECT CURRENT_TIMESTAMP + INTERVAL '7 days';

10. Регулярні вирази

MariaDB/MySQL:

SELECT * FROM table_name WHERE column REGEXP 'pattern';

PostgreSQL:

З урахуванням регістру:

SELECT * FROM table_name WHERE column ~ 'pattern';

Без урахування регістру:

SELECT * FROM table_name WHERE column ~* 'pattern';

11. Конфігураційні файли

Розташування конфігурації

MariaDB/MySQL:
Основна конфігурація в my.cnf або my.ini.
PostgreSQL:
Основна конфігурація в postgresql.conf.
Автентифікація клієнтів у pg_hba.conf.

Примітка: pg_hba.conf контролює автентифікацію на основі хоста і є критичним для налаштування безпеки.

12. Імпорт/Експорт даних

Імпорт даних

MariaDB/MySQL:

LOAD DATA INFILE 'file_path' INTO TABLE table_name;

PostgreSQL:

COPY table_name FROM 'file_path' DELIMITER ',' CSV HEADER;

Експорт даних

MariaDB/MySQL:

SELECT * FROM table_name INTO OUTFILE 'file_path';

PostgreSQL:

COPY table_name TO 'file_path' DELIMITER ',' CSV HEADER;

13. Послідовності

MariaDB/MySQL:
Використовує AUTO_INCREMENT для автоінкрементних стовпців.
PostgreSQL:
Використовує послідовності, які можна створювати незалежно.
```
CREATE SEQUENCE sequence_name;
```
Може бути пов'язана зі стовпцем таблиці.

14. Чутливість до регістру в ідентифікаторах

MariaDB/MySQL:
Загалом нечутливий до регістру для ідентифікаторів.
PostgreSQL:
Перетворює ідентифікатори без лапок у нижній регістр.

Ідентифікатори в лапках чутливі до регістру.

SELECT * FROM "TableName";  -- Посилається саме на "TableName"

15. Збережені процедури та функції

Визначення функцій

MariaDB/MySQL:

DELIMITER $$
CREATE FUNCTION function_name(parameters) RETURNS return_type
BEGIN
  -- тіло функції
END$$
DELIMITER ;

PostgreSQL:

CREATE FUNCTION function_name(parameters) RETURNS return_type AS $$
BEGIN
  -- тіло функції
END;
$$ LANGUAGE plpgsql;

Збережені процедури

MariaDB/MySQL:
Підтримує збережені процедури.
PostgreSQL:

Ввів CREATE PROCEDURE у версії 11.

CREATE PROCEDURE procedure_name(parameters)
LANGUAGE plpgsql
AS $$
BEGIN
  -- тіло процедури
END;
$$;

16. Представлення та матеріалізовані представлення

Представлення

Однаково в обох:

CREATE VIEW view_name AS SELECT ...;

Матеріалізовані представлення

MariaDB/MySQL:
Не підтримує матеріалізовані представлення нативно.
PostgreSQL:

CREATE MATERIALIZED VIEW view_name AS SELECT ...;

Оновлення матеріалізованих представлень:

REFRESH MATERIALIZED VIEW view_name;

17. Підтримка JSON

MariaDB/MySQL:
Підтримує тип даних JSON та функції.
PostgreSQL:
Пропонує розширену підтримку JSON з типами даних json та jsonb.

JSON-функції та оператори:

SELECT data->'key' FROM table_name;    -- Повертає JSON-об'єкт
SELECT data->>'key' FROM table_name;   -- Повертає текст

18. Система розширень

MariaDB/MySQL:
Використовує плагіни.
PostgreSQL:
Підтримує розширення для додавання функціональності.
```
CREATE EXTENSION extension_name;
```
Поширені розширення включають hstore, uuid-ossp, pgcrypto та PostGIS.

19. Обгортки зовнішніх даних (FDW)

MariaDB/MySQL:
Обмежені можливості зовнішніх таблиць.
PostgreSQL:
Підтримує FDW для запитів до зовнішніх джерел даних (наприклад, інших баз даних, файлів).

Приклад:

CREATE EXTENSION postgres_fdw;
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'remote_host', dbname 'remote_db');
CREATE USER MAPPING FOR current_user SERVER foreign_server OPTIONS (user 'remote_user', password 'password');
IMPORT FOREIGN SCHEMA public FROM SERVER foreign_server INTO local_schema;

20. Керування транзакціями

Явні транзакції:

MariaDB/MySQL:

START TRANSACTION;
-- SQL-оператори
COMMIT;  -- або ROLLBACK;