SQL Data Types for MySQL, SQL Server, and MS Access
The data type of a column defines what value the column can hold: integer, character, money, date and time, binary, and so on.
SQL Data Types
Each column in a database table is required to have a name and a data type.
An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.
Note: Data types might have different names in different database. And even if the name is the same, the size and other details may be different! Always check the documentation!
MySQL Data Types (Version 8.0)
In MySQL there are three main data types: string, numeric, and date and time.
String Data Types
| Data type | Description |
|---|---|
| CHAR(size) | A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters — can be from 0 to 255. Default is 1 |
| VARCHAR(size) | A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum string length in characters — can be from 0 to 65535 |
| BINARY(size) | Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1 |
| VARBINARY(size) | Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes. |
| TINYBLOB | For BLOBs (Binary Large Objects). Max length: 255 bytes |
| TINYTEXT | Holds a string with a maximum length of 255 characters |
| TEXT(size) | Holds a string with a maximum length of 65,535 bytes |
| BLOB(size) | For BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data |
| MEDIUMTEXT | Holds a string with a maximum length of 16,777,215 characters |
| MEDIUMBLOB | For BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data |
| LONGTEXT | Holds a string with a maximum length of 4,294,967,295 characters |
| LONGBLOB | For BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data |
| ENUM(val1, val2, val3, . ) | A string object that can have only one value, chosen from a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them |
| SET(val1, val2, val3, . ) | A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list |
Numeric Data Types
| Data type | Description |
|---|---|
| BIT(size) | A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The default value for size is 1. |
| TINYINT(size) | A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255) |
| BOOL | Zero is considered as false, nonzero values are considered as true. |
| BOOLEAN | Equal to BOOL |
| SMALLINT(size) | A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255) |
| MEDIUMINT(size) | A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255) |
| INT(size) | A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255) |
| INTEGER(size) | Equal to INT(size) |
| BIGINT(size) | A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255) |
| FLOAT(size, d) | A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions |
| FLOAT(p) | A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE() |
| DOUBLE(size, d) | A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter |
| DOUBLE PRECISION(size, d) | |
| DECIMAL(size, d) | An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0. |
| DEC(size, d) | Equal to DECIMAL(size,d) |
Note: All the numeric data types may have an extra option: UNSIGNED or ZEROFILL. If you add the UNSIGNED option, MySQL disallows negative values for the column. If you add the ZEROFILL option, MySQL automatically also adds the UNSIGNED attribute to the column.
Date and Time Data Types
| Data type | Description |
|---|---|
| DATE | A date. Format: YYYY-MM-DD. The supported range is from ‘1000-01-01’ to ‘9999-12-31’ |
| DATETIME(fsp) | A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time |
| TIMESTAMP(fsp) | A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition |
| TIME(fsp) | A time. Format: hh:mm:ss. The supported range is from ‘-838:59:59’ to ‘838:59:59’ |
| YEAR | A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000. MySQL 8.0 does not support year in two-digit format. |
SQL Server Data Types
String Data Types
| Data type | Description | Max size | Storage |
|---|---|---|---|
| char(n) | Fixed width character string | 8,000 characters | Defined width |
| varchar(n) | Variable width character string | 8,000 characters | 2 bytes + number of chars |
| varchar(max) | Variable width character string | 1,073,741,824 characters | 2 bytes + number of chars |
| text | Variable width character string | 2GB of text data | 4 bytes + number of chars |
| nchar | Fixed width Unicode string | 4,000 characters | Defined width x 2 |
| nvarchar | Variable width Unicode string | 4,000 characters | |
| nvarchar(max) | Variable width Unicode string | 536,870,912 characters | |
| ntext | Variable width Unicode string | 2GB of text data | |
| binary(n) | Fixed width binary string | 8,000 bytes | |
| varbinary | Variable width binary string | 8,000 bytes | |
| varbinary(max) | Variable width binary string | 2GB | |
| image | Variable width binary string | 2GB |
Numeric Data Types
| Data type | Description | Storage |
|---|---|---|
| bit | Integer that can be 0, 1, or NULL | |
| tinyint | Allows whole numbers from 0 to 255 | 1 byte |
| smallint | Allows whole numbers between -32,768 and 32,767 | 2 bytes |
| int | Allows whole numbers between -2,147,483,648 and 2,147,483,647 | 4 bytes |
| bigint | Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 | 8 bytes |
| decimal(p,s) | Fixed precision and scale numbers. |
Allows numbers from -10^38 +1 to 10^38 –1.
The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.
The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
Allows numbers from -10^38 +1 to 10^38 –1.
The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.
The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.
Что такое integer в sql
Числовые типы включают двух-, четырёх- и восьмибайтные целые, четырёх- и восьмибайтные числа с плавающей точкой, а также десятичные числа с задаваемой точностью. Все эти типы перечислены в Таблице 8.2.
Таблица 8.2. Числовые типы
| Имя | Размер | Описание | Диапазон |
|---|---|---|---|
| smallint | 2 байта | целое в небольшом диапазоне | -32768 .. +32767 |
| integer | 4 байта | типичный выбор для целых чисел | -2147483648 .. +2147483647 |
| bigint | 8 байт | целое в большом диапазоне | -9223372036854775808 .. 9223372036854775807 |
| decimal | переменный | вещественное число с указанной точностью | до 131072 цифр до десятичной точки и до 16383 — после |
| numeric | переменный | вещественное число с указанной точностью | до 131072 цифр до десятичной точки и до 16383 — после |
| real | 4 байта | вещественное число с переменной точностью | точность в пределах 6 десятичных цифр |
| double precision | 8 байт | вещественное число с переменной точностью | точность в пределах 15 десятичных цифр |
| smallserial | 2 байта | небольшое целое с автоувеличением | 1 .. 32767 |
| serial | 4 байта | целое с автоувеличением | 1 .. 2147483647 |
| bigserial | 8 байт | большое целое с автоувеличением | 1 .. 9223372036854775807 |
Синтаксис констант числовых типов описан в Подразделе 4.1.2. Для этих типов определён полный набор соответствующих арифметических операторов и функций. За дополнительными сведениями обратитесь к Главе 9. Подробнее эти типы описаны в следующих разделах.
8.1.1. Целочисленные типы
Типы smallint , integer и bigint хранят целые числа, то есть числа без дробной части, имеющие разные допустимые диапазоны. Попытка сохранить значение, выходящее за рамки диапазона, приведёт к ошибке.
Чаще всего используется тип integer , как наиболее сбалансированный выбор ширины диапазона, размера и быстродействия. Тип smallint обычно применяется, только когда крайне важно уменьшить размер данных на диске. Тип bigint предназначен для тех случаев, когда числа не умещаются в диапазон типа integer .
8.1.2. Числа с произвольной точностью
Тип numeric позволяет хранить числа с очень большим количеством цифр. Он особенно рекомендуется для хранения денежных сумм и других величин, где важна точность. Вычисления с типом numeric дают точные результаты, где это возможно, например, при сложении, вычитании и умножении. Однако операции со значениями numeric выполняются гораздо медленнее, чем с целыми числами или с типами с плавающей точкой, описанными в следующем разделе.
Ниже мы используем следующие термины: масштаб значения numeric определяет количество десятичных цифр в дробной части, справа от десятичной точки, а точность — общее количество значимых цифр в числе, т. е. количество цифр по обе стороны десятичной точки. Например, число 23.5141 имеет точность 6 и масштаб 4. Целочисленные значения можно считать числами с масштабом 0.
Для столбца типа numeric можно настроить и максимальную точность, и максимальный масштаб. Столбец типа numeric объявляется следующим образом:
Точность должна быть положительной, а масштаб может быть положительным или отрицательным (см. ниже). Альтернативный вариант
устанавливает масштаб 0. Форма:
без указания точности и масштаба создаёт столбец типа « неограниченное число » , в котором можно сохранять числовые значения любой длины до предела, обусловленного реализацией. В столбце этого типа входные значения не будут приводиться к какому-либо масштабу, тогда как в столбцах numeric с явно заданным масштабом значения подгоняются под этот масштаб. (Стандарт SQL утверждает, что по умолчанию должен устанавливаться масштаб 0, т. е. значения должны приводиться к целым числам. Однако мы считаем это не очень полезным. Если для вас важна переносимость, всегда указывайте точность и масштаб явно.)
Примечание
Максимальная точность, которую можно указать в объявлении типа numeric , равна 1000. Для столбцов типа « неограниченный numeric » существуют пределы, описанные в Таблице 8.2.
Если масштаб значения, которое нужно сохранить, превышает объявленный масштаб столбца, система округлит его до заданного количества цифр после точки. Если же после этого количество цифр слева в сумме с масштабом превысит объявленную точность, произойдёт ошибка. Например, столбец, объявленный как
, будет содержать значения, округлённые до одного десятичного знака, от -99.9 до 99.9 включительно.
Начиная с PostgreSQL 15, столбец типа numeric можно объявить с отрицательным масштабом. В этом случае значения будут округляться до позиции слева от десятичной точки. При этом точность так же задаёт максимальное число цифр, без учёта округления. Таким образом, в столбце, объявленном как
, число будет округляться до ближайшей тысячи, и в нём можно будет хранить значения от -99000 до 99000 включительно. Также вполне возможно задать масштаб, превышающий объявленную точность. Такой столбец сможет содержать только дробные значения, в которых количество нулей сразу после точки должно равняться разности масштаба и точности. Например, столбец, объявленный как
, будет округлять значения до пяти знаков после точки и сможет хранить значения от -0.00999 до 0.00999 включительно.
Примечание
Числовые значения физически хранятся без каких-либо дополняющих нулей слева или справа. Таким образом, объявляемые точность и масштаб столбца определяют максимальный, а не фиксированный размер хранения. (В этом смысле тип numeric больше похож на тип varchar( n ) , чем на char( n ) .) Действительный размер хранения такого значения складывается из двух байт для каждой группы из четырёх цифр и дополнительных трёх-восьми байт.
В дополнение к обычным числовым значениям тип numeric принимает следующие специальные значения:
Они представляют особые значения, описанные в IEEE 754, соответственно « бесконечность » , « минус бесконечность » и « не число » . Записывая эти значения в виде констант в команде SQL, их нужно заключать в апострофы, например так: UPDATE table SET x = ‘-Infinity’ . Регистр символов в этих строках не важен. В качестве альтернативы значения бесконечности могут быть записаны как inf и -inf .
Значения бесконечности соответствуют ожиданиям с точки зрения математики. Например, Infinity плюс любое конечное значение равно Infinity , как и Infinity плюс Infinity ; но Infinity минус Infinity даёт NaN (не число), потому что в результате получается неопределённость. Обратите внимание, что бесконечность может быть сохранена только в столбце типа « неограниченный numeric » , потому что она теоретически превышает любой конечный предел точности.
Значение NaN (не число) используется для представления неопределённых результатов вычислений. Вообще, любая операция с NaN выдаёт в результате тоже NaN . Единственное исключение составляют операции, которые дают один и тот же результат для любого числового значения, конечного или бесконечного — этот результат будет выдаваться и для NaN . (Пример этого принципа: результатом возведения NaN в нулевую степень будет единица.)
Примечание
В большинстве реализаций « не число » ( NaN ) считается не равным любому другому значению (в том числе и самому NaN ). Чтобы значения numeric можно было сортировать и использовать в древовидных индексах, Postgres Pro считает, что значения NaN равны друг другу и при этом больше любых числовых значений (не NaN ).
Типы decimal и numeric равнозначны. Оба эти типа описаны в стандарте SQL .
При округлении значений тип numeric выдаёт число, большее по модулю, тогда как (на большинстве платформ) типы real и double precision выдают ближайшее чётное число. Например:
8.1.3. Типы с плавающей точкой
Типы данных real и double precision хранят приближённые числовые значения с переменной точностью. На всех поддерживаемых в настоящее время платформах эти типы реализуют стандарт IEEE 754 для двоичной арифметики с плавающей точкой (с одинарной и двойной точностью соответственно), в той мере, в какой его поддерживают процессор, операционная система и компилятор.
Неточность здесь выражается в том, что некоторые значения, которые нельзя преобразовать во внутренний формат, сохраняются приближённо, так что полученное значение может несколько отличаться от записанного. Управление подобными ошибками и их распространение в процессе вычислений является предметом изучения целого раздела математики и компьютерной науки, и здесь не рассматривается. Мы отметим только следующее:
Если вам нужна точность при хранении и вычислениях (например, для денежных сумм), используйте вместо этого тип numeric .
Если вы хотите выполнять с этими типами сложные вычисления, имеющие большую важность, тщательно изучите реализацию операций в вашей среде и особенно поведение в крайних случаях (бесконечность, антипереполнение).
На всех поддерживаемых сейчас платформах тип real может сохранить значения примерно от 1E-37 до 1E+37 с точностью не меньше 6 десятичных цифр. Тип double precision предлагает значения в диапазоне приблизительно от 1E-307 до 1E+308 и с точностью не меньше 15 цифр. Попытка сохранить слишком большие или слишком маленькие значения приведёт к ошибке. Если точность вводимого числа слишком велика, оно будет округлено. При попытке сохранить число, близкое к 0, но непредставимое как отличное от 0, произойдёт ошибка антипереполнения.
По умолчанию числа с плавающей точкой выводятся в текстовом виде в кратчайшем точном десятичном представлении; выводимое десятичное значение оказывается более близким к изначальному двоичному числу, чем любое другое значение, представимое с той же двоичной точностью. (Однако выводимое значение в текущей реализации никогда не находится точно посередине между двумя представимыми двоичными значениями, во избежание распространённой ошибки с функциями ввода, не учитывающими корректно правило округления до ближайшего чётного.) Выводимое значение может занимать не больше 17 значащих десятичных цифр для типа float8 и не больше 9 цифр для типа float4 .
Примечание
Преобразование в кратчайший точный вид производится гораздо быстрее, чем в традиционное представление с округлением.
Для совместимости с результатами, выдаваемыми старыми версиями Postgres Pro , и уменьшения точности выводимых чисел, когда это требуется, в параметре extra_float_digits можно выбрать также вариант округлённого десятичного вывода. Со значением 0 восстанавливается действовавшее ранее по умолчанию округление числа до 6 (для типа float4 ) или 15 (для float8 ) значащих десятичных цифр. При отрицательных значениях число значащих цифр уменьшается дополнительно; например, при -2 результат будет округлён до 4 или 13 цифр, соответственно.
При любом значении extra_float_digits, большем 0, выбирается кратчайшее точное представление.
Примечание
Приложения, которым были нужны точные числовые значения, раньше задавали для параметра extra_float_digits значение 3, чтобы получить их. Они могут продолжать использовать это значение для максимальной совместимости с разными версиями.
В дополнение к обычным числовым значениям типы с плавающей точкой принимают следующие специальные значения:
Они представляют особые значения, описанные в IEEE 754, соответственно « бесконечность » , « минус бесконечность » и « не число » . Записывая эти значения в виде констант в команде SQL, их нужно заключать в апострофы, например так: UPDATE table SET x = ‘-Infinity’ . Регистр символов в этих строках не важен. В качестве альтернативы значения бесконечности могут быть записаны как inf и -inf .
Примечание
Согласно IEEE 754, NaN не должно считаться равным любому другому значению с плавающей точкой (в том числе и самому NaN ). Чтобы значения с плавающей точкой можно было сортировать и использовать в древовидных индексах, Postgres Pro считает, что значения NaN равны друг другу, и при этом больше любых числовых значений (не NaN ).
Postgres Pro также поддерживает форматы float и float( p ) , оговорённые в стандарте SQL, для указания неточных числовых типов. Здесь p определяет минимально допустимую точность в двоичных цифрах. Postgres Pro воспринимает запись от float(1) до float(24) как выбор типа real , а запись от float(25) до float(53) как выбор типа double precision . Значения p вне допустимого диапазона вызывают ошибку. Если float указывается без точности, подразумевается тип double precision .
8.1.4. Последовательные типы
Примечание
В этом разделе описывается специфичный для Postgres Pro способ создания столбца с автоувеличением. Другой способ, соответствующий стандарту SQL, заключается в использовании столбцов идентификации и рассматривается в описании CREATE TABLE .
Типы данных smallserial , serial и bigserial не являются настоящими типами, а представляют собой просто удобное средство для создания столбцов с уникальными идентификаторами (подобное свойству AUTO_INCREMENT в некоторых СУБД). В текущей реализации запись:
равнозначна следующим командам:
То есть при определении такого типа создаётся целочисленный столбец со значением по умолчанию, извлекаемым из генератора последовательности. Чтобы в столбец нельзя было вставить NULL, в его определение добавляется ограничение NOT NULL . (Во многих случаях также имеет смысл добавить для этого столбца ограничения UNIQUE или PRIMARY KEY для защиты от ошибочного добавления дублирующихся значений, но автоматически это не происходит.) Последняя команда определяет, что последовательность « принадлежит » столбцу, так что она будет удалена при удалении столбца или таблицы.
Примечание
Так как типы smallserial , serial и bigserial реализованы через последовательности, в числовом ряду значений столбца могут образовываться пропуски (или «дыры»), даже если никакие строки не удалялись. Значение, выделенное из последовательности, считается «задействованным», даже если строку с этим значением не удалось вставить в таблицу. Это может произойти, например, при откате транзакции, добавляющей данные. См. описание nextval() в Разделе 9.17.
Чтобы вставить в столбец serial следующее значение последовательности, ему нужно присвоить значение по умолчанию. Это можно сделать, либо исключив его из списка столбцов в операторе INSERT , либо с помощью ключевого слова DEFAULT .
Имена типов serial и serial4 равнозначны: они создают столбцы integer . Так же являются синонимами имена bigserial и serial8 , но они создают столбцы bigint . Тип bigserial следует использовать, если за всё время жизни таблицы планируется использовать больше чем 2 31 значений. И наконец, синонимами являются имена типов smallserial и serial2 , но они создают столбец smallint .
Последовательность, созданная для столбца serial , автоматически удаляется при удалении связанного столбца. Последовательность можно удалить и отдельно от столбца, но при этом также будет удалено определение значения по умолчанию.
SQL Базовый №3. Типы данных
В столбце базы данных может находиться только 1 тип данных, который был указан при создании таблицы в выражении CREATE TABLE. После указания имени столбца нужно указать тип данных этого столбца.
В различных СУБД и в PostgreSQL в том числе много типов данных, но чаще всего мы сталкиваемся с данными следующих категорий:
- Characters — Любой знак или символ
- Numbers — Целые и дробные числа
- Dates and times — Дата и время
Строковые типы данных
Строковые типы данных подходят для любых комбинаций букв, цифр, символов.
- char(n). Строка фиксированной длины. Все значения этого столбца состоят из одинакового количества символов, которое указано в скобках. Если попытаться импортировать более короткое значение, то недостаток будет заполнен пробелами. Этот тип данных является стандартным SQL типом.
- varchar(n). Строка, максимальная длина которой указана в скобках. Длина строки может быть любой в пределах n. Этот тип данных является стандартным SQL типом.
- text. Строка неограниченной длины. Этот тип данных не является стандартным SQL типом.
Создадим таблицу из 3 столбцов. По одному столбцу для каждого из этих типов данных. Добавим данные в эту таблицу.
Чтобы сохранить данные в файл нужно:
- Создать папку на рабочем столе
- Правый клик по папке — Свойства — Безопасность — Изменить — Добавить — Назвать группу «Все» — Разрешить все
Откройте сохраненный 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:
Добавим одну строку в таблицу:
Добавим еще одну строку:
Теперь посмотрите как выглядит получившаяся таблица. Особенно обратите внимание на первый столбец под названием «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 десятичных знаков |
Создадим таблицу с тремя столбцами. По одному столбцу для каждого из этих трех типов данных.
Импортируем данные в эту таблицу.
Выполняем экспорт в CSV.
Откройте созданный CSV и изучите его.
Точность при вычислениях при использовании плавающих точек
Способ, которым компьютер хранит и обрабатывает числа с плавающей точкой может привести к математическим ошибкам. Умножим значение из первого и второго столбца на 10 миллионов.
Мы одно и то же число умножили на 10 миллионов, но получили неравные результаты.
Дата и время
В PostgreSQL есть 4 основных типа данных для хранения даты и времени.
- timestamp хранит дату и время. Довольно часто при создании столбца нужно добавить ключевые слова with time zone, чтобы указать часовой пояс. В стандартном SQL для создания столбца с указанием часового пояса используется команда timestamp with time zone. В PostgreSQL помимо стандартного способа можно воспользоваться ключевым словом timestamptz.
- date хранит только дату.
- time хранит только время. Можно добавить часовой пояс добавив with time zone.
- interval. Не хранит значений даты и времени. Показывает сколько времени прошло. Например, это может быть 5 days или 13 hours.
Создадим таблицу с двумя столбцами. В первом будут значения с типом данных timestamptz, а во втором interval.
В PostgreSQL есть специальное VIEW, в котором хранятся все способы указания часовых поясов.
Использование типа interval в вычислениях
Тип данных interval полезен, когда нужно сделать вычисления с использованием значений date, time, datetime. Допустим в одном столбце находится дата. Во втором столбце находится интервал, который нужно прибавить. В третьем столбце нам нужно получить новую дата, которая равна старой дате с прибавлением интервала.
Прочие типы данных
Типы данных для работы с числами, датами и текстом используются чаще всего. Тем не менее этих типов данных может быть иногда недостаточно. PostgreSQL поддерживает очень много типов данных, например:
- Boolean
- Географические типы данных
- XML и JSON
- и многие другие
Преобразование типов данных
Иногда нужно преобразовать один тип данных в другой. Например, иногда число, сохраненное как текст нужно преобразовать в число. Для преобразования типов данных используется функция CAST().
В PostgreSQL вместо функции CAST() можно воспользоваться сокращенной записью. Например, cast(timestamp_col as varchar(10)) в сокращенном виде выглядит так: timestamp_col::varchar(10).