Serial postgresql что это

Serial postgresql что это

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

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

serial : представляет автоинкрементирующееся числовое значение, которое занимает 4 байта и может хранить числа от 1 до 2147483647. Значение данного типа образуется путем автоинкремента значения предыдущей строки. Поэтому, как правило, данный тип используется для определения идентификаторов строки.

smallserial : представляет автоинкрементирующееся числовое значение, которое занимает 2 байта и может хранить числа от 1 до 32767. Аналог типа serial для небольших чисел.

bigserial : представляет автоинкрементирующееся числовое значение, которое занимает 8 байт и может хранить числа от 1 до 9223372036854775807. Аналог типа serial для больших чисел.

smallint : хранит числа от -32768 до +32767. Занимает 2 байта. Имеет псевдоним int2 .

integer : хранит числа от -2147483648 до +2147483647. Занимает 4 байта. Имеет псевдонимы int и int4 .

bigint : хранит числа от -9223372036854775808 до +9223372036854775807. Занимает 8 байт. Имеет псевдоним int8 .

numeric : хранит числа с фиксированной точностью, которые могут иметь до 131072 знаков в целой части и до 16383 знаков после запятой.

Данный тип может принимать два параметра precision и scale: numeric(precision, scale) .

Параметр precision указывает на максимальное количество цифр, которые может хранить число.

Параметр scale представляет максимальное количество цифр, которые может содержать число после запятой. Это значение должно находиться в диапазоне от 0 до значения параметра precision. По умолчанию оно равно 0.

Например, для числа 23.5141 precision равно 6, а scale — 4.

decimal : хранит числа с фиксированной точностью, которые могут иметь до 131072 знаков в целой части и до 16383 знаков в дробной части. То же самое, что и numeric .

real : хранит числа с плавающей точкой из диапазона от 1E-37 до 1E+37. Занимает 4 байта. Имеет псевдоним float4 .

double precision : хранит числа с плавающей точкой из диапазона от 1E-307 до 1E+308. Занимает 8 байт. Имеет псевдоним float8 .

Типы для работы с валютой (денежными единицами)

Для работы с денежными единицами определен тип money , который может принимать значения в диапазоне от -92233720368547758.08 до +92233720368547758.07 и занимает 8 байт.

Символьные типы

character(n) : представляет строку из фиксированного количества символов. С помощью параметра задается задается количество символов в строке. Имеет псевдоним char(n) .

character varying(n) : представляет строку из переменной длины. С помощью параметра задается задается максимальное количество символов в строке. Имеет псевдоним varchar(n) .

text : представляет текст произвольной длины.

Бинарные данные

Для хранения бинарных данных определен тип bytea . Он хранит данные в виде бинарных строк, которые представляют последовательность октетов или байт.

Типы для работы с датами и временем

timestamp : хранит дату и время. Занимает 8 байт. Для дат самое нижнее значение — 4713 г до н.э., самое верхнее значение — 294276 г н.э.

timestamp with time zone : то же самое, что и timestamp , только добавляет данные о часовом поясе.

date : представляет дату от 4713 г. до н.э. до 5874897 г н.э. Занимает 4 байта.

time : хранит время с точностью до 1 микросекунды без указания часового пояса. Принимает значения от 00:00:00 до 24:00:00. Занимает 8 байт.

time with time zone : хранит время с точностью до 1 микросекунды с указанием часового пояса. Принимает значения от 00:00:00+1459 до 24:00:00-1459. Занимает 12 байт.

interval : представляет временной интервал. Занимает 16 байт.

Распространенные форматы дат:

Month dd, yyyy — January 8, 1999

Распространенные форматы времени:

hh:mi am/pm — 1:21 pm

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

Тип boolean может хранить одно из двух значений: true или false.

Вместо true можно указывать следующие значения: TRUE, ‘t’, ‘true’, ‘y’, ‘yes’, ‘on’, ‘1’.

Вместо false можно указывать следующие значения: FALSE, ‘f’, ‘false’, ‘n’, ‘no’, ‘off’, ‘0’.

Типы для представления интернет-адресов

cidr : интернет-адрес в формате IPv4 и IPv6. Например, 192.168.0.1 . Занимает от 7 до 19 байт.

inet : интернет-адрес в формате cidr/y , где cidr это адрес в формате IPv4 или IPv6, а /y — количество бит в адресе (если этот параметр не указан, то используется 34 для IPv4, 128 для IPv6). Например, 192.168.0.1/24 или 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128 . Занимает от 7 до 19 байт.

macaddr : хранит MAC-адрес. Занимает 6 байт.

macaddr8 : хранит MAC-адрес в формате EUI-64. Занимает 8 байт.

Геометрические типы

point : представляет точку на плоскости в формате (x,y) . Занимает 16 байт.

line : представляет линию неопределенной длины в формате . Занимает 32 байта.

lseg : представляет отрезок в формате ((x1,y1),(x2,y2)) . Занимает 32 байта.

box : представляет прямоугольник в формате ((x1,y1),(x2,y2)) . Занимает 32 байта.

path : представляет набор содиненных точек. В формате ((x1,y1). ) путь является закрытым (первая и последняя точка соединяются линией) и фактически представляет многоугольник. В формате [(x1,y1). ] путь является открытым Занимает 16+16n байт.

polygon : представляет многоугольник в формате ((x1,y1). ) . Занимает 40+16n байт.

circle : представляет окружность в формате <(x,y),r> . Занимает 24 байта.

Остальные типы данных

json : хранит данные json в текстовом виде.

jsonb : хранит данные json в бинарном формате.

