Postgresql типы данных: Postgres Pro Standard : Документация: 10: 8.4. Двоичные типы данных : Компания Postgres Professional

Postgres Pro Standard : Документация: 10: 8.4. Двоичные типы данных : Компания Postgres Professional

8.4. Двоичные типы данных

Для хранения двоичных данных предназначен тип bytea; см. Таблицу 8.6.

Таблица 8.6. Двоичные типы данных

Двоичные строки представляют собой последовательность октетов (байт) и имеют два отличия от текстовых строк. Во-первых, в двоичных строках можно хранить байты с кодом 0 и другими «непечатаемыми» значениями (обычно это значения вне десятичного диапазона 32..126). В текстовых строках нельзя сохранять нулевые байты, а также значения и последовательности значений, не соответствующие выбранной кодировке базы данных. Во-вторых, в операциях с двоичными строками обрабатываются байты в чистом виде, тогда как текстовые строки обрабатываются в зависимости от языковых стандартов. То есть, двоичные строки больше подходят для данных, которые программист видит как «просто байты», а символьные строки — для хранения текста.

Тип bytea поддерживает два формата ввода и вывода: «шестнадцатеричный» и традиционный для PostgreSQL формат «спецпоследовательностей». Входные данные принимаются в обоих форматах, а формат выходных данных зависит от параметра конфигурации bytea_output; по умолчанию выбран шестнадцатеричный. (Заметьте, что шестнадцатеричный формат был введён в PostgreSQL 9.0; в ранних версиях и некоторых программах он не будет работать.)

Стандарт SQL определяет другой тип двоичных данных, BLOB (BINARY LARGE OBJECT, большой двоичный объект). Его входной формат отличается от форматов bytea, но функции и операторы в основном те же.

8.4.1. Шестнадцатеричный формат

bytea

В «шестнадцатеричном» формате двоичные данные кодируются двумя шестнадцатеричными цифрами на байт, при этом первая цифра соответствует старшим 4 битам. К полученной строке добавляется префикс \x (чтобы она отличалась от формата спецпоследовательности). В некоторых контекстах обратную косую черту нужно экранировать, продублировав её (см. Подраздел 4.1.2.1). Вводимые шестнадцатеричные цифры могут быть в любом регистре, а между парами цифр допускаются пробельные символы (но не внутри пары и не в начале последовательности \x). Этот формат совместим со множеством внешних приложений и протоколов, к тому же обычно преобразуется быстрее, поэтому предпочтительнее использовать его.

Пример:

SELECT '\xDEADBEEF';

8.4.2. Формат спецпоследовательностей

bytea

Формат «спецпоследовательностей» традиционно использовался в Postgres Pro для значений типа bytea. В нём двоичная строка представляется в виде последовательности ASCII-символов, а байты, непредставимые в виде ASCII-символов, передаются в виде спецпоследовательностей. Этот формат может быть удобен, если с точки зрения приложения представление байт в виде символов имеет смысл. Но на практике это обычно создаёт путаницу, так как двоичные и символьные строки могут выглядеть одинаково, а кроме того выбранный механизм спецпоследовательностей довольно неуклюж. Поэтому в новых приложениях этот формат обычно не стоит использовать.

Передавая значения bytea в формате спецпоследовательности, байты с определёнными значениями необходимо записывать специальным образом, хотя так можно записывать и все значения. В общем виде для этого значение байта нужно преобразовать в трёхзначное восьмеричное число и добавить перед ним обратную косую черту. Саму обратную косую черту (символ с десятичным кодом 92) можно записать в виде двух таких символов. В Таблице 8.7 перечислены символы, которые нужно записывать спецпоследовательностями, и приведены альтернативные варианты записи, если они возможны.

Таблица 8.7. Спецпоследовательности записи значений bytea

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

Апострофы должны дублироваться, как показано в Таблице 8.7, потому что это обязательно для любой текстовой строки в команде SQL. При общем разборе текстовой строки внешние апострофы убираются, а каждая пара внутренних сводится к одному символу. Таким образом, функция ввода bytea видит всего один апостроф, который она обрабатывает как обычный символ в данных. Дублировать же обратную косую черту при вводе bytea не требуется: этот символ считается особым и меняет поведение функции ввода, как показано в Таблице 8.7.

В некоторых контекстах обратная косая черта должна дублироваться (относительно примеров выше), так как при общем разборе строковых констант пара таких символов будет сведена к одному; см. Подраздел 4.1.2.1.

Данные Bytea по умолчанию выводятся в шестнадцатеричном формате (hex). Если поменять значение bytea_output на escape, «непечатаемые» байты представляются в виде соответствующих трёхзначных восьмеричных значений, которые предваряются одной обратной косой чертой. Большинство «печатаемых» байтов представляются обычными символами из клиентского набора символов, например:

