Теория: Таблицы

В этом уроке поближе познакомимся с таблицами в 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.

• 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. Какой конкретно указывать тип, зависит от потенциального потолка значения. Ниже указаны диапазоны, допустимые в рамках этих типов:

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

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.

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

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 в каждую таблицу базы данных. С их помощью всегда можно узнать, когда запись создалась и обновилась.

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

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

Содержит всего два значения: 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