SQL Базовый №3. Типы данных

Файлы к уроку:

Для спонсоров Boosty

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

create table albums (
  date_release date,
  album_name text
);

В различных СУБД и в PostgreSQL в том числе много типов данных, но чаще всего мы сталкиваемся с данными следующих категорий:

Characters — Любой знак или символ
Numbers — Целые и дробные числа
Dates and times — Дата и время

Строковые типы данных

Строковые типы данных подходят для любых комбинаций букв, цифр, символов.

char(n). Строка фиксированной длины. Все значения этого столбца состоят из одинакового количества символов, которое указано в скобках. Если попытаться импортировать более короткое значение, то недостаток будет заполнен пробелами. Этот тип данных является стандартным SQL типом.
varchar(n). Строка, максимальная длина которой указана в скобках. Длина строки может быть любой в пределах n. Этот тип данных является стандартным SQL типом.
text. Строка неограниченной длины. Этот тип данных не является стандартным SQL типом.

Создадим таблицу из 3 столбцов. По одному столбцу для каждого из этих типов данных. Добавим данные в эту таблицу.

-- Создать таблицу
create table string_types_sample (
  char_col char(12),
  varchar_col varchar(12),
  text_col text
);

-- Импорт данных в созданную таблицу
insert into string_types_sample
values
  ('dio', 'dio', 'dio'),
  ('acdc', 'acdc', 'acdc')

-- Экспорт таблицы в CSV
copy string_types_sample to 'c:\Users\user\Desktop\sql_training\string_types_sample.csv'
with (format csv, header, delimiter ',')

Чтобы сохранить данные в файл нужно:

Создать папку на рабочем столе
Правый клик по папке — Свойства — Безопасность — Изменить — Добавить — Назвать группу «Все» — Разрешить все

Откройте сохраненный csv файл и внимательно изучите данные.

Числовые типы данных

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

Целые числа

Integer — это целое число, которое может быть положительным или отрицательным. В стандартном SQL есть 3 типа данных для хранения целых чисел: smallint, integer, bigint. Разница между этими типами данных заключается в максимальном значении, которое они могут хранить.

Тип данных	Место в памяти	Диапазон значений
smallint	2 bytes	-32768..+32767
integer	4 bytes	-2147483648..+2147483647
bigint	8 bytes	-9223372036854775808..+9223372036854775807

Тип bigint справится с любыми целыми числами, но потребляет больше всех места. Если вы уверены, что для вашей задачи будет достаточно диапазона типа integer, то лучше использовать его, чтобы сохранить тип данных.

Если числовые значения столбца жестко ограничены, то подойдет тип данных smallint. Примеры ограниченных значений: номер дня недели, год, номер дома, номер квартиры.

Автоматически увеличивающиеся целые числа

Если вы создаете столбцы с типами данных smallserial, serial, bigserial, то значения в этих столбцах будут увеличиваться при добавлении новых строк.

Чтобы воспользоваться столбцом типа serial нужно при создании таблицы указать название типа рядом с названием столбца. Пример создания таблицы со столбцом типа serial:

create table js_albums (
  id serial,
  release_year smallint,
  album_name text
)

Добавим одну строку в таблицу:

insert into js_albums
values
  (default, 1974, 'Rocka Rolla')

Добавим еще одну строку:

insert into js_albums
values
  (default, 1976, 'Sad Wings of Destiny')

Теперь посмотрите как выглядит получившаяся таблица. Особенно обратите внимание на первый столбец под названием «id».

Разница между serial типами тоже заключается в объеме места и диапазоне значений.

Тип данных	Место в памяти	Диапазон значений
smallserial	2 bytes	1..32767
serial	4 bytes	1..2147483647
bigserial	8 bytes	1..9223372036854775807

Десятичные числа

В SQL базах данных есть типы данных с фиксированной точкой и плавающей.

Числа с фиксированной точкой

Числа с фиксированной точкой также называются числами с произвольной точностью. Для хранения таких числе используется тип данных numeric(precision, scale). В параметре precision указывается максимальное количество цифр вообще, а в параметре scale максимальное количество чисел справа. Если не задать значение scale, то для scale будет автоматически назначено значение 0, т. е. число станет целым или integer. Если не указать оба параметра, то автоматически будет назначена максимальная точность.

Если столбец вашей таблицы хранит, например, курс доллара с двумя десятичными знаками, то вам скорее всего подойдет numeric(5, 2).

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

Два типа данных для числа с плавающей точкой — это real и double precision. Разница в объеме данных, который может храниться значениях. Тип real позволяет хранить до 7 десятичных знаков. Тип double precision позволяет хранить до 15 десятичных знаков.