uuid : хранит универсальный уникальный идентификатор (UUID), например, a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 . Занимает 32 байта.

Serial postgresql что это

PostgreSQL has a special kind of database object generator called SERIAL. It is used to generate a sequence of integers which are often used as the Primary key of a table.

When creating a table, this sequence of integers can be created as follows:

By assigning the SERIAL to the id column, PostgreSQL carries out the following:

• The initial step generates sequences of integers, where the next default value depends upon the value for the column.
• As SERIAL always generates a sequence of integers, it is important to set that no null value is added as an ID to any column. This is done by adding the NOT NULL constraint.
• Finally, it is needed to set the owner of the sequence for the ID column. These IDs are automatically deleted if the column or table is dropped.

PostgreSQL supports 3 serial pseudo-types namely SMALLSERIAL, SERIAL, and BIGSERIAL which has the following characteristics:

Example 1:
First, we will create a animals table with the id column as the SERIAL column as below:

Now while inserting the values to the table one can use anyone of the below commands:

So let’s add some rows to our animal table using the command below:

Now check if ids are auto-assigned to each row by using the below command:

Output:

Example 2:
First, we will create a fruits table with the id column as the SERIAL column as below:

Now while inserting the values to the table one can use anyone of the below commands:

So let’s add some rows to our fruits table using the command below:

Now check if ids are auto-assigned to each row by using the below command:

Serial postgresql что это

Числовые типы включают двух-, четырёх- и восьмибайтные целые, четырёх- и восьмибайтные числа с плавающей точкой, а также десятичные числа с задаваемой точностью. Все эти типы перечислены в Таблице 8.2.

Таблица 8.2. Числовые типы

Синтаксис констант числовых типов описан в Подразделе 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.

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

Числовые значения физически хранятся без каких-либо дополняющих нулей слева или справа. Таким образом, объявляемые точность и масштаб столбца определяют максимальный, а не фиксированный размер хранения. (В этом смысле тип 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 можно было сортировать и использовать в древовидных индексах, PostgreSQL считает, что значения 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 .

Примечание

Преобразование в кратчайший точный вид производится гораздо быстрее, чем в традиционное представление с округлением.

Для совместимости с результатами, выдаваемыми старыми версиями PostgreSQL , и уменьшения точности выводимых чисел, когда это требуется, в параметре 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 ). Чтобы значения с плавающей точкой можно было сортировать и использовать в древовидных индексах, PostgreSQL считает, что значения NaN равны друг другу, и при этом больше любых числовых значений (не NaN ).

PostgreSQL также поддерживает форматы float и float( p ) , оговорённые в стандарте SQL, для указания неточных числовых типов. Здесь p определяет минимально допустимую точность в двоичных цифрах. PostgreSQL воспринимает запись от float(1) до float(24) как выбор типа real , а запись от float(25) до float(53) как выбор типа double precision . Значения p вне допустимого диапазона вызывают ошибку. Если float указывается без точности, подразумевается тип double precision .

8.1.4. Последовательные типы

Примечание

В этом разделе описывается специфичный для PostgreSQL способ создания столбца с автоувеличением. Другой способ, соответствующий стандарту 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 , автоматически удаляется при удалении связанного столбца. Последовательность можно удалить и отдельно от столбца, но при этом также будет удалено определение значения по умолчанию.

Using PostgreSQL SERIAL To Create Auto-increment Column

Summary: in this tutorial, you will learn about the PostgreSQL SERIAL pseudo-type and how to use the SERIAL pseudo-type to define auto-increment columns in tables.

Introduction to the PostgreSQL SERIAL pseudo-type

In PostgreSQL, a sequence is a special kind of database object that generates a sequence of integers. A sequence is often used as the primary key column in a table.

When creating a new table, the sequence can be created through the SERIAL pseudo-type as follows:

By assigning the SERIAL pseudo-type to the id column, PostgreSQL performs the following:

• First, create a sequence object and set the next value generated by the sequence as the default value for the column.
• Second, add a NOT NULL constraint to the id column because a sequence always generates an integer, which is a non-null value.
• Third, assign the owner of the sequence to the id column; as a result, the sequence object is deleted when the id column or table is dropped

Behind the scenes, the following statement:

is equivalent to the following statements:

PostgreSQL provides three serial pseudo-types SMALLSERIAL , SERIAL , and BIGSERIAL with the following characteristics:

PostgreSQL SERIAL example

It is important to note that the SERIAL does not implicitly create an index on the column or make the column as the primary key column. However, this can be done easily by specifying the PRIMARY KEY constraint for the SERIAL column.

The following statement creates the fruits table with the id column as the SERIAL column:

To assign the default value for a serial column when you insert row into the table, you ignore the column name or use the DEFAULT keyword in the INSERT statement.

See the following example:

PostgreSQL inserted two rows into the fruits table with the values for the id column are 1 and 2.

To get the sequence name of a SERIAL column in a table, you use the pg_get_serial_sequence() function as follows:

You can pass a sequence name to the currval() function to get the recent value generated by the sequence. For example, the following statement returns the recent value generated by the fruits_id_seq object:

If you want to get the value generated by the sequence when you insert a new row into the table, you use the RETURNING id clause in the INSERT statement.

The following statement inserts a new row into the fruits table and returns the value generated for the id column.

The sequence generator operation is not transaction-safe. It means that if two concurrent database connections attempt to get the next value from a sequence, each client will get a different value. If one client rolls back the transaction, the sequence number of that client will be unused, creating a gap in the sequence.

In this tutorial, you have learned how to use the PostgreSQL pseudo-type SERIAL to create an auto-increment column for a table.