Pgadmin 3 инструкция на русском

Установка

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

Первый шаг – установить SQL

Мы будем использовать PostgreSQL (Postgres) – достаточно распространенный SQL диалект. Для этого откроем страницу загрузки, выберем операционную систему (в моем случае Windows), и запустим установку. Если вы установите пароль для вашей базы данных, постарайтесь сразу не забыть его, он нам дальше понадобится. Поскольку наша база будет локальной, можете использовать простой пароль, например: admin.

Следующий шаг – установка pgAdmin

pgAdmin – это графический интерфейс пользователя (GUI – graphical user interface), который упрощает взаимодействие с базой данных PostgreSQL. Перейдите на страницу загрузки, выберите вашу операционную систему и следуйте указаниям (в статье используется Postgres 14 и pgAdmin 4 v6.3.).

После установки обоих компонентов открываем pgAdmin и нажимаем Add new server. На этом шаге установится соединение с существующим сервером, именно поэтому необходимо сначала установить Postgres. Я назвал свой сервер home и использовал пароль, указанный при установке.

Теперь всё готово к созданию таблиц. Давайте создадим набор таблиц, которые моделируют школу. Нам необходимы таблицы: ученики, классы, оценки. При создании модели данных необходимо учитывать, что в одном классе может быть много учеников, а у ученика может быть много оценок (такое отношение называется «один ко многим»).

Мы можем создать таблицы напрямую в pgAdmin, но вместо этого мы напишем код, который можно будет использовать в дальнейшем, например, для пересоздания таблиц. Для создания запроса, который создаст наши таблицы, нажимаем правой кнопкой мыши на postgres (пункт расположен в меню слева home → Databases (1) → postgres и далее выбираем Query Tool.

Начнем с создания таблицы классов (classrooms). Таблица будет простой: она будет содержать идентификатор id и имя учителя – teacher. Напишите следующий код в окне запроса (query tool) и запустите (run или F5).

        DROP TABLE IF EXISTS classrooms CASCADE;

CREATE TABLE classrooms (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    teacher VARCHAR(100)
);
    

В первой строке фрагмент DROP TABLE IF EXISTS classrooms удалит таблицу classrooms, если она уже существует. Важно учитывать, что Postgres, не позволит нам удалить таблицу, если она имеет связи с другими таблицами, поэтому, чтобы обойти это ограничение (constraint) в конце строки добавлен оператор CASCADE. CASCADE – автоматически удалит или изменит строки из зависимой таблицы, при внесении изменений в главную. В нашем случае нет ничего страшного в удалении таблицы, поскольку, если мы на это пошли, значит мы будем пересоздавать всё с нуля, и остальные таблицы тоже удалятся.

Добавление DROP TABLE IF EXISTS перед CREATE TABLE позволит нам систематизировать схему нашей базы данных и создать скрипты, которые будут очень удобны, если мы захотим внести изменения – например, добавить таблицу, изменить тип данных поля и т. д. Для этого нам просто нужно будет внести изменения в уже готовый скрипт и перезапустить его.

Ничего нам не мешает добавить наш код в систему контроля версий. Весь код для создания базы данных из этой статьи вы можете посмотреть по ссылке.

Также вы могли обратить внимание на четвертую строчку. Здесь мы определили, что колонка id является первичным ключом (primary key), что означает следующее: в каждой записи в таблице это поле должно быть заполнено и каждое значение должно быть уникальным. Чтобы не пришлось постоянно держать в голове, какое значение id уже было использовано, а какое – нет, мы написали GENERATED ALWAYS AS IDENTITY, этот приём является альтернативой синтаксису последовательности (CREATE SEQUENCE). В результате при добавлении записей в эту таблицу нам нужно будет просто добавить имя учителя.

И в пятой строке мы определили, что поле teacher имеет тип данных VARCHAR (строка) с максимальной длиной 100 символов. Если в будущем нам понадобится добавить в таблицу учителя с более длинным именем, нам придется либо использовать инициалы, либо изменять таблицу (alter table).

Теперь давайте создадим таблицу учеников (students). Новая таблица будет содержать: уникальный идентификатор (id), имя ученика (name), и внешний ключ (foreign key), который будет указывать (references) на таблицу классов.

        DROP TABLE IF EXISTS students CASCADE;

CREATE TABLE students (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100),
    classroom_id INT,
    CONSTRAINT fk_classrooms
        FOREIGN KEY(classroom_id)
        REFERENCES classrooms(id)
);
    