Использование чисел с плавающей точкой и фиксированной

Тип данных	Место в памяти	Тип точки	Диапазон значений
numeric, decimal	variable	Фиксированная	До 131072 до точки, до 16383 после точки
real	4 bytes	Плавающая точка	Точность до 7 десятичных знаков
double precision	8 bytes	Плавающая точка	Точность до 15 десятичных знаков

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

create table float_types_sample (
  numeric_col numeric(10, 5),
  real_col real,
  double_col double precision
)

Импортируем данные в эту таблицу.

insert into float_types_sample
values
  (1.1, 1.1, 1.1),
  (1.12345, 1.12345, 1.12345),
  (1.1234567899, 1.1234567899, 1.1234567899)
  (1.123456789911111, 1.123456789911111, 1.123456789911111)

Выполняем экспорт в CSV.

copy float_types_sample
to 'c:\Users\user\Desktop\sql_training\float_types_sample.csv'
with (format csv, header, delimiter ',')

Откройте созданный CSV и изучите его.

Точность при вычислениях при использовании плавающих точек

Способ, которым компьютер хранит и обрабатывает числа с плавающей точкой может привести к математическим ошибкам. Умножим значение из первого и второго столбца на 10 миллионов.

select
  numeric_col * 10000000 as fixed_col,
  real_col * 10000000 as float_col
from float_types_sample
limit 1

Мы одно и то же число умножили на 10 миллионов, но получили неравные результаты.

Дата и время

В PostgreSQL есть 4 основных типа данных для хранения даты и времени.

Тип данных	Место в памяти	Описание	Диапазон
timestamp	8 bytes	Дата и время	С 4713 года до н.э. по 294276 год н.э.
date	4 bytes	Дата без времени	С 4713 года до н.э. по 5874897 год нашей эры
time	8 bytes	Время без даты	00:00:00 — 24:00:00
interval	16 bytes	Интервал времени	+/− 178,000,000 лет

timestamp хранит дату и время. Довольно часто при создании столбца нужно добавить ключевые слова with time zone, чтобы указать часовой пояс. В стандартном SQL для создания столбца с указанием часового пояса используется команда timestamp with time zone. В PostgreSQL помимо стандартного способа можно воспользоваться ключевым словом timestamptz.
date хранит только дату.
time хранит только время. Можно добавить часовой пояс добавив with time zone.
interval. Не хранит значений даты и времени. Показывает сколько времени прошло. Например, это может быть 5 days или 13 hours.

Создадим таблицу с двумя столбцами. В первом будут значения с типом данных timestamptz, а во втором interval.

-- Создание таблицы
create table dt_types (
timestamp_col timestamp with time zone,
interval_col interval
);

-- Импорт значений
insert into dt_types
values
  ('2021-10-31 15:15 +3', '15 days'),
  ('2022-10-31 00:10 MSK', '30 years')

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

select *
from pg_timezone_names
where name ilike '%moscow%'

Использование типа interval в вычислениях

Тип данных interval полезен, когда нужно сделать вычисления с использованием значений date, time, datetime. Допустим в одном столбце находится дата. Во втором столбце находится интервал, который нужно прибавить. В третьем столбце нам нужно получить новую дата, которая равна старой дате с прибавлением интервала.

select
  timestamp_col,
  interval_col,
  timestamp_col + interval_col as new_timestamp
from dt_types

Прочие типы данных

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

Boolean
Географические типы данных
XML и JSON
и многие другие

Преобразование типов данных

Иногда нужно преобразовать один тип данных в другой. Например, иногда число, сохраненное как текст нужно преобразовать в число. Для преобразования типов данных используется функция CAST().

-- Преобразовать дату в текст
select
  timestamp_col,
  cast(timestamp_col as varchar(10)) as new_timestamp_col
from dt_types

-- Преобразовать десятичное число в целое
-- Преобразовать десятичное число в текст
select
  numeric_col,
  cast(numeric_col as integer) as new_integer_col,
  cast(numeric_col as varchar(6)) as new_varchar_col
from float_types_sample

-- Попытка преобразовать буквы в число
-- Вернется ошибка
select
  cast(char_col as integer)
from string_types_sample

В PostgreSQL вместо функции CAST() можно воспользоваться сокращенной записью. Например, cast(timestamp_col as varchar(10)) в сокращенном виде выглядит так: timestamp_col::varchar(10).

предыдущая запись

Power Query Разное. Скученные данные 4

следующая запись