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 при помощи стороннего приложения или командной строки.