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

Введение

В этой лабораторной работе мы изучим создание таблиц и типы данных в PostgreSQL. Цель состоит в том, чтобы понять основные типы данных, такие как целые числа, текст, даты и логические значения, которые имеют решающее значение для определения структуры таблиц и обеспечения целостности данных.

Мы подключимся к базе данных PostgreSQL с помощью psql, создадим таблицы с первичными ключами, используя SERIAL, и добавим основные ограничения, такие как NOT NULL и UNIQUE. Затем мы проверим структуру таблицы и вставим данные, чтобы продемонстрировать использование различных типов данных, таких как INTEGER, SMALLINT, TEXT, VARCHAR(n) и CHAR(n).

Изучение типов данных PostgreSQL

В этом шаге мы изучим некоторые из основных типов данных, доступных в PostgreSQL. Понимание типов данных имеет решающее значение для определения структуры таблиц и обеспечения целостности данных. Мы рассмотрим общие типы, такие как целые числа, текст, даты и логические значения.

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

sudo -u postgres psql

Теперь вы должны находиться в интерактивном терминале PostgreSQL. Вы увидите приглашение, похожее на postgres=#.

Теперь давайте изучим некоторые основные типы данных.

1. Целочисленные типы (Integer Types):

PostgreSQL предлагает несколько целочисленных типов с различными диапазонами. Наиболее распространенными являются INTEGER (или INT) и SMALLINT.

• INTEGER: Типичный выбор для большинства целочисленных значений.
• SMALLINT: Используется для меньших целочисленных значений для экономии места.

Давайте создадим простую таблицу для демонстрации этих типов:

CREATE TABLE integer_example (
    id SERIAL PRIMARY KEY,
    quantity INTEGER,
    small_quantity SMALLINT
);

Здесь SERIAL — это специальный тип, который автоматически генерирует последовательность целых чисел, что делает его подходящим для первичных ключей.

Теперь вставьте некоторые данные:

INSERT INTO integer_example (quantity, small_quantity) VALUES (100, 10);
INSERT INTO integer_example (quantity, small_quantity) VALUES (2000000, 32767);

Вы можете просмотреть данные, используя:

SELECT * FROM integer_example;

Вывод (Output):

 id | quantity | small_quantity
----+----------+----------------
  1 |      100 |             10
  2 |  2000000 |          32767
(2 rows)

2. Текстовые типы (Text Types):

PostgreSQL предоставляет TEXT, VARCHAR(n) и CHAR(n) для хранения текста.

• TEXT: Хранит строки переменной длины неограниченной длины.
• VARCHAR(n): Хранит строки переменной длины с максимальной длиной n.
• CHAR(n): Хранит строки фиксированной длины n. Если строка короче, она дополняется пробелами.

Давайте создадим еще одну таблицу:

CREATE TABLE text_example (
    id SERIAL PRIMARY KEY,
    name TEXT,
    short_name VARCHAR(50),
    code CHAR(5)
);

Вставьте некоторые данные:

INSERT INTO text_example (name, short_name, code) VALUES ('PostgreSQL Database', 'PostgreSQL', 'PG001');
INSERT INTO text_example (name, short_name, code) VALUES ('Another Database', 'Another', 'AD002');

Просмотрите данные:

SELECT * FROM text_example;

Вывод (Output):

 id |        name        | short_name | code
----+--------------------+------------+-------
  1 | PostgreSQL Database | PostgreSQL | PG001
  2 | Another Database   | Another    | AD002
(2 rows)

3. Типы даты и времени (Date and Time Types):

PostgreSQL предлагает DATE, TIME, TIMESTAMP и TIMESTAMPTZ для обработки значений даты и времени.

• DATE: Хранит только дату (год, месяц, день).
• TIME: Хранит только время (час, минута, секунда).
• TIMESTAMP: Хранит как дату, так и время без информации о часовом поясе.
• TIMESTAMPTZ: Хранит как дату, так и время с информацией о часовом поясе.

Создайте таблицу:

CREATE TABLE datetime_example (
    id SERIAL PRIMARY KEY,
    event_date DATE,
    event_time TIME,
    event_timestamp TIMESTAMP,
    event_timestamptz TIMESTAMPTZ
);

Вставьте данные:

INSERT INTO datetime_example (event_date, event_time, event_timestamp, event_timestamptz)
VALUES ('2023-10-27', '10:30:00', '2023-10-27 10:30:00', '2023-10-27 10:30:00+00');

Просмотрите данные:

SELECT * FROM datetime_example;

Вывод (Output):

 id | event_date | event_time |   event_timestamp   |    event_timestamptz
----+------------+------------+---------------------+----------------------------
  1 | 2023-10-27 | 10:30:00   | 2023-10-27 10:30:00 | 2023-10-27 10:30:00+00
(1 row)

4. Логический тип (Boolean Type):

Тип BOOLEAN хранит значения true/false (истина/ложь).

Создайте таблицу:

CREATE TABLE boolean_example (
    id SERIAL PRIMARY KEY,
    is_active BOOLEAN
);

Вставьте данные:

INSERT INTO boolean_example (is_active) VALUES (TRUE);
INSERT INTO boolean_example (is_active) VALUES (FALSE);

Просмотрите данные:

SELECT * FROM boolean_example;

Вывод (Output):

 id | is_active
----+-----------
  1 | t
  2 | f
(2 rows)

Наконец, выйдите из терминала psql:

\q

Теперь вы изучили некоторые из основных типов данных в PostgreSQL. Эти типы данных формируют строительные блоки для создания надежных и хорошо определенных схем баз данных.

Создание таблиц с первичными ключами

В этом шаге мы научимся создавать таблицы с первичными ключами в PostgreSQL. Первичный ключ (primary key) — это столбец или набор столбцов, который однозначно идентифицирует каждую строку в таблице. Он обеспечивает уникальность и служит важным элементом для целостности данных и связей между таблицами.

Сначала давайте подключимся к базе данных PostgreSQL. Откройте терминал и используйте команду psql для подключения к базе данных postgres от имени пользователя postgres.

sudo -u postgres psql

Теперь вы должны находиться в интерактивном терминале PostgreSQL.

Понимание первичных ключей (Understanding Primary Keys)

Первичный ключ имеет следующие характеристики:

• Он должен содержать уникальные значения.
• Он не может содержать значения NULL.
• Таблица может иметь только один первичный ключ.

Создание таблицы с первичным ключом (Creating a Table with a Primary Key)

Существует два распространенных способа определения первичного ключа при создании таблицы:

1. Использование ограничения PRIMARY KEY в определении столбца:

   CREATE TABLE products (
       product_id SERIAL PRIMARY KEY,
       product_name VARCHAR(100),
       price DECIMAL(10, 2)
   );
   

   В этом примере product_id определен как первичный ключ с использованием ограничения PRIMARY KEY. Ключевое слово SERIAL автоматически создает последовательность для генерации уникальных целочисленных значений для product_id.

2. Использование ограничения PRIMARY KEY отдельно:

   CREATE TABLE customers (
       customer_id INT,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       PRIMARY KEY (customer_id)
   );
   

   Здесь ограничение PRIMARY KEY определено отдельно, указывая, что столбец customer_id является первичным ключом.

Пример: Создание таблицы users с первичным ключом

Давайте создадим таблицу users с первичным ключом, используя тип SERIAL для автоматической генерации ID:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100),
    registration_date DATE
);

В этой таблице:

• user_id является первичным ключом, автоматически генерируемым с использованием SERIAL.
• username — это уникальное имя пользователя для каждого пользователя.
• email — это адрес электронной почты пользователя.
• registration_date — это дата регистрации пользователя.

Теперь давайте вставим некоторые данные в таблицу users:

INSERT INTO users (username, email, registration_date) VALUES ('john_doe', 'john.doe@example.com', '2023-10-26');
INSERT INTO users (username, email, registration_date) VALUES ('jane_smith', 'jane.smith@example.com', '2023-10-27');

Вы можете просмотреть данные, используя:

SELECT * FROM users;

Вывод (Output):

 user_id |  username  |        email        | registration_date