SET bytea_output = 'escape';

SELECT 'abc \153\154\155 \052\251\124'::bytea;
     bytea
----------------
 abc klm *\251T

Байт с десятичным кодом 92 (обратная косая черта) при выводе дублируется. Это иллюстрирует Таблица 8.8.

Таблица 8.8. Спецпоследовательности выходных значений bytea

В зависимости от применяемой клиентской библиотеки Postgres Pro, для преобразования значений bytea в спецстроки и обратно могут потребоваться дополнительные действия. Например, если приложение сохраняет в строках символы перевода строк, возможно их также нужно будет представить спецпоследовательностями.

Типы данных PostgreSQL, поддерживаемые в ArcGIS—Справка

Как указано в таблице, ArcGIS создает и может работать в PostgreSQL с двумя типами геометрических данных: Esri ST_Geometry или PostGIS Geometry. Более подробная информация о них приведена в следующих двух разделах.

ST_Geometry

Ниже приводится общее описание пространственного типа данных ST_Geometry. Сведения о реализации в PostgreSQL см. в разделе ST_Geometry в PostgreSQL.

Тип данных ST_Geometry использует спецификацию пользовательских типов данных SQL 3 (UDT), что позволяет создавать столбцы, в которых могут храниться пространственные данные, например, улицы, земельные участки, достопримечательности. В соответствии со стандартами International Organization for Standards (ISO) и Open Geospatial Consortium, Inc., (OGC) он обеспечивает поддержку SQL-доступа к базам геоданных и базам данных Данный вид хранения расширяет возможности базы данных, обеспечивая хранение для объектов (точек, линий и полигонов), которые представляют графические объекты. Оно было разработано для эффективного использования ресурсов баз данных, для совместимости с объектами баз данных, такими, как реплики и разделы, и для предоставления быстрого доступа к пространственным данным.

ST_Geometry является абстрактным невызываемым суперклассом. Однако его подклассы могут быть вызваны (инициализированы). Вызванный тип данных – это тип данных, который может быть определен как столбец таблицы; в него могут быть добавлены значения этого типа.

Хотя вы можете определить столбец в виде типа ST_Geometry, вы не добавляете значения ST_Geometry в этот столбец, поскольку он не может быть вызван. Вместо этого вы добавляете значения подкласса.

Следующая схема демонстрирует иерархию типа данных ST_Geometry и его подклассов.

Суперкласс ST_Geometry и его подклассы

Подклассы ST_Geometry разделены на две категории: подклассы основной геометрии и подклассы однородных наборов. Основная геометрия включает: ST_Point, ST_LineString и ST_Polygon, а однородные наборы включают ST_MultiPoint, ST_MultiLineString и ST_MultiPolygon. Как ясно из названий, наборы однородных элементов являются наборами базовых геометрий. Помимо добавлений одинаковых свойств с базовой геометрией, однородные наборы имеют некоторые собственные свойства.

Каждый подкласс хранит тип геометрии, отображенный в его имени; например, ST_MultiPoint хранит мультиточки. Список подклассов и их описание приведены в следующей таблице:

Подтипы ST_Geometry

Учтите, что каждый подкласс наследует свойства суперкласса ST_Geometry, но также имеет собственные свойства. Функции, которые работают с типом данных ST_Geometry, принимают любой из типов элементов подклассов. Однако некоторые функции определены на уровне подклассов, и принимают только заданные подклассы. Например, функция ST_GeometryN принимает в качестве входных параметров только значения подтипов ST_MultiLinestring, ST_MultiPoint или ST_MultiPolygon.

Геометрия PostGIS

PostGIS – это продукт, который расширяет возможности базы данных PostgreSQL для работы с пространственными данными. PostGIS следует спецификации Open Geospatial Consortium, Inc. (OGC) для простых объектов для языка структурированных запросов (Structured Query Language (SQL)). В этом продукте используется стандартные двоичные (WKB) и текстовые (WKT) представления геометрии.

В PostGIS имеется два пространственных типа: geometry и geography. В ArcGIS поддерживается только тип geometry.

При использовании типа хранения geometry в PostGIS с ArcGIS нужно учесть следующее:

• Нужно использовать шаблон базы данных PostGIS для создания базы данных PostgreSQL, используемой для базы геоданных, или активировать PostGIS в базе данных.
  

  Примечание:

  Если база геоданных создается с использованием инструмента геообработки Создать многопользовательскую базу геоданных, ваша база данных будет создана с использованием шаблона, отличающегося от шаблона PostGIS. Поэтому, если вы хотите использовать тип геометрии PostGIS, следует создать базу данных вручную, используя шаблон PostGIS. После этого при запуске инструмента геообработки Создать многопользовательскую базу геоданных можно выбрать существующую базу данных, в которой будет создана база геоданных.