И снова мы перед созданием новой таблицы удаляем старую, если она существует, добавляем поле id, которое автоматически увеличивает своё значение и имя с типом данных VARCHAR (строка) и максимальной длиной 100 символов. Также в эту таблицу мы добавили колонку с идентификатором класса (classroom_id), и с седьмой по девятую строку установили, что ее значение указывает на колонку id в таблице классов (classrooms).

Мы определили, что classroom_id является внешним ключом. Это означает, что мы задали правила, по которым данные будут записываться в таблицу учеников (students). То есть Postgres на данном этапе не позволит нам вставить строку с данными в таблицу учеников (students), в которой указан идентификатор класса (classroom_id), не существующий в таблице classrooms. Например: у нас в таблице классов 10 записей (id с 1 до 10), система не даст нам вставить данные в таблицу учеников, у которых указан идентификатор класса 11 и больше.

        INSERT INTO students
    (name, classroom_id)
VALUES
    ('Matt', 1);

/*
ERROR: insert or update on table "students" violates foreign
    key constraint "fk_classrooms"
DETAIL: Key (classroom_id)=(1) is not present in table
    "classrooms".
SQL state: 23503
*/
    

Теперь давайте добавим немного данных в таблицу классов (classrooms). Так как мы определили, что значение в поле id будет увеличиваться автоматически, нам нужно только добавить имена учителей.

        INSERT INTO classrooms
    (teacher)
VALUES
    ('Mary'),
    ('Jonah');

SELECT * FROM classrooms;

/*
 id | teacher
 -- | -------
  1 | Mary
  2 | Jonah
*/
    

Прекрасно! Теперь у нас есть записи в таблице классов, и мы можем добавить данные в таблицу учеников, а также установить нужные связи (с таблицей классов).

        INSERT INTO students
    (name, classroom_id)
 VALUES
    ('Adam', 1),
    ('Betty', 1),
    ('Caroline', 2);

SELECT * FROM students;

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
  2 | Betty    |            1
  3 | Caroline |            2
*/
    

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

Конечно же, нет. Мы установили внешний ключ, и он будет блокировать запись, поскольку ссылка на несуществующий id класса невозможна, но мы можем в качестве идентификатора класса (classroom_id) передать null. Это можно сделать двумя способами: указанием null при записи значений, либо просто передачей только имени.

        -- явно определим значение NULL
INSERT INTO students
    (name, classroom_id)
VALUES
    ('Dina', NULL);

-- неявно определим значение NULL
INSERT INTO students
    (name)
VALUES
    ('Evan');

SELECT * FROM students;

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
  2 | Betty    |            1
  3 | Caroline |            2
  4 | Dina     |       [null]
  5 | Evan     |       [null]
*/
    

И наконец, давайте заполним таблицу успеваемости. Этот параметр, как правило, формируется из нескольких составляющих – домашние задания, участие в проектах, посещаемость и экзамены. Мы будем использовать две таблицы. Таблица заданий (assignments), как понятно из названия, будет содержать данные о самих заданиях, и таблица оценок (grades), в которой мы будем хранить данные о том, как ученик выполнил эти задания.

        DROP TABLE IF EXISTS assignments CASCADE;
DROP TABLE IF EXISTS grades CASCADE;

CREATE TABLE assignments (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    category VARCHAR(20),
    name VARCHAR(200),
    due_date DATE,
    weight FLOAT
);

CREATE TABLE grades (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    assignment_id INT,
    score INT,
    student_id INT,
    CONSTRAINT fk_assignments
        FOREIGN KEY(assignment_id)
        REFERENCES assignments(id),
    CONSTRAINT fk_students
        FOREIGN KEY(student_id)
        REFERENCES students(id)
);
    

Вместо того чтобы вставлять данные вручную, давайте загрузим их с помощью CSV-файла. Вы можете скачать файл из этого репозитория или создать его самостоятельно. Имейте в виду, чтобы разрешить pgAdmin доступ к данным, вам может понадобиться расширить права доступа к папке (в моем случае – это папка db_data).

        COPY assignments(category, name, due_date, weight)