---------+------------+---------------------+---------------------
       1 | john_doe   | john.doe@example.com | 2023-10-26
       2 | jane_smith | jane.smith@example.com | 2023-10-27
(2 rows)

Попытка вставить дубликат первичного ключа (Trying to insert a duplicate primary key)

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

INSERT INTO users (user_id, username, email, registration_date) VALUES (1, 'duplicate_user', 'dup@example.com', '2023-10-28');

Вывод (Output):

ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (user_id)=(1) already exists.

Это демонстрирует ограничение первичного ключа в действии, предотвращая дублирующиеся значения.

Наконец, выйдите из терминала psql:

\q

Теперь вы успешно создали таблицу с первичным ключом и увидели, как он обеспечивает уникальность. Это фундаментальная концепция в проектировании баз данных.

Добавление базовых ограничений (NOT NULL, UNIQUE)

В этом шаге мы научимся добавлять основные ограничения (constraints) к таблицам в PostgreSQL. Ограничения — это правила, которые обеспечивают целостность и согласованность данных. Мы сосредоточимся на двух фундаментальных ограничениях: NOT NULL и UNIQUE.

Сначала давайте подключимся к базе данных PostgreSQL. Откройте терминал и используйте команду psql для подключения к базе данных postgres от имени пользователя postgres.

sudo -u postgres psql

Теперь вы должны находиться в интерактивном терминале PostgreSQL.

Понимание ограничений (Understanding Constraints)

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

1. Ограничение NOT NULL

Ограничение NOT NULL гарантирует, что столбец не может содержать значения NULL. Это полезно, когда определенная часть информации важна для каждой строки в таблице.

2. Ограничение UNIQUE

Ограничение UNIQUE гарантирует, что все значения в столбце различны. Это полезно для столбцов, которые должны иметь уникальные идентификаторы или значения, такие как имена пользователей или адреса электронной почты (помимо первичного ключа).

Добавление ограничений при создании таблицы (Adding Constraints During Table Creation)

Вы можете добавлять ограничения при создании таблицы. Давайте создадим таблицу под названием employees с ограничениями NOT NULL и UNIQUE:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

В этой таблице:

• employee_id является первичным ключом.
• first_name и last_name объявлены как NOT NULL, что означает, что они должны иметь значение для каждого сотрудника.
• email объявлен как UNIQUE, что гарантирует, что у каждого сотрудника будет уникальный адрес электронной почты.

Теперь давайте попробуем вставить некоторые данные, которые нарушают эти ограничения.

Попытка вставить значение NULL в столбец NOT NULL:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES (NULL, 'Smith', 'john.smith@example.com', '2023-10-27');

Вывод (Output):

ERROR:  null value in column "first_name" of relation "employees" violates not-null constraint
DETAIL:  Failing row contains (1, null, Smith, john.smith@example.com, 2023-10-27).

Эта ошибка указывает на то, что вы не можете вставить значение NULL в столбец first_name из-за ограничения NOT NULL.

Попытка вставить дублирующееся значение в столбец UNIQUE:

Сначала вставьте допустимую строку:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Smith', 'john.smith@example.com', '2023-10-27');

Теперь попробуйте вставить другую строку с тем же адресом электронной почты:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Doe', 'john.smith@example.com', '2023-10-28');

Вывод (Output):

ERROR:  duplicate key value violates unique constraint "employees_email_key"
DETAIL:  Key (email)=(john.smith@example.com) already exists.

Эта ошибка указывает на то, что вы не можете вставить дублирующийся адрес электронной почты из-за ограничения UNIQUE.

Вставка допустимых данных:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Doe', 'jane.doe@example.com', '2023-10-28');

Просмотрите данные:

SELECT * FROM employees;

Вывод (Output):

 employee_id | first_name | last_name |        email        | hire_date
-------------+------------+-----------+---------------------+------------
           1 | John       | Smith     | john.smith@example.com | 2023-10-27
           2 | Jane       | Doe       | jane.doe@example.com  | 2023-10-28
(2 rows)

Наконец, выйдите из терминала psql:

\q