• Пользователь sde и любой пользователь, создающий данные в базе геоданных, должен получить разрешения для используемых таблиц PostGIS.
• Классы создаваемых пространственных объектов могут использовать только пространственные привязки, перечисленные в таблице PostGIS public.spatial_ref_sys. Если указана привязка, не перечисленная в таблице, класс объектов создан не будет.
• Следует указать ключевое слово конфигурации, которое содержит параметр GEOMETRY_STORAGE, присвоенный для PG_GEOMETRY, чтобы создать класс объекта, использующий тип геометрии PostGIS.
• Нельзя переименовывать пространственные таблицы, сохраненные с использованием типа геометрии PostGIS. Данное ограничение связано с тем, что не существует функции PostGIS для обновления имени таблицы в public.geometry_columns.

Для использования типа геометрии PostGIS следует установить PostGIS. PostGIS устанавливается после установки PostgreSQL. Убедитесь, что вы установили версию PostGIS, поддерживаемую версией ArcGIS, с которой вы работаете.

При установке PostGIS создается шаблон базы данных PostGIS в кластере базы данных PostgreSQL. Используйте шаблон базы данных PostGIS для создания базы данных, в которой будет храниться ваша база геоданных.

Предоставление прав на создание столбцов геометрии PostGIS

Если для базы данных разрешено использование PostGIS, в общей схеме будут созданы две таблицы – geometry_columns и spatial_ref_sys. Как минимум, нужно выдать права SELECT, INSERT, UPDATE и DELETE для столбцов таблицы geometry_columns, а также выбрать SELECT для таблицы spatial_ref_sys для пользователя sde и всех пользователей, которые будут создавать данные в базе геоданных.

GRANT select, insert, update, delete 
 ON TABLE public.geometry_columns 
 TO <user_name>;
GRANT select
 ON TABLE public.spatial_ref_sys
 TO <user_name>;

Создание класса пространственных объектов, использующего геометрию PostGIS

ArcGIS использует настройки параметров конфигурации в таблице sde_dbtune для определения типа пространственных данных, используемого при создании класса пространственных объектов. Этот параметр имеет название GEOMETRY_STORAGE. В базах геоданных, работающих в базах данных PostgreSQL, используют один из двух типов хранения геометрии: ST_GEOMETRY или PG_GEOMETRY (значение параметра типа хранения PostGIS geometry). Таким образом, если нужно создать класс объектов с использованием ArcGIS на базе типа хранения PostGIS geometry, следует указать ключевое слово конфигурации, которое содержит параметр GEOMETRY_STORAGE со значением PG_GEOMETRY.

По умолчанию новые классы пространственных объектов используют хранение ST_Geometry. Если нужно сохранить большинство данных с типом хранения PostGIS, измените параметр GEOMETRY_STORAGE в ключевом слове конфигурации DEFAULTS в таблице sde_dbtune на PG_GEOMETRY. Или если вы хотите хранить только часть классов пространственных объектов в типе геометрии PostGIS, то вы можете указать ключевое слово конфигурации PG_GEOMETRY при создании класса объектов. При экспорте из базы геоданных ключевое слово PG_GEOMETRY выглядит следующим образом:

##PG_GEOMETRY
GEOMETRY_STORAGE    "PG_GEOMETRY"
UI_TEXT             "User Interface text description for POSTGIS geometry storage" 
END

Остальные параметры хранения будут взяты из ключевого слова DEFAULTS. Более подробно о ключевых словах конфигурации и параметрах см. в следующих разделах:

Использование существующих таблиц геометрии

ArcGIS может использовать таблицы, содержащие столбцы геометрии формата PostGIS, созданные вне ArcGIS другими приложениями или при помощи SQL (которые иногда называют таблицами третьих лиц) при условии, что эти таблицы соответствуют следующим условиям.

• Каждая таблица должна содержать один столбец геометрии. Если нет, определите слой запроса или представление, которые будут содержать только один пространственный столбец.
• Таблицы не должны иметь других столбцов, тип которых задается пользователем.
• Таблицы должны иметь один тип геометрии (точки, линии или полигоны), при этом геометрия может быть составной.
• В каждой таблице необходимо наличие столбца с целочисленными, уникальными значениями (не NULL), которые можно использовать в качестве идентификатора строки.
• В каждой таблице должен присутствовать пространственный индекс.

Сведения о создании таблиц со столбцом PostGIS с помощью SQL находятся в документации к PostGIS.

Можно подключиться к базе данных PostgreSQL, содержащей таблицы PostGIS, через ArcGIS for Desktop и зарегистрировать их в базе геоданных. Дополнительную информацию см. в разделе Регистрация таблицы в базе геоданных.

