Типы данных в Postgresql
Целочисленные типы данных
smallint
integer - наиболее оптимальный
bigint
Числа фиксированной точности
numeric и decimal - числа фиксированной точности
numeric (точность, масштаб) -> numeric(6,4) -> 12.3456
Числа типа numeric могут хранить очень большое количество цифр: 131 072 цифры — до десятичной точки (запятой), 16 383 — после точки. Однако нужно учитывать, что такая точность достигается за счет замедления вычислений по сравнению с целочисленными типами и типами с плавающей точкой. При этом для хранения числа затрачивается больше памяти, чем в случае целых чисел. Основы языка SQL Моргунов Е.П.
Числа с плавающей точкой
real
double
precision
serial
Тип serial удобен в тех случаях, когда требуется в какой-либо столбец вставлять уникальные целые значения, например, значения суррогатного первичного ключа
CREATE TABLE имя-таблицы ( имя-столбца serial );
Одним из видов объектов в базе данных являются так называемые последова- тельности. Это, по сути, генераторы уникальных целых чисел. Для работы с этими последовательностями-генераторами используются специальные функции. Одна из них — это функция nextval, которая как раз и получает очередное число из последо- вательности, имя которой указано в качестве параметра функции. В команде CREATE TABLE ключевое слово DEFAULT предписывает, чтобы СУБД использовала в качестве значения по умолчанию то значение, которое формирует функция nextval.
Строковые типы данных
character
varying(n) или varchar(n) - наиболее востребованные
character(n) char(n)v
text - наиболее востребованные
Символ обратной косой черты или кавычек можно не удваивать, если в качестве экранирования в начале и в конце использовать $$
Для использования строковых констант в стиле языка C используется символ E перед началом строки(кавычки) :
SELECT E'PGDAY\n17';
Тип дата
«yyyy-mm-dd» - формат используемый по умолчанию
В postgresql приведение типа осуществляется с помощью двоеточия ::
SELECT '2016-09-12'::date;
SELECT 'Sep 12, 2016'::date;
Здесь в обоих случаях вывод будет одинаковыйДля получения значения текущей даты служит функция current_date SELECT current_date;
Для разового преобразования формата можно использовать функцию to_char
SELECT to_char( current_date, 'dd-mm-yyyy' );
Время суток и временная зона
Для хранения времени суток служат два типа данных:
time хранит - время суток
time with time zone - время суток и временная зона(часовой пояс)
select '21:15:15'::time;
12 часовой формат:
select '10:15:15 pm' ::time;
Получение текущего времени:
select current_time
При объединение даты и времени получается интегральный тип - временная метка.
Временная метка существует с учетом часового пояса и без.
select timestamp with time zone '2022-08-28 23:34:34';
timestamptz
------------------------
2022-08-28 23:34:34+03
(1 row)
#Без часового пояса
select timestamp '2022-08-28 23:34:34';
timestamp
---------------------
2022-08-28 23:34:34
(1 row)
Чтобы получить текущие время и дату одной командой:
select current_timestamp ;
current_timestamp
-------------------------------
2022-08-28 23:39:54.757214+03
(1 row)
select '2 year ago'::interval;
interval
----------
-2 years
(1 row)
interval
quantity unit [quantity unit ...] direction
unit - единица измерения (microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium)
quantity - кол-во этих едениц
direction - параметр (ago)
Альтернативный вариант:
demo=# select 'P0001-02-03' ::interval;
interval
----------------------
1 year 2 mons 3 days
(1 row)
Вычитание временных отрезков:
elect ('2022-08-01' ::timestamp - '2022-07-01' ::timestamp)::interval;
interval
----------
31 days
(1 row)
Усечение времени можно усекать с точностью до выбранных единиц:
SELECT ( date_trunc( 'hour', current_timestamp ) );
date_trunc
------------------------
2016-09-27 22:00:00+03
(1 строка)
Извлечение отдельных полей:
demo=# select extract ( 'mon' from timestamp '2001-09-12' );
date_part
-----------
9
(1 row)
Логический тип bool
bool - true (t, y, yes, 1) false (f, n, no, 0) null
Массивы
Для указания типа массив, дополнительно указываются квадратные скобки, число элементов не указываются:
# create table pilots ( pilot_name text, sheldure integer[] );
CREATE TABLE
Вставка значений в виде массива:
# insert into pilots
values( 'Egor', '{1, 2, 3, 4, 5}' ::integer[] ),
testdb-# ('Fedor', '{1, 3, 5}' ::integer[] ),
testdb-# ('Nikita', '{2, 4, 6}' ::integer[] );
INSERT 0 3
Добавить значения в массиве в конце списка:
# update pilots set sheldure = sheldure || 7 where pilot_name = 'Nikita';
UPDATE 1
Добавить значение в массиве в конце списка, с помощью функции array_append():
# update pilots set sheldure = array_append(sheldure, 7) where pilot_name = 'Fedor';
UPDATE 1
Добавить значение в массиве в начале списка:
# update pilots set sheldure = array_prepend(1, sheldure) where pilot_name = 'Nikita';
UPDATE 1
Удалить значение массива:
# update pilots set sheldure = array_remove(sheldure, 7) where pilot_name = 'Ivan';
UPDATE 1
Индекс массива начинается с единицы, а не с нуля
К элементам одного массива можно обращаться по индексу, через запятую:
# update pilots set sheldure[5] = 7, sheldure[4] = 6 where pilot_name = 'Egor';
UPDATE 1
testdb=# select * from pilots where pilot_name = 'Egor';
pilot_name | sheldure
------------+-------------
Egor | {1,2,3,6,7}
(1 row)
Либо используя срез (slice) массива
# update pilots set sheldure[5:6] = ARRAY[6,7] where pilot_name = 'Ivan';
UPDATE 1
Получить значения пилотов, у которых в массиве есть цифра 3(среда):
# select * from pilots where array_position(sheldure, 3) is not null;
pilot_name | sheldure
------------+---------------
Fedor | {1,3,5,7}
Egor | {1,2,3,6,7}
Ivan | {1,2,3,4,6,7}
(3 rows)
array_position() - возвращает первое вхождение в массиве
Вывод пилотов, у которых в массиве есть 1 и 5:
select * from pilots where sheldure @> '{1, 5}' ::integer[];
pilot_name | sheldure
------------+-----------
Fedor | {1,3,5,7}
(1 row)
Вывод пилотов у которых в массиве есть 1 или 5:
select * from pilots where sheldure && ARRAY[1,5];
Наоборот, вывод тех у кого нет 1 или 5 с использованием отрицания NOT:
select * from pilots where not sheldure && ARRAY[2, 6];
pilot_name | sheldure
------------+-----------
Fedor | {1,3,5,7}
(1 row)
Изменить формата вывода даты:
select to_char( date_update, 'DD.MM.YYYY hh:mm') FROM content_table ;
Больше о форматировании вывода здесь
Функции для обработки времени: здесь
Источники
Связанные темы
Буферный кэш и журнал в Postgresql
Системный каталог в Postgresql
Табличные пространства в Postgresql
Определение данных в Postgresql
Основные команды psql и sql Postgresql
Агрегирование и группировка в Postgresql
Основные понятия реляционной модели
Создание и управление кластером postgresql
Системные каталоги в Postgresql
Этапы запроса и получения результата в postgresql