FROM 'C:/Users/mgsosna/Desktop/db_data/assignments.csv'
DELIMITER ','
CSV HEADER;
/*
COPY 5
Query returned successfully in 118 msec.
*/

COPY grades(assignment_id, score, student_id)
FROM 'C:/Users/mgsosna/Desktop/db_data/grades.csv'
DELIMITER ','
CSV HEADER;
/*
COPY 25
Query returned successfully in 64 msec.
*/
    

Теперь давайте проверим, что мы всё сделали верно. Напишем запрос, который покажет среднюю оценку, по каждому виду заданий с группировкой по учителям.

        SELECT
    c.teacher,
    a.category,
    ROUND(AVG(g.score), 1) AS avg_score
FROM
    students AS s
INNER JOIN classrooms AS c
    ON c.id = s.classroom_id
INNER JOIN grades AS g
    ON s.id = g.student_id
INNER JOIN assignments AS a
    ON a.id = g.assignment_id
GROUP BY
    1,
    2
ORDER BY
    3 DESC;

/*
 teacher | category  | avg_score
 ------- | --------- | ---------
 Jonah   |  project  |     100.0
 Jonah   |  homework |      94.0
 Jonah   |  exam     |      92.5
 Mary    |  homework |      78.3
 Mary    |  exam     |      76.0
 Mary    |  project  |      69.5
*/
    

Отлично! Мы установили, настроили и наполнили базу данных.

***

Итак, в этой статье мы научились:

• создавать базу данных;
• создавать таблицы;
• наполнять таблицы данными;
• устанавливать связи между таблицами;

Теперь у нас всё готово, чтобы пробовать более сложные возможности SQL. Мы начнем с возможностей синтаксиса, которые, вероятно, вам еще не знакомы и которые откроют перед вами новые границы в написании SQL-запросов. Также мы разберем некоторый виды соединений таблиц (JOIN) и способы организации запросов в тех случаях, когда они занимают десятки или даже сотни строк.

В следующей части мы разберем:

• виды фильтраций в запросах;
• запросы с условиями типа if-else;
• новые виды соединений таблиц;
• функции для работы с массивами;

Материалы по теме

• 🐘 8 лучших GUI клиентов PostgreSQL в 2021 году
• 🐍🐬 Python и MySQL: практическое введение
• 🐍🗄️ Управление данными с помощью Python, SQLite и SQLAlchemy

Установка

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

Первый шаг – установить SQL

Мы будем использовать PostgreSQL (Postgres) – достаточно распространенный SQL диалект. Для этого откроем страницу загрузки, выберем операционную систему (в моем случае Windows), и запустим установку. Если вы установите пароль для вашей базы данных, постарайтесь сразу не забыть его, он нам дальше понадобится. Поскольку наша база будет локальной, можете использовать простой пароль, например: admin.

Следующий шаг – установка pgAdmin

pgAdmin – это графический интерфейс пользователя (GUI – graphical user interface), который упрощает взаимодействие с базой данных PostgreSQL. Перейдите на страницу загрузки, выберите вашу операционную систему и следуйте указаниям (в статье используется Postgres 14 и pgAdmin 4 v6.3.).

После установки обоих компонентов открываем pgAdmin и нажимаем Add new server. На этом шаге установится соединение с существующим сервером, именно поэтому необходимо сначала установить Postgres. Я назвал свой сервер home и использовал пароль, указанный при установке.

Теперь всё готово к созданию таблиц. Давайте создадим набор таблиц, которые моделируют школу. Нам необходимы таблицы: ученики, классы, оценки. При создании модели данных необходимо учитывать, что в одном классе может быть много учеников, а у ученика может быть много оценок (такое отношение называется «один ко многим»).