PgConf.Russia 2019 — MobilityDB: расширение PostgreSQL для управления мобильными данными

06 февраля 2019 г.
10:45

В ходе доклада мы представим MobilityDB — расширение PostgreSQL, которое раздвигает границы системы типов в PostgreSQL и PostGIS на абстрактные данные для адекватного представления изменяющихся данных об объектах. Эти типы данных могут представлять эволюцию во времени значений некоторого типа элементов, называемого базовым темпоральным типом. Например, темпоральный целочисленный тип данных может использоваться для демонстрации изменения во времени количества сотрудников департамента. В данном случае базовым типом данных будет целочисленный или темпоральный целочисленный. Аналогично, темпоральный тип данных с плавающей точкой может использоваться для записи изменения во времени температуры в помещении или местоположения автомобиля по GPS-координатам. Темпоральные типы данных оказываются полезны, поскольку для работы многих приложений, например, мобильных, принципиально необходимо обрабатывать изменяющиеся во времени величины.

В расширении MobilityDB темпоральные типы данных основаны на булевых, целочисленных, с плавающей точкой и текстовых типах данных от PostgreSQL, а также на геометрических и географических типах данных от PostGIS (ограниченных размерностью 2D или 3D). MobilityDB соответствует действующим стандартам по перемещаемым объектам OGC http://www.opengeospatial.org/standards/movingfeatures, в частности, OGC Moving Features Access, в котором определены операции, применимые к изменяющимся во времени геометриям.

Для проведения разноообрзаных операций над темпоральными типами данных доступен богатый набор функций и операторов. В общем случае они разделюятся на три типа:

• Пожизненные функции и операторы: операторы над базовыми типами (такие как арифметические операции над целыми числами и числами с плавающей точкой, пространственные отношения и расстояния для геометрий) интуитивно обобщаются на случай изменяющихся во времени значений. Пространственно-темпоральные функции в MobilityDB обобщают пространственные функции PostGIS как для геометрических, так и для географических типов данных, к примеру для «ST_Intersection». На базовом уровне, MobilityDB принимает в расчет аспект темпоральности и делегирует обработку пространственных данных в PostGIS.
• Темпоральные функции и операторы обрабатывают изменяющиеся во времени размерности величины, которая может представлять собой единичное значение, диапазон значений, массив значений или массив диапазонов. Примерами являются функции периодов, которые ограничивают темпоральный тип заданным массивом временных диапазонов, а также функции продолжительности, которые извлекают время определения значения величины.
• Пространственно-темпоральные функции и операторы — в эту категорию попадают все остальные функции. Примеры: speed(tgeompoint/tgeogpoint), azimuth(tgeompoint/tgeogpoint), maxValue(tfloat/tint), взвешенное по времени среднее twAvg(tfloat) и т.д.

Как GiST, так и SP-GiST индексы были расширены для поддержки темпоральных типов данных. Индекс GiST реализует R-дерево для темпоральных численно-буквенных типов данных, а TB-дерево — для темпоральных координат. Индекс SP-GiST реализует Quad-дерево для темпоральных численно-буквенных типов данных, а Oct-дерево — для темпоральных координат. Подход, использованный в MobilityDB при разработке SP-GIST индекса, позволил нам добавить индексы SP-GIST для двумерных, трехмерных и n-мерных геометрий в PostGIS.

Доступны два типа числовых функций аггрегирования. В дополнение к традиционным функциям min, max, count, sum, and avg, теперь есть и их оконные версии (также известные как кумулятивные). Для заданного промежутка времени w, оконная аггрегативная функция вычисляет значение функции в момент времени t, принимая в расчет значения на интервале [t − w, t]. В противоположность стандартной аггрегации, темпоральная аггрегация может возвращать результат большего размера, чем входящие данные. По этой причине темпоральные функции аггрегирования были подвергнуты жесткой оптимизации, чтобы обеспечить их эффективную работу.

В MobilityDB также есть предварительная реализация функций сбора статистики и селективности для темпоральных типов данных.

С точки зрения размера, расширение состоит из 67k строк кода на C, 19k строк SQL кода и 67k строк модульных тестов SQL. В нем определены 40 типов, 2300 функций и 1350 операторов.

В ходе доклада будет проиллюстрирована пространственно-темпоральная концепция и модель данных для темпорального типа. Кратко остановимся на основных компонентах MobilityDB: индексах, аггрегировании, функциях и операторах, а также SQL-интерфейсе. Рассказ будет дополнен примерами запросов и практических случаев использования. Также будет рассказано о текущем статусе проекта MobilityDB и планируемых разработках.

PostgreSQL: как перечислить все доступные типы данных?

Вопрос:

В PostgreSQL ( используя SQL, а не консоль), как я могу перечислить все доступные datataypes ?

В идеале вот так:
http:/ / www.java2s.com / Код/PostgreSQL / Postgre-SQL/Displaysalldatatypesintheconnecteddatabasewithcomments.htm

Он также должен перечислять определенные пользователем типы, если таковые имеются.

Точно так же, как список в pgAdmin3, где вы определяете тип данных для нового столбца в таблице.

sql

postgresql

postgresql-9.1

postgresql-9.2

Поделиться

Источник

Stefan Steiger    

02 мая 2013 в 16:36

2 ответа

• Как перечислить все доступные темы LookAndFeel?

  Как перечислить все доступные темы LookAndFeel? Я хочу показать тогда в JComboBox, чтобы пользователь мог выбрать.

• Как перечислить все раскомментированные столбцы PostgreSQL?

  Я документирую схему базы данных, используя встроенные возможности комментариев PostgreSQL. В какой-то момент я забыл прокомментировать некоторые колонки, но не уверен, какие именно. Есть ли способ перечислить все столбцы в схеме, которые не комментируются ? Это было бы очень полезно, чтобы иметь…

26

«data types» в PostgreSQL фактически включает примитивные (встроенные) типы, типы, добавляемые расширениями, определяемые пользователем составные типы, домены и типы строк таблицы. Неясно, какие из них представляют для вас интерес. Все типы, доступные в данной базе данных, перечислены в pg_catalog.pg_type этой базы данных, поэтому вам может потребоваться отфильтровать результаты. См. документацию по таблице системного каталога pg_type .

Типы доступных, но не установленных расширений не перечислены. Нет никакого способа перечислить типы, предоставляемые расширениями, не установленными в текущей базе данных.

Чтобы получить более красивый список типов, используйте команду psql ‘s \dT * . Вы можете увидеть базовый SQL, который он выполняет, запустив psql с флагом -E :

$ psql -E regress
regress=> \dT *
********* QUERY **********
SELECT n.nspname as "Schema",
  pg_catalog.format_type(t.oid, NULL) AS "Name",
  pg_catalog.obj_description(t.oid, 'pg_type') as "Description"
FROM pg_catalog.pg_type t
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))
  AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
  AND pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2;
**************************

Поделиться

Craig Ringer    

03 мая 2013 в 00:04

Поделиться

Clodoaldo Neto    

02 мая 2013 в 16:41

Похожие вопросы:

dwscript-как перечислить все доступные типы?

Эй, Delphi Web Script-это действительно отличный скриптовый движок. Я пытаюсь использовать его в одном из своих проектов. Однако я не уверен, что можно перечислить все типы, функции, доступные в…

Как перечислить все доступные веб-сервисы

Как я могу перечислить все доступные веб-сервисы, просто указав имя сервера? Я требую, чтобы я просто указал имя сервера, а затем список будет заполнен именами всех доступных веб-сервисов.

Как перечислить все доступные сетевые интерфейсы?

Как перечислить все сетевые интерфейсы, доступные в настоящее время на компьютере (включая виртуальные, не подключенные, петлевые и т. д.)? Мне нужно знать их маску IP4/6,, шлюз, DNS, WINS и т. д….

Как перечислить все доступные темы LookAndFeel?

Как перечислить все доступные темы LookAndFeel? Я хочу показать тогда в JComboBox, чтобы пользователь мог выбрать.умолчанию» ]
[, ELEMENT = элемент ] [. DELIMITER = разделитель ]
[, SEND = функция_отправки ] [, RECEIVE = функция_получения ]
[, PASSEDBYVALUE ]
[, ALIGNMENT = выравнивание ]
[, STORAGE = хранение ])

Параметры:

• тип. Имя нового типа. Максимальная длина имени равна 30 символам. Имена типов должны быть уникальными в рамках базы данных и не могут начинаться с символа подчеркивания (зарезервирован для типов косвенно определяемых массивов).
• внутренний_размер. Внутренний размер определяемого типа (в байтах).
• внешний_размер. Внешний размер определяемого тина (в байтах).
• входная_функция. Имя входной функции определяемого типа. Функция должна быть заранее определена командой CREATE FUNCTION и должна преобразовывать внешнее представление типа во внутреннее представление.
• выходная_функция. Имя выходной функции определяемого типа. Функция должна преобразовывать внутреннее представление типа во внешнее представление (форму, предназначенную для вывода).
• элемент. Тип данных отдельного элемента массива, адресуемого данным типом (при самостоятельной реализации типов для хранения массивов). Параметр элемент должен быть типом данных фиксированного размера.
• разделитель. Разделитель значений для косвенно определяемых массивов, связанных с типом (тип[]).
• значение_по_умолчанию. Значение по умолчанию для нового типа данных. Если значение не указано, по умолчанию поля заполняются либо величиной, указанной в ограничении DEFAULT уровня таблицы, либо псевдозначениямп NULL.
• функция_отправки. Имя функции отправки для определяемого типа. Функция преобразует данные в форму, подходящую для передачи на другой компьютер, однако в PostgrcSQL 7.1.x такая возможность не поддерживается, поэтому параметр не используется.
• функция_получения. Имя функции получения для определяемого типа. Функция получает данные в форме, сгенерированной функцией отправки, и восстанавливает но ней внутреннее представление типа. В PostgreSQL 7.1.х такая возможность не поддерживается, поэтому параметр не используется.
• PASSEDBYVALUE. Необязательное ключевое слово PASSEDBYVALUE означает, что операторы п функции, использующие определяемый тип данных, должны передавать соответствующие аргументы по значению, а не по ссылке (как происходит по умолчанию). Параметр не может использоваться с типами, внутреннее представление которых занимает более 4 байт.
• выравнивание. Способ выравнивания определяемого типа в памяти. Допустимые значения – char, int2, int4 и double. Если параметр не задан, по умолчанию выбирается значение int4.
• хранение. Принцип храпения значений определяемого типа. Допустимые значения – plain, external, extended и main. Если параметр не задан, по умолчанию выбирается значение plain.

Результаты:

CREATE. Это сообщение выдается при успешном создании типа.

Описание

Команда CREATE TYPE предназначена для регистрации новых пользовательских типов в текущей базе данных. Пользователь PostgreSQL, выполнивший команду, становится владельцем нового типа.

При создании нового типа указываются две пользовательские функции, написанные на С, – входная и выходная функции определяемого типа данных. Входная функция преобразует внешнее представление типа во внутреннее представление, которое используется системными объектами, связанными с этим типом. Выходная функция преобразует внутреннее представление во внешнее.

Обе функции, входная и выходная, вызываются с одним аргументом типа opaque. Выходная функция должна возвращать тип opaque, тогда как входная функция возвращает значение определяемого типа. Обратите внимание: функции должны быть определены до создания типа.

Тип определяется с фиксированным или с переменным размером. Если вы хотите создать тип фиксированного размера, присвоите параметру INTERNALLENGTH нужное числовое значение (в байтах). Если тип определяется с переменным размером, используйте ключевое слово VARIABLE вместо параметра INTERNALLENGTH; в этом случае размер будет определяться по тем же правилам, что и для типа text. Внешний размер (EXTERNALLENGTH) задается по тому же принципу – в виде числового значения или с ключевым словом VARIABLE.

Выбор типа данных для строк/текста (char vs varchar vs text) — PostgreSQL Cook Book

В PostgreSQL есть три типа данных для хранения текстовой информации:

• character(n), char(n) — текст фиксированной длины (строка всегда имеет строго заданный размер)
• character varying(n), varchar(n) — текст с ограничением по длине (максимальная длина строка может быть ограничена)
• text — текст неограниченной длины

Физически максимальный размер любых строк в PostgreSQL ограничен одним гигабайтом (1GB).

Примечание

Логическое ограничение на максимальный размер строки (char(n), varchar(n), где n — логическое ограничение),
задаваемое пользователем для конкретного поля,
лимитирует количество символов в строке, а не длину строки в байтах. Поэтому,
несмотря на физический лимит на максимальный размер строки в 1GB, реальное максимальное количество символов в строке может быть меньше, так
как различные кодировки символов могут использовать больше одного байта для представления одного символа.

CHAR(N)

Этот тип всегда имеет ограничение по длине. Даже, если длина не задана (char), то подразумевается, что она равна
единице, то есть char ~ char(1).

Тип имеет одну особенность, которая выделяет есть среди других текстовых типов.
Так как поля с этим типом имеют фиксированный размер (не больше / не меньше заданного),
то, при сохранении в таблицу у всех строк, длина которых меньше этого размера,
оставшееся пустое место в конце строки заполняется пробелами.
Поэтому при сохранении значения ‘123’ в поле с типом char(6) в таблицу реально сохранится ‘123___’. Это может стать
неприятным сюрпризом.

При операциях сравнения полей с типом char(n) пробелы в конце строки игнорируются.

denis=# SELECT '123   '::char(6) = '123';
 ?column?
----------
 t
(1 row)

VARCHAR

Тип без заданной длины интерпретируется PostgreSQL, как безразмерная строка эквивалентная типу text.
При попытке записать в текстовое поле ограниченной длины значение, которое превышает заданные размеры, генерируется ошибка.
Строка этого типа хранится в базе данных без изменений (в отличии от char(n)), поэтому нагрузка на процессор при обработке
varchar-строк немного меньше, чем при работе с char(n).

