PostgreSQL

Создание/удаление пользователя

Создание пользователя

CREATE ROLE myuser;

Добавление привилегии на вход

CREATE ROLE myuser LOGIN;

Добавление пароля

ALTER ROLE myuser WITH PASSWORD;

Создание роли с паролем, действующим до конца 2018 года

CREATE ROLE myuser WITH LOGIN PASSWORD '123456' VALID UNTIL '2018-01-01';

Добавление привилегии на создание баз

ALTER ROLE myuser CREATEDB;

Удаление пароля

ALTER ROLE myuser WITH PASSWORD NULL;

Удаление пользователя

DROP ROLE IF EXISTS myuser;

psql

Внутренние команды

\c[onnect] [dbname|-[user]] - установить новое соединение.

\copy - выполнить SQL-команду COPY на клиентской машине.

\d - список всех отношений(таблиц, последовательностей и представлений)

\d <table> - вывести описание таблицы (индекса, пресдтавления, последовательности)

\d{t|i|s|v} - список таблиц/индексов/последовательностей/представлений.

\d{p|S|l} - список прав доступа/системных таблиц/больших объектов.

\da - список агрегированных функций.

\dd [object] - спсиок комментариев к таблице, типу, функции, оператору.

\df - список функций.

\do - список операторов.

\e [file] - редактировать текущий буфер запроса или файл во внешнем редакторе.

\g [file] - Послать запрос на сервер

\i - чтение команд из файла

\o - вывод запроса в файл

\s - посмотреть историю запросов

\p - посмотреть буфер запросов

\r - очистить буфер

Параметры

-f - выполнение команд из файла

-o - вывод запроса в файл

Выполение команды в базе

psql -U postgres -d dbname -c "SELECT * FROM pg_stat_activity;"

Вывод результата запроса в файл

psql -d dbname -H -c "SELECT * FROM test" -o filename

Команды SQL LIKE

Знак % заменяет любую подстроку.

Знак _ заменяет один произвольный символ. DATESTYLE

По умолчанию PosgreSQL использует формат представления вермени и даты по стандарту ISO-8601: YYYY-MM-DD hh:mm:ss.sTZD.

Однако при попытке работы с датой в формате 'DD.MM.YYYY' возникнут ошибки, т.к. PostgreSQL интепретирует это как 'MDY'. Для отображения даты в формате 'DMY' использутся команда

SET DATESTYLE TO 'ISO, DMY';

Для установки стиля по умолчанию для всей базы данных нужно определить переменную окружения PGDATESTYLE до старта процесса postmaster

PGDATESTYLE=“European,SQL” export PGDATESTYLE

SELECT date_part('day','2017-06-10')

backup и restore

Сохранение только схемы

pg_dump -h [host] -p [port] -U [username] -W -d [database] --schema-only > filename

Восстановление

psql -h [host] -p [port] -U [username] -W -d [database] < filename

Полезные команды

Очищение таблицы tablename и обнуление счетчика с ID.

TRUNCATE TABLE tablename RESTART IDENTITY CASCADE;

Удаление NULL у поля

ALTER TABLE movies ALTER COLUMN year DROP NOT NULL;

Просмотр списка и путей к конфигурационным файлам SELECT name, setting FROM pg_settings WHERE category = 'File Locations';

SHOW ALL;

Просмотр всех активных подключений SELECT * FROM pg_stat_activity;

SHOW data_directory;