Мы можем создать таблицы напрямую в pgAdmin, но вместо этого мы напишем код, который можно будет использовать в дальнейшем, например, для пересоздания таблиц. Для создания запроса, который создаст наши таблицы, нажимаем правой кнопкой мыши на postgres (пункт расположен в меню слева home → Databases (1) → postgres и далее выбираем Query Tool.

Начнем с создания таблицы классов (classrooms). Таблица будет простой: она будет содержать идентификатор id и имя учителя – teacher. Напишите следующий код в окне запроса (query tool) и запустите (run или F5).

        DROP TABLE IF EXISTS classrooms CASCADE;

CREATE TABLE classrooms (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    teacher VARCHAR(100)
);
    

В первой строке фрагмент DROP TABLE IF EXISTS classrooms удалит таблицу classrooms, если она уже существует. Важно учитывать, что Postgres, не позволит нам удалить таблицу, если она имеет связи с другими таблицами, поэтому, чтобы обойти это ограничение (constraint) в конце строки добавлен оператор CASCADE. CASCADE – автоматически удалит или изменит строки из зависимой таблицы, при внесении изменений в главную. В нашем случае нет ничего страшного в удалении таблицы, поскольку, если мы на это пошли, значит мы будем пересоздавать всё с нуля, и остальные таблицы тоже удалятся.

Добавление DROP TABLE IF EXISTS перед CREATE TABLE позволит нам систематизировать схему нашей базы данных и создать скрипты, которые будут очень удобны, если мы захотим внести изменения – например, добавить таблицу, изменить тип данных поля и т. д. Для этого нам просто нужно будет внести изменения в уже готовый скрипт и перезапустить его.

Ничего нам не мешает добавить наш код в систему контроля версий. Весь код для создания базы данных из этой статьи вы можете посмотреть по ссылке.

Также вы могли обратить внимание на четвертую строчку. Здесь мы определили, что колонка id является первичным ключом (primary key), что означает следующее: в каждой записи в таблице это поле должно быть заполнено и каждое значение должно быть уникальным. Чтобы не пришлось постоянно держать в голове, какое значение id уже было использовано, а какое – нет, мы написали GENERATED ALWAYS AS IDENTITY, этот приём является альтернативой синтаксису последовательности (CREATE SEQUENCE). В результате при добавлении записей в эту таблицу нам нужно будет просто добавить имя учителя.

И в пятой строке мы определили, что поле teacher имеет тип данных VARCHAR (строка) с максимальной длиной 100 символов. Если в будущем нам понадобится добавить в таблицу учителя с более длинным именем, нам придется либо использовать инициалы, либо изменять таблицу (alter table).

Теперь давайте создадим таблицу учеников (students). Новая таблица будет содержать: уникальный идентификатор (id), имя ученика (name), и внешний ключ (foreign key), который будет указывать (references) на таблицу классов.

        DROP TABLE IF EXISTS students CASCADE;

CREATE TABLE students (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100),
    classroom_id INT,
    CONSTRAINT fk_classrooms
        FOREIGN KEY(classroom_id)
        REFERENCES classrooms(id)
);
    

И снова мы перед созданием новой таблицы удаляем старую, если она существует, добавляем поле id, которое автоматически увеличивает своё значение и имя с типом данных VARCHAR (строка) и максимальной длиной 100 символов. Также в эту таблицу мы добавили колонку с идентификатором класса (classroom_id), и с седьмой по девятую строку установили, что ее значение указывает на колонку id в таблице классов (classrooms).

Мы определили, что classroom_id является внешним ключом. Это означает, что мы задали правила, по которым данные будут записываться в таблицу учеников (students). То есть Postgres на данном этапе не позволит нам вставить строку с данными в таблицу учеников (students), в которой указан идентификатор класса (classroom_id), не существующий в таблице classrooms. Например: у нас в таблице классов 10 записей (id с 1 до 10), система не даст нам вставить данные в таблицу учеников, у которых указан идентификатор класса 11 и больше.

        INSERT INTO students
    (name, classroom_id)
VALUES
    ('Matt', 1);

/*
ERROR: insert or update on table "students" violates foreign
    key constraint "fk_classrooms"
DETAIL: Key (classroom_id)=(1) is not present in table
    "classrooms".
SQL state: 23503
*/
    

Теперь давайте добавим немного данных в таблицу классов (classrooms). Так как мы определили, что значение в поле id будет увеличиваться автоматически, нам нужно только добавить имена учителей.

        INSERT INTO classrooms
    (teacher)
VALUES
    ('Mary'),
    ('Jonah');