Теперь вы успешно создали таблицу с ограничениями NOT NULL и UNIQUE и увидели, как они обеспечивают целостность данных.

Проверка структуры таблицы

В этом шаге мы научимся проверять структуру таблиц в PostgreSQL. Понимание структуры таблицы, включая имена столбцов, типы данных, ограничения и индексы, необходимо для эффективного запроса и манипулирования данными.

Сначала давайте подключимся к базе данных PostgreSQL. Откройте терминал и используйте команду psql для подключения к базе данных postgres от имени пользователя postgres.

sudo -u postgres psql

Теперь вы должны находиться в интерактивном терминале PostgreSQL.

Команда \d

Основным инструментом для проверки структуры таблицы в psql является команда \d (describe - описать). Эта команда предоставляет подробную информацию о таблице, включая:

• Имена столбцов и типы данных
• Ограничения (первичные ключи, уникальные ограничения, ограничения not-null)
• Индексы

Проверка таблицы employees

Давайте проверим структуру таблицы employees, которую мы создали на предыдущем шаге:

\d employees

Вывод (Output):

                                           Table "public.employees"
   Column    |          Type          | Collation | Nullable |                    Default
-------------+------------------------+-----------+----------+------------------------------------------------
 employee_id | integer                |           | not null | nextval('employees_employee_id_seq'::regclass)
 first_name  | character varying(50)  |           | not null |
 last_name   | character varying(50)  |           | not null |
 email       | character varying(100) |           |          |
 hire_date   | date                   |           |          |
Indexes:
    "employees_pkey" PRIMARY KEY, btree (employee_id)
    "employees_email_key" UNIQUE CONSTRAINT, btree (email)

Вывод предоставляет следующую информацию:

• Table "public.employees": Указывает имя таблицы и схему (schema).
• Column: Перечисляет имена столбцов (employee_id, first_name, last_name, email, hire_date).
• Type: Показывает тип данных каждого столбца (integer, character varying, date).
• Nullable: Указывает, может ли столбец содержать значения NULL (not null или пусто).
• Default: Показывает значение по умолчанию для столбца (если есть).
• Indexes: Перечисляет индексы, определенные для таблицы, включая первичный ключ (employees_pkey) и уникальное ограничение для столбца email (employees_email_key).

Проверка других таблиц

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

\d users

Вывод (Output):

                                            Table "public.users"
      Column       |          Type          | Collation | Nullable |                Default
-------------------+------------------------+-----------+----------+----------------------------------------
 user_id           | integer                |           | not null | nextval('users_user_id_seq'::regclass)
 username          | character varying(50)  |           |          |
 email             | character varying(100) |           |          |
 registration_date | date                   |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)
    "users_username_key" UNIQUE CONSTRAINT, btree (username)

Перечисление всех таблиц

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

\dt

Вывод (Output) (будет варьироваться в зависимости от созданных вами таблиц):

              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | boolean_example  | table | postgres
 public | customers        | table | postgres
 public | datetime_example | table | postgres
 public | employees        | table | postgres
 public | integer_example  | table | postgres
 public | products         | table | postgres
 public | text_example     | table | postgres
 public | users            | table | postgres
(8 rows)

Наконец, выйдите из терминала psql:

\q

Теперь вы узнали, как проверять структуру таблиц в PostgreSQL с помощью команд \d и \dt. Это фундаментальный навык для понимания и работы с базами данных.

Резюме

В этой лабораторной работе мы изучили основные типы данных PostgreSQL, сосредоточившись на целых числах и тексте. Мы узнали о INTEGER и SMALLINT для хранения целочисленных значений, понимая их различные диапазоны и варианты использования. Мы также рассмотрели TEXT, VARCHAR(n) и CHAR(n) для обработки текстовых данных, отметив различия между строками переменной и фиксированной длины.

Кроме того, мы попрактиковались в создании таблиц с использованием этих типов данных, включая использование SERIAL для автоматической генерации последовательностей первичных ключей. Мы вставили примеры данных в таблицы и проверили данные с помощью операторов SELECT, закрепив наше понимание того, как эти типы данных ведут себя в практическом контексте базы данных.