Как узнать тип данных в 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 .
Id SERIAL, TotalWeight NUMERIC(9,2), Age INTEGER, Surplus REAL
Типы для работы с валютой (денежными единицами)
Для работы с денежными единицами определен тип 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 байт.
Распространенные форматы дат:
- yyyy-mm-dd — 1999-01-08
- Month dd, yyyy — January 8, 1999
- mm/dd/yyyy — 1/8/1999
Распространенные форматы времени:
- hh:mi — 13:21
- hh:mi am/pm — 1:21 pm
- hh:mi:ss — 1:21:34
Логический тип
Тип 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 : представляет окружность в формате . Занимает 24 байта.
Остальные типы данных
- json : хранит данные json в текстовом виде.
- jsonb : хранит данные json в бинарном формате.
- uuid : хранит универсальный уникальный идентификатор (UUID), например, a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 . Занимает 32 байта.
- xml : хранит даные в формате XML.
Узнать структуру таблицы PostgreSQL
Иногда требуется узнать структуру таблицы в базе данных. Для MySQL существует куча вариантов. С PostgreSQL все чуть сложней.
Если есть pgphpadmin или другая утилита для администрирования базы, то там как правило можно просмотреть колонки их тип и прочие параметры. В консоли psql так же есть способ узнать информацию о таблице, но он не так очевиден.
SELECT column_name, column_default, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'my_table';
Этот запрос возвращает название колонки, ее значение по умолчанию и тип данных. Если этого недостаточно, то можно «поэкспериментировать» со столбцами из таблицы INFORMATION_SCHEMA.COLUMNS и добавить то что необходимо.
Запись опубликована в рубрике Полезности с метками postgresql. Добавьте в закладки постоянную ссылку.
PostgreSQL: Узнать данные о колонках таблицы
Из таблицы information_schema.columns можно узнать много чего полезного о колонках той или иной таблицы или всех таблиц сразу. А также и представлений.
Узнаем все данные о колонках таблиц и представлений схемы ‘public’ базы данных ‘my_database’:
select * from information_schema.columns where table_catalog = 'my_database' and table_schema = 'public'
В результатах запроса появится таблица со множеством полей, где можно узнать перечень колонок, типы их данных, длину и так далее.
Чтобы получить перечень колонок и присвоенные им номера по порядку таблицы products, надо написать
select column_name, ordinal_position from information_schema.columns where table_catalog = 'products' and table_schema = 'public' and table_name = 'products'
Поделиться ссылкой:
- Послать ссылку другу по электронной почте (Открывается в новом окне)
- Нажмите для печати (Открывается в новом окне)
- Нажмите, чтобы поделиться в WhatsApp (Открывается в новом окне)
- Нажмите, чтобы открыть на Facebook (Открывается в новом окне)
- Нажмите, чтобы поделиться на Twitter (Открывается в новом окне)
- Нажмите, чтобы поделиться записями на Pinterest (Открывается в новом окне)
- Нажмите, чтобы поделиться в Telegram (Открывается в новом окне)
- Нажмите, чтобы поделиться на LinkedIn (Открывается в новом окне)
15 полезных команд PostgreSQL
В сети много руководств по PostgreSQL, которые описывают основные команды. Но при погружении в работу возникают такие практические вопросы, для которых требуются продвинутые команды. Рассмотрим несколько таких команд на примерах, полезных как для разработчиков, так и для администраторов баз данных.
В сети много руководств по PostgreSQL, которые описывают основные команды. Но при погружении глубже в работу возникают такие практические вопросы, для которых требуются продвинутые команды.
Такие команды, или сниппеты, редко описаны в документации. Рассмотрим несколько на примерах, полезных как для разработчиков, так и для администраторов баз данных.
Получение информации о базе данных
Размер базы данных
Чтобы получить физический размер файлов (хранилища) базы данных, используем следующий запрос:
SELECT pg_database_size(current_database());
Результат будет представлен как число вида 41809016 .
current_database() — функция, которая возвращает имя текущей базы данных. Вместо неё можно ввести имя текстом:
SELECT pg_database_size('my_database');
Для того, чтобы получить информацию в человекочитаемом виде, используем функцию pg_size_pretty :
SELECT pg_size_pretty(pg_database_size(current_database()));
В результате получим информацию вида 40 Mb .
Перечень таблиц
Иногда требуется получить перечень таблиц базы данных. Для этого используем следующий запрос:
SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','pg_catalog');
information_schema — стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д. Представления таблиц содержат информацию обо всех таблицах баз данных.
Запрос, описанный ниже, выберет все таблицы из указанной схемы текущей базы данных:
SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog') AND table_schema IN('public', 'myschema');
В последнем условии IN можно указать имя определенной схемы.
Размер таблицы
По аналогии с получением размера базы данных размер данных таблицы можно вычислить с помощью соответствующей функции:
SELECT pg_relation_size('accounts');
Функция pg_relation_size возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.
Имя самой большой таблицы
Для того, чтобы вывести список таблиц текущей базы данных, отсортированный по размеру таблицы, выполним следующий запрос:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT :
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;
relname — имя таблицы, индекса, представления и т.п.
relpages — размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
pg_class — системная таблица, которая содержит информацию о связях таблиц базы данных.
Перечень подключенных пользователей
Чтобы узнать имя, IP и используемый порт подключенных пользователей, выполним следующий запрос:
SELECT datname,usename,client_addr,client_port FROM pg_stat_activity;
Активность пользователя
Чтобы узнать активность соединения конкретного пользователя, используем следующий запрос:
SELECT datname FROM pg_stat_activity WHERE usename = 'devuser';
Работа с данными и полями таблиц
Удаление одинаковых строк
Если так получилось, что в таблице нет первичного ключа (primary key), то наверняка среди записей найдутся дубликаты. Если для такой таблицы, особенно большого размера, необходимо поставить ограничения (constraint) для проверки целостности, то удалим следующие элементы:
- дублирующиеся строки,
- ситуации, когда одна или более колонок дублируются (если эти колонки предполагается использовать в качестве первичного ключа).
Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).
Удалить все дубликаты поможет следующий запрос:
DELETE FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customers.*);
Уникальное для каждой записи поле ctid по умолчанию скрыто, но оно есть в каждой таблице.
Последний запрос требователен к ресурсам, поэтому будьте аккуратны при его выполнении на рабочем проекте.
Теперь рассмотрим случай, когда повторяются значения полей.
Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:
DELETE FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);
Если данные важны, то сначала нужно найти записи с дубликатами:
SELECT * FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);
Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение customer_id на другое.
Общая форма запроса на удаление описанных выше записей выглядит следующим образом:
DELETE FROM table_name WHERE ctid NOT IN (SELECT max(ctid) FROM table_name GROUP BY column1, [column 2,] );
Безопасное изменение типа поля
Может возникнуть вопрос о включении в этот список такой задачи. Ведь в PostgreSQL изменить тип поля очень просто с помощью команды ALTER . Давайте для примера снова рассмотрим таблицу с покупателями.
Для поля customer_id используется строковый тип данных varchar . Это ошибка, так как в этом поле предполагается хранить идентификаторы покупателей, которые имеют целочисленный формат integer . Использование varchar неоправданно. Попробуем исправить это недоразумение с помощью команды ALTER :
ALTER TABLE customers ALTER COLUMN customer_id TYPE integer;
Но в результате выполнения получим ошибку:
Это значит, что нельзя просто так взять и изменить тип поля при наличии данных в таблице. Так как использовался тип varchar , СУБД не может определить принадлежность значения к integer . Хотя данные соответствуют именно этому типу. Для того, чтобы уточнить этот момент, в сообщении об ошибке предлагается использовать выражение USING , чтобы корректно преобразовать наши данные в integer :
ALTER TABLE customers ALTER COLUMN customer_id TYPE integer USING (customer_id::integer);
В результате всё прошло без ошибок:
Обратите внимание, что при использовании USING кроме конкретного выражения возможно использование функций, других полей и операторов.
Например, преобразуем поле customer_id обратно в varchar , но с преобразованием формата данных:
ALTER TABLE customers ALTER COLUMN customer_id TYPE varchar USING (customer_id || '-' || first_name);
В результате таблица примет следующий вид:
Поиск «потерянных» значений
Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.
Рассмотрим два варианта поиска.
Первый способ
Выполним следующий запрос, чтобы найти начало интервала с «потерянным» значением:
SELECT customer_id + 1 FROM customers mo WHERE NOT EXISTS ( SELECT NULL FROM customers mi WHERE mi.customer_id = mo.customer_id + 1 ) ORDER BY customer_id;
В результате получим значения: 5 , 9 и 11 .
Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:
WITH seq_max AS ( SELECT max(customer_id) FROM customers ), seq_min AS ( SELECT min(customer_id) FROM customers ) SELECT * FROM generate_series((SELECT min FROM seq_min),(SELECT max FROM seq_max)) EXCEPT SELECT customer_id FROM customers;
В результате видим следующий результат: 5 , 9 и 6 .
Второй способ
Получаем имя последовательности, связанной с customer_id :
SELECT pg_get_serial_sequence('customers', 'customer_id');
И находим все пропущенные идентификаторы:
WITH sequence_info AS ( SELECT start_value, last_value FROM "SchemaName"."SequenceName" ) SELECT generate_series ((sequence_info.start_value), (sequence_info.last_value)) FROM sequence_info EXCEPT SELECT customer_id FROM customers;
Подсчёт количества строк в таблице
Количество строк вычисляется стандартной функцией count , но её можно использовать с дополнительными условиями.
Общее количество строк в таблице:
SELECT count(*) FROM table;
Количество строк при условии, что указанное поле не содержит NULL :
SELECT count(col_name) FROM table;
Количество уникальных строк по указанному полю:
SELECT count(distinct col_name) FROM table;
Использование транзакций
Транзакция объединяет последовательность действий в одну операцию. Её особенность в том, что при ошибке в выполнении транзакции ни один из результатов действий не сохранится в базе данных.
Начнём транзакцию с помощью команды BEGIN .
Для того, чтобы откатить все операции, расположенные после BEGIN , используем команду ROLLBACK .
А чтобы применить — команду COMMIT .
Просмотр и завершение исполняемых запросов
Для того, чтобы получить информацию о запросах, выполним следующую команду:
SELECT pid, age(query_start, clock_timestamp()), usename, query FROM pg_stat_activity WHERE query != '' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start desc;
Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):
SELECT pg_cancel_backend(procpid);
Для того, чтобы прекратить работу запроса, выполним:
SELECT pg_terminate_backend(procpid);
Работа с конфигурацией
Поиск и изменение расположения экземпляра кластера
Возможна ситуация, когда на одной операционной системе настроено несколько экземпляров PostgreSQL, которые «сидят» на различных портах. В этом случае поиск пути к физическому размещению каждого экземпляра — достаточно нервная задача. Для того, чтобы получить эту информацию, выполним следующий запрос для любой базы данных интересующего кластера:
SHOW data_directory;
Изменим расположение на другое с помощью команды:
SET data_directory to new_directory_path;
Но для того, чтобы изменения вступили в силу, требуется перезагрузка.
Получение перечня доступных типов данных
Получим перечень доступных типов данных с помощью команды:
SELECT typname, typlen from pg_type where typtype='b';
typname — имя типа данных.
typlen — размер типа данных.
Изменение настроек СУБД без перезагрузки
Настройки PostgreSQL находятся в специальных файлах вроде postgresql.conf и pg_hba.conf . После изменения этих файлов нужно, чтобы СУБД снова получила настройки. Для этого производится перезагрузка сервера баз данных. Понятно, что приходится это делать, но на продакшн-версии проекта, которым пользуются тысячи пользователей, это очень нежелательно. Поэтому в PostgreSQL есть функция, с помощью которой можно применить изменения без перезагрузки сервера:
SELECT pg_reload_conf();
Но, к сожалению, она применима не ко всем параметрам. В некоторых случаях для применения настроек перезагрузка обязательна.
На данный момент этот блок не поддерживается, но мы не забыли о нём! Наша команда уже занята его разработкой, он будет доступен в ближайшее время.
Мы рассмотрели команды, которые помогут упростить работу разработчикам и администраторам баз данных, использующим PostgreSQL. Но это далеко не все возможные приёмы. Если вы сталкивались с интересными задачами, напишите о них в комментариях. Поделимся полезным опытом!
Следите за новыми постами по любимым темам
Подпишитесь на интересующие вас теги, чтобы следить за новыми постами и быть в курсе событий.