SELECT * FROM classrooms;

/*
 id | teacher
 -- | -------
  1 | Mary
  2 | Jonah
*/
    

Прекрасно! Теперь у нас есть записи в таблице классов, и мы можем добавить данные в таблицу учеников, а также установить нужные связи (с таблицей классов).

        INSERT INTO students
    (name, classroom_id)
 VALUES
    ('Adam', 1),
    ('Betty', 1),
    ('Caroline', 2);

SELECT * FROM students;

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
  2 | Betty    |            1
  3 | Caroline |            2
*/
    

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

Конечно же, нет. Мы установили внешний ключ, и он будет блокировать запись, поскольку ссылка на несуществующий id класса невозможна, но мы можем в качестве идентификатора класса (classroom_id) передать null. Это можно сделать двумя способами: указанием null при записи значений, либо просто передачей только имени.

        -- явно определим значение NULL
INSERT INTO students
    (name, classroom_id)
VALUES
    ('Dina', NULL);

-- неявно определим значение NULL
INSERT INTO students
    (name)
VALUES
    ('Evan');

SELECT * FROM students;

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
  2 | Betty    |            1
  3 | Caroline |            2
  4 | Dina     |       [null]
  5 | Evan     |       [null]
*/
    

И наконец, давайте заполним таблицу успеваемости. Этот параметр, как правило, формируется из нескольких составляющих – домашние задания, участие в проектах, посещаемость и экзамены. Мы будем использовать две таблицы. Таблица заданий (assignments), как понятно из названия, будет содержать данные о самих заданиях, и таблица оценок (grades), в которой мы будем хранить данные о том, как ученик выполнил эти задания.

        DROP TABLE IF EXISTS assignments CASCADE;
DROP TABLE IF EXISTS grades CASCADE;

CREATE TABLE assignments (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    category VARCHAR(20),
    name VARCHAR(200),
    due_date DATE,
    weight FLOAT
);

CREATE TABLE grades (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    assignment_id INT,
    score INT,
    student_id INT,
    CONSTRAINT fk_assignments
        FOREIGN KEY(assignment_id)
        REFERENCES assignments(id),
    CONSTRAINT fk_students
        FOREIGN KEY(student_id)
        REFERENCES students(id)
);
    

Вместо того чтобы вставлять данные вручную, давайте загрузим их с помощью CSV-файла. Вы можете скачать файл из этого репозитория или создать его самостоятельно. Имейте в виду, чтобы разрешить pgAdmin доступ к данным, вам может понадобиться расширить права доступа к папке (в моем случае – это папка db_data).

        COPY assignments(category, name, due_date, weight)
FROM 'C:/Users/mgsosna/Desktop/db_data/assignments.csv'
DELIMITER ','
CSV HEADER;
/*
COPY 5
Query returned successfully in 118 msec.
*/

COPY grades(assignment_id, score, student_id)
FROM 'C:/Users/mgsosna/Desktop/db_data/grades.csv'
DELIMITER ','
CSV HEADER;
/*
COPY 25
Query returned successfully in 64 msec.
*/
    

Теперь давайте проверим, что мы всё сделали верно. Напишем запрос, который покажет среднюю оценку, по каждому виду заданий с группировкой по учителям.

        SELECT
    c.teacher,
    a.category,
    ROUND(AVG(g.score), 1) AS avg_score
FROM
    students AS s
INNER JOIN classrooms AS c
    ON c.id = s.classroom_id
INNER JOIN grades AS g
    ON s.id = g.student_id
INNER JOIN assignments AS a
    ON a.id = g.assignment_id
GROUP BY
    1,
    2
ORDER BY
    3 DESC;

/*
 teacher | category  | avg_score
 ------- | --------- | ---------
 Jonah   |  project  |     100.0
 Jonah   |  homework |      94.0
 Jonah   |  exam     |      92.5
 Mary    |  homework |      78.3
 Mary    |  exam     |      76.0
 Mary    |  project  |      69.5
*/
    

Отлично! Мы установили, настроили и наполнили базу данных.

***

Итак, в этой статье мы научились:

• создавать базу данных;
• создавать таблицы;
• наполнять таблицы данными;
• устанавливать связи между таблицами;

