Таблицы — Основы SQL

Что такое типы данных
- Типы данных
Выводы

В этом уроке поближе познакомимся с таблицами в SQL и данными, которые могут в них храниться.

Что такое типы данных

К таблицам предъявляется требование о типе данных поля или столбца. Тип данных накладывает ограничение на то, какие данные могут содержаться в столбце и какие операции можно с ними совершать.

С числами мы можем выполнять операции сложения, умножения и деления. Со строками этого сделать не сможем. Но, например, можем соединить две строки или взять часть строки.

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

Типы данных

У каждого поля в PostgreSQL определенный тип, который задается на этапе создания таблицы. Это значит, что значением этого поля могут быть только определенные данные.

CREATE TABLE courses (
    id            integer,
    name          varchar(255),
    slug          varchar(255),
    lessons_count integer,
    body          text
);

INSERT INTO courses (id, name, slug, lessons_count, body) VALUES (1, 'Основы PHP', 'php-basics', 30, 'Курс по основам PHP');
INSERT INTO courses (id, name, slug, lessons_count, body) VALUES (2, 'ООП Python', 'python-oop', 15, 'Курс по ООП Python');

View On DB Fiddle

Запросы выше будут успешно выполнены, так как все добавляемые данные соответствуют типам колонок. Следующий запрос завершится ошибкой. В нем мы пытаемся вставить строку в колонку lessons_count, которое предназначено для целых чисел. База данных выдаст ошибку при попытке выполнить подобный запрос. Если поле имеет числовой тип, то в него невозможно вставить строку, и наоборот.

INSERT INTO courses (id, name, slug, lessons_count, body) VALUES (3, 'Основы SQL', 'sql-basics', 'wrong value', 'Курс по основам SQL');
-- Выполняем запрос на вставку передавая в lessons_count строку вместо числа
ERROR:  invalid input syntax for type integer: "wrong value"

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

Строки

Для строк в базах данных в основном используются два типа:

varchar — для строк с ограничением максимальной длины
text — для строк без ограничения. Как правило, это полноценные тексты

В базах данных нельзя оставить первый тип без указания длины. Это связано с производительностью и эффективностью. Данные в базах данных физически хранятся на дисках в файлах. Быстрый доступ к этим данным возможен только тогда, когда у данных фиксированный размер. Это позволяет быстро перемещаться по ним и считать смещения.

Если размер данных не известен, то придется просматривать весь файл в поисках нужного значения. Чтобы избежать подобной ситуации, тип text хранится отдельно. Это тоже негативно влияет на скорость, но уже не так сильно. Если размер строки известен или он меньше какого-то значения, то предпочтительнее использовать varchar.

Имя

Описание

character varying(n), varchar(n)

строка ограниченной переменной длины

text

строка неограниченной переменной длины

varchar. Полное название типа character varying (varchar может использоваться как псевдоним). Размер строки с таким типом указывается в скобках после названия типа, например, varchar(10). Это значит, что в поле с таким типом можно записать строку длиной до 10 символов.
text. Не требует указания размера и может содержать текст произвольной длины

Пример создания таблицы с такими типами:

CREATE TABLE topics (
    title varchar(255),
    body text
);

Для строк в SQL используют одинарные кавычки:

INSERT INTO topics (body, title) VALUES ('Architecto id autem modi.', 'est iste corporis');

Если размер строки превышает допустимую, то запрос завершится с ошибкой:

INSERT INTO topics (body, title) VALUES ('Architecto id autem modi.', 'Explicabo inventore molestiae consequatur commodi velit quasi itaque. Qui ducimus ad. Voluptatibus dolores consequatur nemo excepturi vitae minima inventore voluptas. Quo dolorem ab eligendi qui similique suscipit aperiam eius. Et est odio iusto repellendus. Odio ea voluptatem voluptatem expedita inventore maiores ad unde');
Query Error: error: value too long for type character varying(255)

Числа

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

Имя

Описание

Диапазон

integer

типичный выбор для целых чисел

-2147483648 .. +2147483647

bigint

целое в большом диапазоне

-9223372036854775808 .. 9223372036854775807

real

вещественное число с переменной точностью

-2147483648 .. +2147483647, точность в пределах 6 десятичных цифр

Пример создания таблицы с такими типами:

CREATE TABLE users (
    id          bigint,
    age         integer,
    weight      real
);

INSERT INTO users (id, age, weight) VALUES (1, 38, 67.4);
INSERT INTO users (id, age, weight) VALUES (2, 15, 54.3);

View On DB Fiddle

Также, как и со строками, если попытаться вставить недопустимое значение, то запрос упадет с ошибкой

CREATE TABLE users (
    id          bigint,
    age         integer,
    weight      real
);
-- Пробуем вставить в age больше 3 миллиардов
INSERT INTO users (id, age, weight) VALUES (3, 3147483648, 67.4);
Schema Error: error: integer out of range

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

Даты

Типы для хранения дат отличаются друг от друга очень сильно, в первую очередь по решаемой задаче. Нам надо хранить день без конкретного времени? Это тип date. Нужно конкретный момент времени, тогда timestamp. Просто время без даты? Тогда time.