TEXT

Этот тип позволяет сохранять строки размером до одного гигабайта. Поэтому его часто используют для хранения текстов,
html-страниц и прочей тестовой информации размер которой превышает одну строку текста.

Предупреждение

Тип text является специфическим типом PostgreSQL, поэтому при разработке приложений для работы с разными СУБД,
лучше использовать безразмерный varchar для лучшей переносимости бизнес-логики.

Резюме

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

Для хранения строк довольно комфортным выбором будет тип varchar без ограничения по длине (если нет жесткого условия, которое не в коем случае
нельзя нарушить). Тип char имеет ряд специфических особенностей, которые в большинстве случаев будут только мешать
при работе с базой данных. Тип text является специфическим для PostrgeSQL и не имеет никаких преимуществ/недостатком перед
типом varchar без ограничения по длине.

Какой тип данных выбрать для email в PostgreSQL

Как уже говорили в комментариях (спасибо, @D-side), аналогичный вопрос был на DBA StackExchange. Разумеется, там и ответ есть 🙂

Кроме того, pgAdmin 4 — это одно (и далеко не единственное) из открытых бесплатных средств администрирования Postgres (а есть еще и пропиетарные). Какие типы данных оно отображает в своём интерфейсе — это одно, а типы данных, которые поддерживает сама СУБД — это несколько иное.

С другой стороны, мы должны понимать, а что же именно мы хотим хранить и какое оно бывает? Для этого можно глянуть непосредственно стандарт, описанный как RFC 821. В стандарте нас интересует возможная длина адреса (описано на стр. 42), и она составит 129 символов.
Но раньше существовал еще и другой стандарт RFC 2821 Simple Mail Transfer Protocol (устарел), где длина адреса была до 255 символов. Подробнее можно почитать тут. Стандарт-то может и устарел, но гарантий, что уже и нет адресов длиной в 255 символов никто не даст.
Потому давайте все же перестрахуемся, и будем считать, что 255 — это максимальная длина адреса электропочты.

Еще нас интересует, чувствителен ли адрес e-mail к регистру букв? Доверившись английской версии StackOverflow и стандарту RFC 5321, раздел 2.3.11, получается, что часть адреса до собачки вроде как может быть чувствительна к регистру букв.

В то же время, на SO по ссылке выше говорится, что согласно RFC 1035, section 3.1, адрес домена не чувствителен к регистру. Проверять не будем, так как даже того, что часть адреса до собаки может быть регистрозависимой, нам достаточно, чтобы понять, что для хранения адреса нам нужен такой тип, который мог бы хранить регистрозависимые данные.

Итого — нам нужен чувствительный к регистру тип, в котором можно хранить 255 символов.

Автор вопроса пользуется pgAdmin 4 и предоставил скрин с возможным выбором типов данных. Итак, выбор не богат, но вполне достаточен.
Смотрим в документацию по PostrgeSQL, и видим, что тип Character varyng — это синоним для varchar. Собственно, на английском SO это мнение разделяют 🙂

Итого, тип character varing, изображенный на скриншоте, чувствителен к регистру букв и может иметь заданную размерность, которая задается в поле Lenght в интерфейсе pgAdmin 4, что привел автор вопроса.

Более того, этот тип является частью стандарта SQL, и, вдруг, если [deleted]завтра война[/deleted], потребуется переезд на другую платформу (читай — на другой движок БД), то проблем с переносом этого поля не будет (как и многих других сложностей, если бы тип был бы уникальным только лишь для Postgres).

Стало быть, его и надо использовать 🙂

А вообще, можно еще навесить на поле констрейнты или триггеры, которые бы при вводе/изменении данных в поле для хранения e-mail проверяли бы его валидность. Но это уже тема для отдельного вопроса. Хотя, если хотите, то можно и проверять регуляркой, например. Хотя лично сам я больше склоняюсь в пользу такого решения по проверке валидности адреса (использовать почтовый ящик с указанным адресом как часть регистрационного процесса), хотя, разумеется, такой вариант может быть не всегда удобен.

Как итог — используйте character varyng, он же varchar и не парьтесь. 🙂

PostgreSQL: Документация: 9.5: Типы данных

PostgreSQL: Документация: 11: Глава 8.Типы данных

Поддерживаемые типы и их сопоставления

Ниже перечислены встроенные сопоставления при чтении и записи типов CLR в типы PostgreSQL.

Обратите внимание, что в дополнение к приведенному ниже, enum и составные сопоставления задокументированы на отдельной странице. Также обратите внимание, что существует несколько плагинов для добавления поддержки большего количества сопоставлений (например, пространственная поддержка PostGIS), они перечислены в меню Типы.

Чтение сопоставлений

Ниже показаны сопоставления, используемые при чтении значений.