Теперь у нас всё готово, чтобы пробовать более сложные возможности SQL. Мы начнем с возможностей синтаксиса, которые, вероятно, вам еще не знакомы и которые откроют перед вами новые границы в написании SQL-запросов. Также мы разберем некоторый виды соединений таблиц (JOIN) и способы организации запросов в тех случаях, когда они занимают десятки или даже сотни строк.

В следующей части мы разберем:

• виды фильтраций в запросах;
• запросы с условиями типа if-else;
• новые виды соединений таблиц;
• функции для работы с массивами;

Материалы по теме

• 🐘 8 лучших GUI клиентов PostgreSQL в 2021 году
• 🐍🐬 Python и MySQL: практическое введение
• 🐍🗄️ Управление данными с помощью Python, SQLite и SQLAlchemy

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

Если вы хотите не просто смотреть видео курса «Основы SQL», но и самостоятельно экспериментировать с SQL запросами и видеть результаты их выполнения на живой базе данных, то можете установить бесплатную систему управления базами данных PostgreSQL и создать в ней демонстрационную базу с данными, которые показаны в видео. Эта статья содержит подробные инструкции по установке и настройке.

Если вы устанавливаете PostgreSQL только для использования в курсе «Основы SQL», то можете оставить почти все настройки по умолчанию, кроме локали, для которой нужно выбрать «Russian, Russia» (русский язык в стране Россия).

На этом установка PostrgreSQL завершена.

При первом запуске pgAdmin просит задать Master Password. Он будет использоваться для безопасного сохранения паролей к базам данных PosgreSQL, с которыми вы работаете через pgAdmin. Master Password можно выбрать любой, главное, запомните или запишите его.

Для удобства можно переключить интерфейс pgAdmin на русский язык. Для этого выберите меню File->Preferences, в появившемся окне в меню слева выберите Miscellaneous -> User Language, а затем в поле User Language справа «Russian».

Список существующих на сервере баз данных показывается в левом окне pgAdmin. Нас интересует база данных postgres, схема public и таблицы в ней. pgAdmin показывает много другой информации, не пугайтесь, если вы пока не понимаете, что это такое. Многое мы разберем в курсе, но преимущественно все это нужно только администраторам базы данных.

Пока в нашей базе нет никаких таблиц. Давайте создадим демонстрационные таблицы, которые используются в курсе, и заполним их данными.

3. Запустите загруженный файл в pgAgmin. Для этого нажмите на кнопку запуска в панели инструментов Запросника или на клавишу F5.

4. Проверьте правильность выполнения запроса. В нижней части экрана pgAdmin, на закладке «Сообщения» должны появиться результаты выполнения.

Итак, демонстрационные таблицы для курса «Основы SQL» созданы и заполнены данными, можно запускать SQL запросы.

После запуска SQL Shell задаст ряд вопросов о параметрах подключения к PostgreSQL, можно оставить все значения по умолчанию (если вы не меняли настройки при установке). После этого введите пароль пользователя postgres и можете начинать работать с базой данных.

Если вам не повезло, то в Windows SQL Shell запустится с неправильной кодировкой для русского языка, будет выдавать предупреждение и некоторые русские буквы будут выводиться неправильно.

i 'C:/путь/к/файлу/sql_foundation.sql'

СЕРГЕЙ СУПРУНОВ

PostgreSQL: графический клиент pgAdmin

В предыдущей статье, посвященной СУБД PostgreSQL (см. июльский номер журнала), упоминался графический клиент pgAdmin. В этой статье рассмотрим его основные функции и возможности.

Для тестирования с сайта www.pgadmin.org был скачан пакет pgAdmin III версии 1.0.2 для Windows 2000/XP (в виде msi-архива). Размер дистрибутива составляет менее 5,5 Мб. Установка пакета на операционную систему Windows XP Professional затруднений не вызвала. После традиционного вопроса о согласии с условиями лицензионного соглашения (пакет распространяется бесплатно) и нескольких секунд инсталляционных процедур программа была готова к работе.

При первом запуске было предложено выбрать предпочитаемый язык интерфейса. Среди нескольких десятков поддерживаемых языков отыскался и русский.