Имя

Описание

Наименьшее значение

Наибольшее значение

Точность

timestamp

дата и время (без часового пояса)

4713 до н. э.

294276 н. э.

1 микросекунда

date

дата (без времени суток)

4713 до н. э.

5874897 н. э.

1 день

time

время суток (без даты)

00:00:00

24:00:00

1 микросекунда

Пример создания таблицы с такими типами:

CREATE TABLE users (
    id bigint,
    username VARCHAR(50),
    birthday DATE,
    updated_at TIMESTAMP,
    created_at TIMESTAMP
);

INSERT INTO users (id, username, birthday, created_at, updated_at) VALUES (1, 'Duncan3', '2022-05-25', '2022-06-14 18:31:05.296', '2022-06-14 18:31:05.296');
INSERT INTO users (id, username, birthday, created_at, updated_at) VALUES (2, 'Jennyfer', '2022-01-13', '2022-06-14 02:04:13.104', '2022-06-14 02:04:13.104');

View On DB Fiddle

Хорошей практикой считается добавление и заполнение полей created_at и updated_at в каждую таблицу базы данных. С их помощью всегда можно узнать, когда запись создалась и обновилась.

Значения даты и времени принимаются практически в любом известном формате. Вот несколько примеров того, как можно задавать дату:

Пример

Описание

1999-01-08

ISO 8601 (рекомендуемый формат)

January 8, 1999

Логический тип

Содержит всего два значения: true и false. Этот тип используется для флагов:

Имя	Описание
boolean	true или false (истина или ложь)

Имя

Описание

boolean

true или false (истина или ложь)

Пример создания таблицы с такими типами:

CREATE TABLE users (
    id bigint,
    username VARCHAR(50),
    email_confirmed BOOLEAN,
    email VARCHAR(255)
);

INSERT INTO users (id, username, email, email_confirmed) VALUES (1, 'Duncan3', 'duncan3@example.com', TRUE);
INSERT INTO users (id, username, email, email_confirmed) VALUES (2, 'Jenny', 'jenny@example.com', FALSE);

View On DB Fiddle

Состояние «true» может задаваться следующими значениями:

TRUE
't'
'true'
'y'
'yes'
'on'
'1'

Для состояния «false» можно использовать следующие варианты:

FALSE
'f'
'false'
'n'
'no'
'off'
'0'

Недопустимое значение в колонке boolean не будет сохранено

INSERT INTO users (id, username, email, email_confirmed) VALUES (1, 'Duncan3', 'duncan3@example.com', 1);
Schema Error: error: column "email_confirmed" is of type boolean but expression is of type integer

NULL

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

CREATE TABLE topics (
   user_id integer,
   body text
);

INSERT INTO topics (user_id, body) VALUES
(1, 'Классный курс!'),
(2, NULL);

View On DB Fiddle

Воспользуемся изученными типами данных, чтобы создать таблицу сотрудников

CREATE TABLE users (
    id bigint,
    username VARCHAR(50),
    birthday DATE,
    email_confirmed BOOLEAN,
    email VARCHAR(255),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TABLE topics (
    id bigint PRIMARY KEY,
    user_id bigint,
    title varchar(255),
    body text,
    updated_at TIMESTAMP,
    created_at TIMESTAMP
);

CREATE TABLE courses (
    id bigint,
    name varchar(255),
    body text,
    updated_at TIMESTAMP,
    created_at TIMESTAMP
);

Выводы

В этом уроке мы познакомились с типами данных в таблице. Повторим ключевые моменты:

В каждом столбце должны храниться однородные данные. Это необходимо для удобства и безопасности работы с данными
Типы данных в SQL накладывают ограничения на хранящиеся данные и операции, которые можно с ними совершать
Типы данных указываются при создании таблицы
Попытка вставить данные неподходящего типа или значения в колонку приведет к ошибке
Для строк используются такие типы данных как varchar и text
Для строк необходимо использовать одинарные кавычки
Для чисел используются типы integer, bigint, real
Для даты и времени используют timestamp, date, time
Для логических значений используют тип boolean. Эти значения чаще всего бывают в колонках-флагах
Когда у поля нет конкретного значения, используют специальное значение NULL

Самостоятельная работа

Немного попрактикуемся с созданием таблицы.

Структура students:

id - идентификатор студента, целое число
first_name - имя студента, строка
last_name - фамилия студента, строка
email - электронная почта, строка
bio - текст о студенте. Может содержать много текста
is_studying - текущий статус студента, учится ли он или нет
updated_at - дата обновления записи
created_at - дата создания записи

На DBFiddle мы подготовили часть запроса. Впишите между комментариями BEGIN и END запрос на создание таблицы и нажмите Run, чтобы проверить результат.

Нажмите, чтобы увидеть ответ

CREATE TABLE students (
    id bigint,
    first_name varchar(255),
    last_name varchar(255),
    email varchar(255),
    bio text,
    is_studying boolean,
    updated_at date,
    created_at date
);