PostgreSQL 9.6 Основные команды.
Переходим в консоль пользователя postgres
# su - postgres
-bash-4.2$
Заходим в консоль PostgreSQL
-bash-4.2$ psql
Пароль: <вводим
пароль пользователя postgres>
psql (9.6.2)
Введите "help", чтобы получить
справку.
postgres=#
Подключение к PostgreSQL под именем
пользователя despot5 сразу к базе данных dbdespot5:
# psql -U
despot5 -b dbdespot5 -W
Пароль пользователя despot5: <вводим пароль пользователя despot5>
psql (9.6.2)
Введите "help", чтобы получить
справку.
dbdespot5=>
Выход из консоли Psql
CTRL + D или
\q
Справка по специальным командам:
postgres=# \?
Общие
\copyright условия использования и
распространения PostgreSQL
\errverbose вывести максимально подробное
сообщение о последней ошибке
\g [ФАЙЛ] или ; выполнить запрос
(и направить
результаты в файл или канал |)
\gexec выполнить запрос, а затем
выполнить каждую строку в результате
\gset [ПРЕФИКС] выполнить запрос и сохранить результаты
в переменных
psql
\q выйти из psql
…
Справка по определенной команде SQL
postgres=# \h ALTER INDEX
Команда:
ALTER INDEX
Описание:
изменить определение индекса
Синтаксис:
ALTER
INDEX [ IF EXISTS ] имя RENAME TO
новое_имя
ALTER
INDEX [ IF EXISTS ] имя SET
TABLESPACE табл_пространство
ALTER
INDEX имя DEPENDS
ON EXTENSION имя_расширения
ALTER INDEX [ IF EXISTS ] имя SET (
параметр_хранения = значение [, ... ] )
ALTER
INDEX [ IF EXISTS ] имя RESET ( параметр_хранения [, ... ] )
ALTER
INDEX ALL IN TABLESPACE имя [ OWNED
BY имя_роли [, ... ] ]
SET
TABLESPACE новое_табл_пространство [ NOWAIT ]
Текущие пользователи просматриваются
так:
postgres=#
\du
Список ролей
Имя роли | Атрибуты | Член ролей
----------+-------------------------------------------------------------------------+------------
postgres | Суперпользователь, Создаёт роли,
Создаёт БД, Репликация, Пропускать RLS | {}
Создание нового пользователя:
postgres=# CREATE USER despot5;
Присвоение пароля пользователю:
postgres=#
alter user despot5 with encrypted password 'passwddespot5';
Создание базы данных
postgres=#
CREATE DATABASE dbdespot5;
Выдача полных прав пользователю despot5 на базу dbdespot5
postgres=#
GRANT ALL privileges ON DATABASE dbdespot5 TO despot5;
Отзыв привелегий:
postgres=#
REVOKE ALL privileges ON DATABASE dbdespot5 FROM despot5;
Просмотр имеющихся баз данных:
postgres=# \l
Список баз
данных
Имя
| Владелец | Кодировка | LC_COLLATE | LC_CTYPE | Права доступа
-----------+----------+-----------+-------------+-------------+-----------------------
dbdespot5 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | despot5=CTc/postgres
postgres | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 |
template0 |
postgres | UTF8 | ru_RU.UTF-8 |
ru_RU.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 |
postgres | UTF8 | ru_RU.UTF-8 |
ru_RU.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testbook | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 |
(5 строк)
Подключение к имеющейся базе данных:
postgres=# \c dbdespot5
Вы подключены к базе данных "dbdespot5"
как пользователь "postgres".
dbdespot5=#
Установка расширений.
Установка расширений выполняется на
конкретной базе данных после подключения к базе данных.
Проведем установку расширений cube,tablefunc,
dict_xsyn, fuzzystrmatch, pg_trgm к базе данных testbook
testbook=#
create extension cube;
CREATE
EXTENSION
Проверка установки этого расширения:
testbook=# SELECT '1'::cube;
cube
------
(1)
(1 строка)
Продолжаем установку расширений:
testbook=#
create extension tablefunc;
CREATE
EXTENSION
testbook=# create extension dict_xsyn;
CREATE
EXTENSION
testbook=# create extension fuzzystrmatch;
CREATE
EXTENSION
testbook=# create extension pg_trgm;
CREATE
EXTENSION
Создание таблицы countries:
postgres=#
CREATE TABLE countries (
country_code char(2) PRIMARY KEY,
country_name text UNIQUE
);
Создание таблицы с ограничением
внешнего ключа:
CREATE
TABLE cities (
name text NOT NULL,
postal_code varchar(9) CHECK (postal_code
<> ''),
country_code char(2) REFERENCES countries,
PRIMARY KEY (country_code, postal_code)
);
Создание таблицы с ограничением
внешнего ключа по двум полям:
CREATE
TABLE venues (
id SERIAL PRIMARY KEY,
name varchar(255),
street_add text,
type char(7) CHECK (type in
('public','private')) DEFAULT 'public',
postal_code varchar(9),
country_code char(2),
FOREIGN KEY (country_code, postal_code)
REFERENCES cities (country_code, postal_code)
MATCH FULL
);
Изменение таблицы:
book=#
ALTER TABLE venues ADD COLUMN active boolean DEFAULT TRUE;
Здесь создается дополнительный столбец
active булевого
типа со значением по умолчанию TRUE.
Чтобы изменить порядок колонок, необходимо либо пересоздать таблицу, либо
создать новые колонки с последующим переносом данных.
Команды просмотра структуры базы и таблиц:
Показать список всех таблиц.
testbook=# \dt
Список отношений
Схема
| Имя |
Тип | Владелец
--------+-----------+---------+----------
public | countries | таблица | postgres
(1 строка)
Показать список всех таблиц с
описанием
testbook=# \dt+
Список отношений
Схема
| Имя |
Тип | Владелец | Размер
| Описание
--------+-----------+---------+----------+------------+----------
public | countries | таблица | postgres | 8192
bytes |
(1 строка)
Показать структуру таблицы countries
testbook=# \d countries
Таблица "public.countries"
Столбец |
Тип | Модификаторы
--------------+--------------+--------------
country_code | character(2) | NOT NULL
country_name | text |
Индексы:
"countries_pkey" PRIMARY KEY,
btree (country_code)
"countries_country_name_key"
UNIQUE CONSTRAINT, btree (country_name)
Режим вывода информации в консоль.
Сохранять результаты запросов в файл 12345.
testbook=# \o 12345
Восстановить режим вывода результатов
запросов в консоль
testbook=# \o
Удаление базы данных:
postgres=#
DROP DATABASE testbook;
DROP
DATABASE
Удаление пользователя.
Если просто так удалять пользователя,
обладающего некоторыми правами, то будем получать ошибку
postgres=# DROP ROLE test123;
ОШИБКА:
роль "test123" нельзя удалить, так как есть зависящие от неё
объекты
ПОДРОБНОСТИ:
права доступа к объекту база данных dbdespot5
То есть сначала нужно отобрать все
привилегии у пользователя test123,
а затем удалять его.
postgres=#
REVOKE ALL privileges ON DATABASE dbdespot5 FROM test123;
REVOKE
postgres=#
DROP ROLE test123;
DROP ROLE
Вставка данных в таблицу:
book=#
INSERT INTO countries (country_code, country_name)
VALUES
('us', 'США'), ('mx', 'Мексика'),
('au', 'Австралия'), ('de', 'Германия'),
('gb', 'United Kingdom'), ('lu',
'Лубундия');
Или
INSERT
INTO cities (name, postal_code,
country_code) VALUES ('Toronto', '159875', 'ca');
Просмотр содержимого таблицы:
book=#
SELECT * FROM countries;
country_code |
country_name
--------------+----------------
us
| США
mx | Мексика
au
| Австралия
de
| Германия
gb | United Kingdom
lu
| Лубундия
(6 строк)
Удаление строки:
book=#
DELETE FROM countries WHERE country_code='lu';
DELETE 1
Обновление строки
UPDATE
cities SET name = 'Портлэнд' WHERE
postal_code = 'MX345as99';
UPDATE 1
Чтение данных таблицы внутренним и
внешним объединением
book-#
SELECT cities.*, country_name
FROM
cities INNER JOIN countries
ON
cities.country_code = countries.country_code;
Или с применением псевдонимов и при
использовании составного ключа
SELECT
v.id, v.name, c.name
FROM
venues AS v INNER JOIN cities AS c
ON
v.country_code = c.country_code
AND
v.postal_code = c.postal_code;
Иллюстрация внутреннего соединения:
Внешнее соединение - LEFT
SELECT
e.starts, e.ends, e.title, v.name
FROM
events AS e LEFT JOIN venues AS v
ON v.id =
e.venue_id;
Внешнее соединение - RIGHT
SELECT
e.starts, e.ends, e.title, v.name
FROM
events AS e RIGHT JOIN venues AS v
ON v.id =
e.venue_id;
Внешнее соединение - FULL
SELECT
e.starts, e.ends, e.title, v.name
FROM
events AS e FULL JOIN venues AS v
ON v.id =
e.venue_id;
Соединение трех таблиц. Пример:
SELECT
e.title, v.name, v.country_code, countries.country_name
FROM
events e, venues v, countries
WHERE
e.venue_id = v.id
AND
v.country_code = countries.country_code
AND e.title = 'Выступление Казимира';
Создание индекса:
book=# CREATE INDEX events_title ON events USING
btree (title);
Просмотр индекса:
book=# \di+
Просмотр всех таблиц с имеющимся
первичным ключом по каталогу pg_class:
book=#
SELECT relname, relowner, relacl, relpersistence, relhaspkey FROM pg_class
WHERE relkind = 'r' AND relhaspkey = 'true' ;
relname
| relowner | relacl | relpersistence | relhaspkey
-----------+----------+--------+----------------+------------
countries | 10 | | p | t
cities
| 10 | | p | t
venues
| 10 | | p | t
events
| 10 | | p | t
(4 строки)