Открывшееся далее главное окно (рис. 1) своими очертаниями вызвало в памяти образ входящего в состав СУБД Oracle клиента PL/SQL Developer. Правда, некоторый аскетизм панели инструментов навевал мысль, что в функциональном плане различия будут более заметны, и никак не в пользу pgAdmin.

Рисунок 1

СУБД PostgreSQL версии 7.4.1 у меня была запущена на другой машине (под управлением FreeBSD), что и было указано при добавлении сервера (рис. 2). Поясню отсутствие пароля: моя рабочая машина прописана в файле pg_hba.conf (файл настройки доступа PostgreSQL) как доверенный хост, что позволяет соединяться с сервером БД, как мне заблагорассудится. Своя рука – владыка. Естественно, на рабочем сервере такая халатность не приветствуется.

Рисунок 2

Теперь слева будет дерево баз данных, справа отображается информация по выделенному объекту и ниже – SQL-команда, с помощью которой выделенный объект мог быть создан (рис. 3).

Рисунок 3

Для общего представления создадим новую таблицу. Для чего давим правую кнопку мыши над веткой «Таблицы», выбираем «Новый объект» – «Новая таблица» и начинаем вводить ее свойства (рис. 4).

Рисунок 4

Далее, на вкладке «Колонки» собираем (другое определение к этому процессу подобрать трудно) структуру таблицы, последовательно добавляя поля (рис. 5). Для каждого поля должно быть задано имя и тип данных. Дополнительно можно указать значение по умолчанию и/или отметить поле как «NOT NULL», запретив тем самым оставлять его без определенного значения.

Рисунок 5

На следующей вкладке можно задать ограничения (первичный и вторичный ключи, UNIQUE и CHECK).

Вкладка «Безопасность» позволяет раздать другим пользователям права доступа к создаваемой таблице.

Последняя закладка – «SQL» – позволяет посмотреть, каким SQL-запросом все это будет создано.

Огорчила невозможность исправить ошибку на стадии создания таблицы. Например, если вы неправильно задали тип данных для поля, то исправить это можно, только удалив поле и создав новое. В дальнейшем, если вернуться к свойствам уже созданной таблицы, кнопка «Изменить» присутствует, а вот во время создания новой – нет… А для ограничений нет даже этого – только удалять и создавать заново.

Еще один недостаток – об ошибке узнаешь только при попытке сохранить сделанные изменения.

Как уже было вскользь замечено, существующие таблицы можно изменять. Да и было бы странно, если бы такая возможность отсутствовала. Интерфейс тот же самый (за исключением упомянутой выше кнопки «Изменить»). На вкладке «SQL» будет формироваться код, который необходимо выполнить для осуществления желаемых изменений.

Код формируется, как и следовало ожидать, довольно просто, без «хитринки». Например, если дать указание при создании таблицы задать владельца, то в код будет просто добавлена команда «ALTER TABLE … OWNER …» после того, как таблица будет создана командой «CREATE TABLE …». Хотя по-хорошему это можно сделать одной командой.

Для работы с наполнением таблиц служит инструмент «Просмотр данных» (рис. 6). Выполненный в стиле «старого доброго» MS Access, он позволяет просматривать содержимое таблицы, добавлять новые строки, изменять и удалять существующие. Причем изменение данных возможно только в случае, если таблица создана с установленным флагом «Имеет OID». Как ни странно это выглядит, но в обоих случаях инструмент называется именно «Просмотр данных» (в англоязычном варианте «View Data»), несмотря на возможность редактирования, хотя заголовок окна гласит «pgAdmin III Edit data», и тоже в обоих случаях. Таким образом, этот недочет, допущенный при проектировании интерфейса, способен ввести в заблуждение, и сразу отыскать возможность работы с данными таблиц довольно трудно.

Рисунок 6

Более широкие возможности по работе с запросами предоставляет «Инструмент запросов» (рис. 7). Тут уже есть куда ввести любой SQL-запрос и посмотреть его результат. Закладка «Сообщения» внизу окна позволяет ознакомиться с такой информацией, как количество выбранных запросом записей и время выполнения запроса. Весьма полезная функция – «Объяснить запрос» (рис. 8), выводящая последовательность действий, которые будет выполнять PostgreSQL, обрабатывая данный запрос, с расчетом времени, затрачиваемого на каждую операцию – незаменимый инструмент для оптимизации запросов. В пункте меню «Запрос» – «Опции режима объяснения» можно включить и подробный разбор каждого запроса.

