Установка и основы работы с PostgreSQL

Linux, Ubuntu | Amber | 2 комментария

PostgreSQL – это система управления базами данных (СУБД), которая использует язык запросов SQL. Благодаря своей надёжности и многофункциональности она часто используется для хранения данных приложений.

Данное руководство научит создавать, настраивать и управлять таблицами в интерфейсе postgreSQL.

Установка PostgreSQL

Примечание: В данном разделе описана установка PostgreSQL в Ubuntu.

Чтобы запустить установку, введите:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

После установки создайте нового пользователя для управления БД.

sudo adduser postgres_user

Теперь при помощи стандартного пользователя PostgreSQL (по имени postgres) нужно создать БД и передать права на неё новому пользователю.

sudo su - postgres
psql

После этого на экране появится командная строка PostgreSQL.

Создайте нового пользователя, одноименно пользователю системы. Затем создайте БД.

CREATE USER postgres_user WITH PASSWORD 'password';
CREATE DATABASE my_postgres_db OWNER postgres_user;

Закройте интерфейс:

\q

Закройте сессию пользователя postgres и войдите как новый пользователь:

exit
sudo su - postgres_user

Войдите в базу данных:

psql my_postgres_db

Синтаксис таблиц PostgreSQL

Новая БД ещё не содержит таблиц. В этом можно убедиться, запросив список доступных таблиц:

\d
No relations found.

Чтобы создать новую таблицу, придерживайтесь следующего синтаксиса:

CREATE TABLE new_table_name (
table_column_title TYPE_OF_DATA column_constraints,
next_column_title TYPE_OF_DATA column_constraints,
table_constraint
table_constraint
) INHERITS existing_table_to_inherit_from;

Часть от закрытой скобки и до запятой – это дополнительная конструкция, позволяющая наследовать все столбцы существующей таблицы в дополнение к столбцам, перечисленным в команде.

Часть синтаксиса, взятая в скобки, делится на две части: определения столбцов и ограничения таблиц.

Определения столбцов PostgreSQL

Определение столбцов осуществляется согласно шаблону:

column_name data_type (optional_data_length_restriction) column_constraints

Примечание: Имя столбца должно быть описательным.

Типы данных PostgreSQL

• boolean: (или bool) объявляет значения true и false.

Символьные значения

• char: хранит один символ
• char (#): содержит # количество символов; свободные места будут заполнены пробелами.
• varchar (#): хранит данные переменной длины (не в Юникоде); параметр # определяет длину строки.

Целые значения

• smallint: целое число между -32768 и 32767.
• int: целое число между -214783648 и 214783647.
• serial: целое число с автоувеличением.

Числа с плавающей точкой

• float (#): число с плавающей точкой, где # – количество битов.
• real: 8-битное число с плавающей точкой.
• numeric (#,after_dec): число с # количеством цифр, где after_dec – количество цифр после десятичного знака

Дата и время

• date: дата
• time: время
• timestamp: дата и время
• timestamptz: дата, время и часовой пояс
• interval: разница между двумя значениями timestamp

Геометрические типы

• point: хранит пару координат определённой точки.
• line: набор точек, определяющих линию.
• lseg: набор данных, определяющий сегмент линии.
• box: набор данных, который определяет прямоугольник.
• polygon: набор данных, определяющий любое закрытое пространство

Сетевые адреса

• inet: IP-адрес
• macaddr: MAC адреса.

Ограничения столбцов и таблиц PostreSQL

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

• NOT NULL: столбец не может иметь значение 0.
• UNIQUE: значение столбца должно быть разным для каждой записи. Нуль всегда считается уникальным значением.
• PRIMARY KEY: комбинирует первые два ограничения. Можно использовать только раз на всю таблицу.
• CHECK: проверяет, истинно ли условие для значений в столбце.
• REFERENCES: значение должно существовать в столбце в другой таблице.

После определения ограничений столбцов можно объявить ограничения для всей таблицы. Среди них UNIQUE, PRIMARY KEY, CHECK и REFERENCES.

Создание таблицы в PostgreSQL

Попробуйте создать тестовую таблицу. Для примера назовём её pg_equipment и поместим в неё данные о различном оборудовании для детских площадок. Введите следующее определение таблицы:

CREATE TABLE pg_equipment (
equip_id serial PRIMARY KEY,
type varchar (50) NOT NULL,
color varchar (25) NOT NULL,
location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
install_date date
);
NOTICE:  CREATE TABLE will create implicit sequence "pg_equipment_equip_id_seq" for serial column "pg_equipment.equip_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pg_equipment_pkey" for table "pg_equipment"
CREATE TABLE

Чтобы просмотреть таблицу, введите в командную строку \d.

\d
List of relations
Schema |           Name            |   Type   |     Owner
--------+---------------------------+----------+---------------
public | pg_equipment              | table    | postgres_user
public | pg_equipment_equip_id_seq | sequence | postgres_user
(2 rows)

Эта команда выведет на экран таблицу и создаст последовательность согласно типу данных serial.

Изменение таблиц в PostgreSQL

Теперь попробуйте внести в таблицу изменения. Для этого используется следующий синтаксис:

ALTER TABLE table_name Action_TO_Take;

К примеру, можно добавить в таблицу pg_equipment новый столбец.

ALTER TABLE pg_equipment ADD COLUMN functioning bool;
ALTER TABLE

Чтобы просмотреть новый столбец, введите:

\d pg_equipment
Column    |         Type          |                            Modifiers
--------------+-----------------------+-----------------------------------------------------------------
equip_id     | integer               | not null default nextval('pg_equipment_equip_id_seq'::regclass)
type         | character varying(50) | not null
color        | character varying(25) | not null
location     | character varying(25) |
install_date | date                  |
functioning  | boolean               |
. . .

Чтобы добавить значение по умолчанию, которое указывает, что оборудование следует рассматривать как рабочее, если не указано иное, используйте следующую команду:

ALTER TABLE pg_equipment ALTER COLUMN functioning SET DEFAULT 'true';

Чтобы указать также, что значение не может быть нулём, используйте:

ALTER TABLE pg_equipment ALTER COLUMN functioning SET NOT NULL;

Чтобы переименовать столбец, введите:

ALTER TABLE pg_equipment RENAME COLUMN functioning TO working_order;

Удалить столбец можно с помощью команды:

ALTER TABLE pg_equipment DROP COLUMN working_order;

Чтобы переименовать всю таблицу, введите:

ALTER TABLE pg_equipment RENAME TO playground_equip;

Удаление таблиц PostgreSQL

Чтобы удалить таблицу PostgreSQL, введите:

DROP TABLE playground_equip;
DROP TABLE

Если применить эту команду к таблице, которой не существует, появится следующее сообщение об ошибке:

ERROR: table "playground_equip" does not exist

Чтобы избежать этой ошибки, можно добавить в команду IF EXISTS; тогда таблица будет удалена, если она существует. В любом случае команда будет выполнена успешно.

DROP TABLE IF EXISTS playground_equip;
NOTICE:  table "playground_equip" does not exist, skipping
DROP TABLE

В этот раз команда сообщает, что заданная таблица не найдена, но не возвращает ошибки.

Заключение

Данное руководство охватывает основы работы с таблицами PostgreSQL. Эти навыки помогут управлять данными PostgreSQL  при помощи стороннего приложения или командной строки.