• Тип по умолчанию возвращается при использовании NpgsqlCommand.ExecuteScalar () , NpgsqlDataReader.GetValue () и подобных методов.
• Вы можете читать как другие типы, вызвав NpgsqlDataReader.GetFieldValue () .
• Типы, зависящие от поставщика, возвращаются NpgsqlDataReader.GetProviderSpecificValue () .

По умолчанию.Столбец типа NET указывает тип данных , который будет возвращать NpgsqlDataReader.GetValue () .

NpgsqlDataReader.GetProviderSpecificValue вернет значение типа данных, указанного в столбце типа, зависящего от поставщика, или типа .NET по умолчанию, если нет специализации.

Наконец, третий столбец определяет другие типы среды CLR, которые Npgsql поддерживает для типа данных PostgreSQL. Их можно получить, вызвав NpgsqlDataReader.GetBoolean () , GetByte () , GetDouble () и т. Д.или через GetFieldValue () .

Запись сопоставлений

Есть три правила, которые определяют тип PostgreSQL, отправляемый для параметра:

1. Если установлен параметр NpgsqlDbType , он используется.
2. Если установлен параметр DataType , он используется.
3. Если установлен параметр DbType , он используется.
4. Если ничего из вышеперечисленного не установлено, тип серверной части будет выведен из типа значения CLR.

Обратите внимание, что для DateTime и NpgsqlDateTime атрибут Kind определяет, использовать ли временную метку или timestamptz .

Примечания при использовании диапазона и массива, побитового или NpgsqlDbType.Range или NpgsqlDbType.Array с дочерним типом. Например, чтобы создать NpgsqlDbType для int4range , напишите NpgsqlDbType.Range | NpgsqlDbType.Integer . Чтобы построить NpgsqlDbType для int [] , напишите NpgsqlDbType.Array | NpgsqlDbType.Integer .

Для получения информации о перечислениях см. Страницу «Перечисления и составы».

MySQL :: Руководство по MySQL Workbench :: 10.6.4 Сопоставление типов PostgreSQL

10.6.4 Отображение типов PostgreSQL

В следующей таблице показано соответствие между PostgreSQL.
(исходные) типы данных и типы данных MySQL.

Таблица 10.3 Отображение типов

Различия в типах данных между Amazon Redshift и поддерживаемыми базами данных PostgreSQL и MySQL

В следующей таблице показано сопоставление типа данных Amazon Redshift с соответствующим
Тип данных Amazon RDS PostgreSQL или Aurora PostgreSQL.

Следующие типы данных RDS PostgreSQL и Aurora PostgreSQL преобразуются в VARCHAR (64 КБ)
в
Amazon Redshift:

• JSON, JSONB

• Массивы

• БИТ, РАЗЛИЧНЫЙ БИТ

• BYTEA

• Составные типы

• Типы даты и времени INTERVAL, TIME, TIME WITH TIMEZONE

• Перечислимые типы

• Типы денежных средств

• Типы сетевых адресов

• Числовые типы SERIAL, BIGSERIAL, SMALLSERIAL и MONEY

• Типы идентификаторов объектов

• pg_lsn тип

• Псевдотипы

• Типы диапазонов

• Типы текстового поиска

• TXID_SNAPSHOT

• UUID

• Тип XML

В следующей таблице показано сопоставление типа данных Amazon Redshift с соответствующим
Тип данных Amazon RDS MySQL или Aurora MySQL.

Следующие типы данных RDS MySQL и Aurora MySQL преобразуются в VARCHAR (64 КБ).
в
Amazon Redshift:

• БИТ

• ДВОИЧНЫЙ

• ПЕРЕМЕННАЯ

• TINYBLOB, BLOG, MEDIUMBLOB, LONGBLOB

• TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT

• ENUM

• НАБОР

• ПРОСТРАНСТВЕННЫЙ

• ВРЕМЯ

• ГОД

• ДАТА

Типы данных PostgreSQL

Типы данных PostgreSQL

Когда вы настраиваете соединение с внешним
источник данных, Spotfire должен сопоставить типы данных в источнике данных с
типы данных в Spotfire.См. Ниже список различных типов данных.
сопоставления, применимые при работе с базой данных PostgreSQL.

Любые другие типы данных не поддерживаются.

См.
также:

Добавление
Связь данных с анализом

Детали
при подключении к PostgreSQL

Поддерживаемые типы данных — драйвер ODBC для PostgreSQL

Драйвер ODBC

для PostgreSQL поддерживает все типы данных PostgreSQL.

Полный список типов данных PostgreSQL доступен в документации PostgreSQL.

Отображение типов данных

В следующей таблице описано, как типы данных ODBC сопоставляются с типами данных PostgreSQL.

.