Заметка! Установка и настройка PostgreSQL 12 на Windows 10.
- Способ 1 – используем графический инструмент pgAdmin 4
- Способ 2 – используем язык SQL
- Example¶
- Исходные данные
- Создание таблицы с помощью языка SQL
- Создание таблиц в PostgreSQL с помощью pgAdmin 4 (видеоматериал)
- Шаг 1 – Подключение репозитория и обновление списка пакетов в системе
- Шаг 2 – Установка необходимых пакетов для PostgreSQL
- Шаг 3 – Проверка установки
- Настройка PostgreSQL 12 в Debian 10
- Создание пользователя и базы данных в PostgreSQL
- Разрешаем подключение к PostgreSQL по сети
- Удаленное подключение к PostgreSQL 12 с помощью pgAdmin 4
- Материалы по теме
- База данных в PostgreSQL
Способ 1 – используем графический инструмент pgAdmin 4
После этого откроется окно «Создание базы данных», где необходимо указать название создаваемой базы данных. И этого достаточно, чтобы создать базу данных с настройками по умолчанию, таким образом, если Вы не хотите внести какие-нибудь дополнительные параметры, можете сразу нажимать «Сохранить».
В данном случае создается база данных test_db.
Заметка! Как создать составной тип данных в PostgreSQL.
В результате в обозревателе отобразится новая база данных.
Способ 2 – используем язык SQL
Далее напишите простую SQL инструкцию
CREATE DATABASE test_db;
И запустите ее на выполнение, для этого нажмите на кнопку «Выполнить» (Execute).
Заметка! Создание базы данных в Microsoft SQL Server.
В итоге будет создана точно такая же база данных с настройками по умолчанию.
Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком SQL в любой системе управления базами данных.
Чтобы писать и выполнять SQL запросы в контексте новой базы данных, выделите ее и запустите редактор запросов.
В результате редактор запустится в контексте нужной базы данных, это будет отображено в строке подключения.
Кроме этого, создать базу данных можно и с помощью стандартной консольной утилиты psql, пример того, как это делается, можете посмотреть в статье про установку PostgreSQL на Linux.
Заметка! Установка и настройка PostgreSQL 12 на Debian 10.
На сегодня это все, надеюсь, материал, был Вам полезен, пока!
Use the fields in the General tab to identify the database:
Click the Definition tab to continue.
Use the Definition tab to set properties for the database:
Click the Security tab to continue.
Use the Security tab to assign privileges and define security labels.
Use the Privileges panel to assign privileges to a role. Click the Add icon
(+) to set privileges for database objects:
Click add to set additional privileges; to discard a privilege, click the trash
icon to the left of the row and confirm deletion in the Delete Row popup.
Use the Security Labels panel to define security labels applied to the
database. Click the Add icon (+) to add each security label selection:
To discard a security label, click the trash icon to the left of the row and
confirm deletion in the Delete Row popup.
Click the Parameters tab to continue.
Use the Parameters tab to set parameters for the database. Click the Add
icon (+) to add each parameter:
Click the Advanced tab to continue.
Use the Advanced tab to set advanced parameters for the database.
Click the SQL tab to continue.
Your entries in the Database dialog generate a SQL command (see an example
below). Use the SQL tab for review; revisit or switch tabs to make any changes
to the SQL command.
Example¶
В прошлой статье «Как создать базу данных в PostgreSQL» мы рассмотрели процесс создания пустой базы данных, т.е. того контейнера, в котором и хранятся все объекты этой базы данных. Одним из таких объектов является таблица, в которой и хранятся сами данные, т.е. таблица является неким физическим представлением определенной сущности реального мира.
Таким образом, данные хранятся не просто в базе данных, они хранятся в таблицах, которые в свою очередь хранятся в базе данных.
Поэтому сегодня давайте продолжим эту тему и рассмотрим процесс создания таблиц.
И так как, наверное, лучшим способом разобраться в какой-либо теме является ее рассмотрение на конкретном примере, т.е. на решении конкретной задачи, давайте сначала определим задачу, в которой нам нужно будет создать несколько таблиц.
Заметка! Для комплексного изучения языка SQL рекомендую почитать мою книгу «SQL код», в ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком SQL в любой системе управления базами данных.
Исходные данные
Давайте представим, что нам нужно создать две таблицы, первая – для хранения товаров, и вторая – для хранения категорий, к которым относятся эти товары.
Структура таблиц будет следующая:
При этом внести товар с несуществующей категорией нельзя, поэтому мы добавим еще и ограничение внешнего ключа.
Заметка! Установка и настройка PostgreSQL на Windows 10.
Затем запустится графический конструктор таблиц, где первым делом нам необходимо ввести название таблицы, сначала давайте создадим таблицу с категориями, чтобы потом в процессе создания таблицы с товарами у нас была возможность сразу определить ограничение внешнего ключа. Поэтому вводим categories.
В случае необходимости Вы можете изменить владельца таблицы и схему, в которой будет располагаться таблица.
Схема – это пространство имен в базе данных, своего рода контейнер объектов внутри базы данных, который позволяет логически разделять данные на схемы.
После того как название таблицы задано, мы можем переходить к определению столбцов. Для этого необходимо перейти на вкладку «Столбцы» и, используя кнопку плюс «+», добавить нужные столбцы.
Сначала создаем идентификатор категории, по условиям нашей задачи он должен отвечать определенным требованиям, поэтому мы должны задать следующие свойства у столбца:
В качестве типа данных выберем целочисленный тип integer.
Далее точно так же добавляем столбец для хранения наименования категории. При этом тип данных у нас уже должен быть текстовый, например, character varying (VARCHAR) с длинной 100. Он уже не должен быть первичным ключом и значения генерировать здесь не нужно.
После ввода всех данных мы можем сохранить все изменения, нажав на кнопку «Сохранить», тем самым создав таблицу.
Затем точно также создаем таблицу с товарами и определяем соответствующие для нее столбцы. Столбец идентификатора товара определяем, как первичный ключ, и включаем у него идентификацию с автоматическим генерированием значений.
Заметка! ТОП 5 популярных систем управления базами данных (СУБД).
После того как столбцы определены, нам необходимо добавить значение по умолчанию для столбца category, а также определить ограничение внешнего ключа.
Чтобы у столбца задать значение по умолчанию, необходимо открыть детализированные свойства столбца, перейти там на вкладку «Ограничения» и в поле «По умолчанию» указать значение, которое будет присваиваться по умолчанию, например, 1.
Осталось определить ограничение внешнего ключа, иными словами, чтобы столбец category таблицы goods ссылался на столбец category_id таблицы categories, таким образом, мы определим связь между этими таблицами.
Чтобы это сделать, переходим на вкладку «Ограничения» (основной формы создания таблиц), затем переходим на вкладку «Внешний ключ» и с помощью кнопки плюс «+» добавляем новый внешний ключ.
Вводим название ограничения, и в детализированных свойствах на вкладке «Столбцы» задаем связь между таблицами, т.е. указываем столбцы и нажимаем на плюс «+» для добавления связи.
После этого все требования, указанные в нашей задаче, будут выполнены и мы можем нажать кнопку «Сохранить» для создания таблицы.
Созданные таблицы отобразятся в обозревателе.
Создание таблицы с помощью языка SQL
Теперь давайте рассмотрим процесс создания таблиц в PostgreSQL на языке SQL.
Таблицы в SQL создаются с помощью инструкции CREATE TABLE.
Для создания точно таких же таблиц и решения нашей задачи мы можем использовать следующие инструкции языка SQL.
Примечание! Если чуть ранее Вы создавали эти таблицы с помощью конструктора, то перед выполнением указанных ниже инструкций создания таблиц необходимо предварительно удалить эти таблицы, т.е. выполнить инструкцию DROP TABLE. В противном случае инструкции создания таблиц завершатся ошибкой, так как в пределах одной схемы не может быть таблиц с одинаковым названием.
Чтобы выполнить инструкции, открываем редактор запросов (Запросник), вводим инструкции и нажимаем «Execute».
— Удаление таблиц
DROP TABLE IF EXISTS goods, categories;
— Создание таблицы categories
CREATE TABLE categories (
category_id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
category_name VARCHAR(100) NOT NULL
);
— Создание таблицы goods
CREATE TABLE goods (
product_id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category INT NOT NULL DEFAULT 1,
price NUMERIC(18,2) NULL,
CONSTRAINT fk_category_goods FOREIGN KEY (category) REFERENCES categories (category_id)
);
Создание таблиц в PostgreSQL с помощью pgAdmin 4 (видеоматериал)
https://youtube.com/watch?v=h5wgbJiSy7Q%3Ffeature%3Doembed
На сегодня это все, надеюсь, материал был Вам интересен и полезен, пока!
Всем привет! Сегодня мы разберем процесс установки PostgreSQL 12 на операционную систему Debian 10, а также выполним первоначальную настройку PostgreSQL. В результате у нас получится полноценный сервер баз данных, реализованный на базе Debian + PostgreSQL, и данный сервер можно использовать в сети предприятия.
Итак, давайте перейдем к установке и рассмотрим все шаги, которые необходимо выполнить, чтобы установить PostgreSQL 12 на Debian 10.
Установку я буду производить удаленно, используя программу PuTTY.
Шаг 1 – Подключение репозитория и обновление списка пакетов в системе
Установку и настройку PostgreSQL необходимо выполнять с правами пользователя root, поэтому давайте сразу переключимся на root.
Для этого пишем команду su и вводим пароль.
Далее, давайте проверим, нет ли в системе необходимых нам пакетов, так как вдруг нам и не нужно подключать дополнительный репозиторий.
Заметка! Что такое репозитории в Linux.
Для этого вводим следующую команду.
apt-cache search postgresql-12
Как видим, в Debian 10 нужной нам версии PostgreSQL нет, поэтому нам нужно подключить дополнительный репозиторий от разработчиков. Если у Вас более новая версия Debian и в стандартных репозиториях есть 12 версия PostgreSQL, то дополнительный репозиторий Вам подключать не нужно, т.е. данный шаг Вы можете пропустить.
А всем остальным, у которых также как у меня нет нужной версии, для подключения репозитория необходимо ввести следующую команду, которая создает файл в источниках с адресом нужного репозитория.
Примечание! Здесь обязательно стоить отметить, что этот репозиторий предназначен для Debian 10, для других версий адрес репозитория будет другим, например, если Вам нужно установить PostgreSQL на Debian 9, то в адресе репозитория вместо buster напишите stretch, т.е. замените кодовое имя версии.
После этого нам необходимо импортировать ключ подписи репозитория, для этого вводим команду.
Осталось обновить список пакетов в системе, это делаем стандартной командой.
И для проверки того, что теперь нам доступны пакеты PostgreSQL 12, давайте снова запустим команду поиска пакетов.
Теперь нужные пакеты нам доступны и мы можем переходить к установке PostgreSQL 12.
Заметка! Установка Debian 10 рядом с Windows 10.
Шаг 2 – Установка необходимых пакетов для PostgreSQL
Для установки PostgreSQL 12 и базовых стандартных утилит необходимо установить пакет postgresql-12, это делается следующей командой.
apt-get -y install postgresql-12
Шаг 3 – Проверка установки
Чтобы проверить, установился и запущен ли Postgres, давайте выполним следующую команду, которая покажет статус сервиса PostgreSQL.
systemctl status postgresql
Как видим, PostgreSQL 12 установился и работает.
Заметка! Установка MySQL 8 на Windows 10.
Настройка PostgreSQL 12 в Debian 10
PostgreSQL у нас установлен, однако на текущий момент им пользоваться пока нельзя, поэтому нам необходимо его настроить, в частности создать пользователя, указать какие сетевые интерфейсы будет прослушивать сервер, а также разрешить подключения по сети.
Создание пользователя и базы данных в PostgreSQL
После установки к серверу PostgreSQL мы можем подключиться только с помощью системного пользователя postgres, причем без пароля.
Давайте переключимся на пользователя postgres (данная учетная запись была создана автоматически во время установки PostgreSQL).
su — postgres
Затем запускаем утилиту psql – это консоль для PostgreSQL.
Первым делом нам нужно задать пароль для пользователя postgres.
Затем создаем нового пользователя на сервере PostgreSQL, так как работать от имени postgres крайне не рекомендуется.
где info_comp – это имя пользователя, ‘123456’ – это его пароль, Вы, соответственно, придумываете и подставляете свои данные.
Далее давайте создадим базу данных.
где test_db – это имя новой базы данных.
Теперь давайте дадим права на управление базой данных нашему новому пользователю.
grant all privileges on database test_db to info_comp;
Все готово, выходим из консоли.
Для проверки давайте подключимся к PostgreSQL от имени нового пользователя, на предложение о вводе пароля вводим пароль от новой учетной записи.
psql -h localhost test_db info_comp
Все работает, я подключился. Для выхода снова набираем q.
Для переключения обратно на root вводим exit.
Заметка! Установка Microsoft SQL Server 2019 Express на Windows 10.
Разрешаем подключение к PostgreSQL по сети
По умолчанию PostgreSQL прослушивает только адрес localhost, поэтому, для того чтобы мы могли подключаться по сети, нам нужно указать, какие сетевые интерфейсы будет просушивать PostgreSQL. Я для примера укажу, что прослушивать нужно все доступные интерфейсы. Если у Вас несколько сетевых интерфейсов, и Вы хотите, чтобы PostgreSQL использовал только один конкретный, то Вы его можете указать именно здесь.
Чтобы это сделать, открываем файл postgresql.conf, например, редактором nano.
Находим следующую строку.
#listen_addresses = ‘localhost’
и вносим следующие изменения (вместо звездочки Вы в случае необходимости указываете IP адрес нужного интерфейса).
listen_addresses = ‘*’
Сохраняем изменения сочетанием клавиш CTRL+O и подтверждаем нажатием Enter, затем просто закрываем редактор nano сочетанием клавиш CTRL+X.
Теперь давайте разрешим подключение из сети, я для примера разрешу подключаться из сети 192.168.1.0/24 с методом аутентификации md5.
Для этого открываем файл pg_hba.conf
Ищем следующие строки.
Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
И указываем нужную нам сеть (если IPv6 Вы не будете использовать, то можете закомментировать соответствующие строки знаком #).
Далее точно так же сохраняем изменения сочетанием клавиш CTRL+O, подтверждаем нажатием Enter и закрываем редактор nano сочетанием клавиш CTRL+X.
Перезапускаем PostgreSQL, чтобы изменения вступили в силу.
systemctl restart postgresql
Заметка! ТОП 5 популярных систем управления базами данных.
Удаленное подключение к PostgreSQL 12 с помощью pgAdmin 4
Далее откроется окно создания сервера, в котором нам нужно на вкладке «Общие» ввести название сервера.
А на вкладке «Соединение» указать IP адрес сервера и данные пользователя, под которым мы подключимся к серверу.
Заметка! ТОП 5 популярных дистрибутивов Linux для сервера.
В результате мы подключимся к серверу, а в обозревателе отобразятся все объекты на данном сервере.
На сегодня это все, надеюсь, материал был Вам полезен, пока!
Материалы по теме
Теперь всё готово к созданию таблиц. Давайте создадим набор таблиц, которые моделируют школу. Нам необходимы таблицы: ученики, классы, оценки. При создании модели данных необходимо учитывать, что в одном классе может быть много учеников, а у ученика может быть много оценок (такое отношение называется «один ко многим»).
Начнем с создания таблицы классов (). Таблица будет простой: она будет содержать идентификатор 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. C ASCADE – автоматически удалит или изменит строки из зависимой таблицы, при внесении изменений в главную. В нашем случае нет ничего страшного в удалении таблицы, поскольку, если мы на это пошли, значит мы будем пересоздавать всё с нуля, и остальные таблицы тоже удалятся.
DROP TABLE IF EXISTS позволит нам систематизировать схему нашей базы данных и создать скрипты, которые будут очень удобны, если мы захотим внести изменения – например, добавить таблицу, изменить тип данных поля и т. д. Для этого нам просто нужно будет внести изменения в уже готовый скрипт и перезапустить его.
Ничего нам не мешает добавить наш код в систему контроля версий. Весь код для создания базы данных из этой статьи вы можете посмотреть по
Также вы могли обратить внимание на четвертую строчку. Здесь мы определили, что колонка 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)
);
И снова мы перед созданием новой таблицы удаляем старую, если она существует, добавляем поле , которое автоматически увеличивает своё значение и имя с типом данных VARCHAR (строка) и максимальной длиной 100 символов. Также в эту таблицу мы добавили колонку с идентификатором класса (), и с седьмой по девятую строку установили, что ее значение указывает на колонку в таблице классов (
Мы определили, что является внешним ключом. Это означает, что мы задали правила, по которым данные будут записываться в таблицу учеников (). То есть Postgres на данном этапе не позволит нам вставить строку с данными в таблицу учеников (), в которой указан идентификатор класса (), не существующий в таблице . Например: у нас в таблице классов 10 записей (id с 1 до 10), система не даст нам вставить данные в таблицу учеников, у которых указан идентификатор класса 11 и больше.
Невозможно вставить данные, поскольку в таблице классов нет записи с id = 1
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)=
is not present in table
«classrooms».
SQL state: 23503
*/
Теперь давайте добавим немного данных в таблицу классов (). Так как мы определили, что значение в поле id будет увеличиваться автоматически, нам нужно только добавить имена учителей.
Прекрасно! Теперь у нас есть записи в таблице классов, и мы можем добавить данные в таблицу учеников, а также установить нужные (с таблицей классов).
Но что же случится, если у нас появится новый ученик, которому ещё не назначили класс? Неужели нам придется ждать, пока станет известно в каком он классе, и только после этого добавить его запись в базу данных?
Конечно же, нет. Мы установили внешний ключ, и он будет блокировать запись, поскольку ссылка на несуществующий id класса невозможна, но мы можем в качестве идентификатора класса () передать null. Это можно сделать двумя способами: указанием null при записи значений, либо просто передачей только имени.
И наконец, давайте заполним таблицу успеваемости. Этот параметр, как правило, формируется из нескольких составляющих – домашние задания, участие в проектах, посещаемость и экзамены. Мы будем использовать две таблицы. Таблица заданий (), как понятно из названия, будет содержать данные о самих заданиях, и таблица оценок (), в которой мы будем хранить данные о том, как ученик выполнил эти задания.
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
,
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)
);
Теперь давайте проверим, что мы всё сделали верно. Напишем запрос, который покажет среднюю оценку, по каждому виду заданий с группировкой по учителям.
Отлично! Мы установили, настроили и наполнили базу данных.
Итак, в этой статье мы научились:
Теперь у нас всё готово, чтобы пробовать более сложные возможности SQL. Мы начнем с возможностей синтаксиса, которые, вероятно, вам еще не знакомы и которые откроют перед вами новые границы в написании SQL-запросов. Также мы разберем некоторый виды соединений таблиц (JOIN) и способы организации запросов в тех случаях, когда они занимают десятки или даже сотни строк.
В следующей части мы разберем:
База данных в PostgreSQL
Откроется окошко «Создать базу данных», пропишите название будущей базы. Если вы не собираетесь включать – отключать какие — то дополнительные опции для работы базы, можно смело нажать «Сохранить». Такая настройка достаточна для создания базы с настройками по умолчанию – базовыми, которые вполне могут обеспечить стабильную работу базы.
В моем случаи на скриншоте ниже создана мной база данных под именем test_db.
Как видно на скриншоте ниже новая база данных успешно создана.
Вариант 2 — Использование языка программирования SQL
Следующим шагом пропишите SQL запрос:
CREATE DATABASE test_db; — в данном случаи инструкции test_db – это название моей базы, вы должны прописать свою.
Теперь ее осталось только запустить для этого нажмите на кнопочку «Выполнить».
Чтобы изменить-прописать какие либо SQL запросы для исполнения базой выделите их и запустите редактор.
В Linux для создания базы можно воспользоваться обычной консольной утилитой psql.