Рисунок 7

Рисунок 8

Функция обслуживания БД позволяет выполнить дефрагментацию и анализ структуры базы данных либо отдельных таблиц. Регулярная дефрагментация нужна для повышения скорости доступа к данным, а анализ поставляет служебную информацию оптимизатору СУБД, на основании которой последний способен обрабатывать запросы более эффективно.

Ну и наконец, в окне «Опции» вы можете произвести некоторые настройки интерфейса, такие как выбор языка и шрифта, по своему вкусу, а также настроить log-файл.

Включение опции «Показать» – «Системные объекты» приведет к отображению баз template0 и template1, а также в каждой пользовательской БД, помимо схемы public, хранящей пользовательские данные, будут видны и служебные схемы: information schema, pg_catalog, pg_temp_1, pg_toast (рис. 9). Естественно, что для работы с системными объектами нужно обладать соответствующими правами.

Рисунок 9

Трудно сказать, недостаток это или особенность реализации, но pgAdmin жестко привязывает имя пользователя к конкретному серверу. То есть при необходимости подсоединиться к БД с другими правами приходится создавать несколько «серверов», указывающих на одну СУБД, но с разными пользователями (рис. 10). Хотя в этом есть и определенные преимущества, т.к. можно поддерживать одновременно несколько подключений с различными правами. К слову, посмотреть все подключения к СУБД можно, выбрав «Сервис» – «Состояние сервера».

Рисунок 10

В заключение заметим, что в клиенте доступен файл помощи. Правда, он на английском языке, но тем не менее достаточно подробно, просто и с обилием иллюстраций описывает работу с клиентом, его настройку, а также содержит подробную справку по PostgreSQL 7.4. Показалось несколько неудобным, что развернутое окно помощи всегда находится поверх рабочего окна, закрывая часть данных. Хотя всегда можно открыть справку отдельно (соответствующий пункт присутствует в группе «pgAdmin III» меню «Пуск»), и тогда ничто не будет мешать переключаться между окнами любимой комбинацией .

Итак, мои общие впечатления – клиент добротный, не считая мелких недостатков; поддерживает практически все функции разработки и администрирования. Широкие возможности по анализу запросов, наглядное представление структуры БД и ее составляющих, интуитивно понятный, не обремененный ничем лишним, да еще и русскоязычный интерфейс делают pgAdmin удобным инструментом для работы с базами PostgreSQL, особенно для начинающих. Помимо версии для Windows, есть пакеты для Linux (Debian, RPM, Slackware packages), для FreeBSD. При желании пакет может быть собран и из исходных текстов.

Дополнительную информацию (в том числе и на русском языке) можно получить на сайте проекта.

Последнее обновление: 23.11.2022

Для упрощения администрирования на сервере postgresql в базовый комплект установки входит такой инструмент как pgAdmin.
Он представляет графический клиент для работы с сервером, через который мы в удобном виде можем создавать, удалять, изменять базы данных и управлять ими.
Так, на Windows после установки мы можем найти значок pgAdmin в меню Пуск и запустить его:

После этого нам откроется следующая программа pgAdmin. При открытии также отображится окно для ввода пароля для подключения к серверу Postgres:

Здесь необходимо ввести пароль для суперпользователя postgres, который был задан при установке PostgreSQL.

После успешного логина нам откроется содержимое сервера:

В частности, в узле Databases мы можем увидеть все имеющиеся базы данных. По умолчанию здесь есть только одна база данных — postgres.

Также в правой части мы можем увидеть узел Login/Group Roles, который предназначен для управления пользователями и их ролями.

И третий узел — Tablespaces позволяет управлять местом хранения файлов баз данных.

Теперь создадим свою базу данных. Для этого нажмем правой кнопкой мыши на узел Databases. И далее в контекстном меню
выберем Create->Database…

После этого нам отобразится окно для создания базы данных. Введем название для БД, например, test1 и нажмем на кнопку
«Save»:

После этого в древовидном меню слева отобразится содержимое созданной базы данных test1: