Postgresql: разработка расширений (функций) на языке с
Содержание:
- Q2: way to measure page size
- JSONB
- История
- Производные продукты
- Числовые типы данных
- Изменение таблиц
- Что такое функции в SQL?
- Прочие возможности
- Write mappings
- Типы данных
- Chapter 8. Типы данных
- Поддержка стандартов, возможности, особенности
- Read mappings
- Как отобразить список всех баз данных сервера PostgreSQL?
- Работа с данными и полями таблиц
- Создание функций на PL/pgSQL
- Заключение
Q2: way to measure page size
PostgreSQL предоставляет ряд , вы можете использовать. Я собрал самые интересные из них в этом запросе и добавил некоторые .
Это продемонстрирует, что различные методы измерения «размера строки» могут привести к очень различным результатам. Все зависит от того, что вы хотите точно измерить.
Замените на ваше (необязательно, с помощью схемы) имя таблицы, чтобы получить компактный просмотр собранной статистики о размере ваших строк.
Я снова упаковываю значения в массивы и , поэтому мне не нужно многократно выставлять вычисления для каждой отдельной строки.
Общая статистика count строк добавляется в конце с нетрадиционным SQL-foo, чтобы получить все в одном запросе. Вы можете перенести его в функцию plpgsql для повторного использования, указать имя таблицы в качестве параметра и использовать .
Результат:
что | bytes /ct | bytes_pretty | bytes_per_row ----------------------------------- + ---------- + --- ----------- + --------------- core_relation_size | 44138496 | 42 МБ | 91 visibility_map | 0 | 0 байт | 0 free_space_map | 32768 | 32 kB | 0 table_size_incl_toast | 44179456 | 42 МБ | 91 indexes_size | 33128448 | 32 МБ | 68 total_size_incl_toast_and_indexes | 77307904 | 74 МБ | 159 live_rows_in_text_representation | 29987360 | 29 МБ | 62 ------------------------------ | | | row_count | 483424 | | live_tuples | 483424 | | dead_tuples | 2677 | |
Дополнительный модуль pgstattuple обеспечивает более полезные функции.
Обновление для Postgres 9.3 +
Мы могли бы использовать новую форму в стр. 9.4, используя несколько параметров для параллельной работы с массивами.
Но используя и выражение , это может быть упрощены далее. Кроме того, некоторые другие улучшения:
Тот же результат.
JSONB
Наконец, в Postgres 9.4 мы получили настоящую и правильную поддержку JSON в виде JSONB. B означает “лучше” (Better). JSONB — это бинарное представление данных в формата JSON. Это означает, что данные сжимается и более эффективны для хранения, чем обычный текст. Кроме того, под капотом у него механизм, подобный Hstore. Технически, когда-то при разработке, был почти реализованный тип Hstore2 и отдельный тип JSON и впоследствии они были объединены в JSONB в том виде, как он есть сейчас.
Тип JSONB представляет в значительной степени то, что вы могли бы ожидать от типа данных JSON. Он позволяет реализовывать вложенные структуры, использовать основные типы данных, а также имеет ряд встроенных функций для работы с ним. Лучшей частью такой схожести с Hstore является индексация. Создание индекса GIN на колонке JSONB создаст индекс по каждому ключу и значению в пределах этого документа JSON. Возможность индексации и вложенность данных внутри документа означают, что JSONB превосходит Hstore в большинстве случаев.
Хотя все еще остаётся небольшой вопрос о том, в каких случаях следует использовать исключительно JSONB. Допустим, вы создаете базу данных документов и из всех вариантов выбираете Postgres. С пакетом, наподобие MassiveJS это может быть довольно удобным.
Наиболее распространенные примеры использования:
- Отслеживание событий данных, добавляя изменяющийся payload события.
- Хранение игровых данные достаточно распространено, особенно там, где у вас есть одиночная игра и изменяющаяся схема данных на основе состояния пользователя.
- Инструменты, которые объединяют несколько источников данных, пример здесь может быть инструментом, который интегрирует несколько баз данных клиентов к Salesforce, к Zendesk или к чему-то еще. Сочетание схем делает это более болезненной процедурой, чем она должна быть.
Давайте рассмотрим, другой пример работы с JSONB. Скрипт создает таблицу и вставляет некоторые данные для примера:
История
PostgreSQL создана на основе некоммерческой СУБД Postgres, разработанной как open-source проект в Калифорнийском университете в Беркли. К разработке Postgres, начавшейся в 1986 году, имел непосредственное отношение Майкл Стоунбрейкер, руководитель более раннего проекта Ingres, на тот момент уже приобретённого компанией Computer Associates. Название расшифровывалось как «Post Ingres», и при создании Postgres были применены многие уже ранее сделанные наработки.
Стоунбрейкер и его студенты разрабатывали новую СУБД в течение восьми лет с по 1994 год. За этот период в синтаксис были введены процедуры, правила, пользовательские типы и другие компоненты. В 1995 году разработка снова разделилась: Стоунбрейкер использовал полученный опыт в создании коммерческой СУБД Illustra, продвигаемой его собственной одноимённой компанией (приобретённой впоследствии компанией Informix), а его студенты разработали новую версию Postgres — Postgres95, в которой язык запросов POSTQUEL — наследие Ingres — был заменен на SQL.
Разработка Postgres95 была выведена за пределы университета и передана команде энтузиастов. Новая СУБД получила имя, под которым она известна и развивается в текущий момент — PostgreSQL.
Производные продукты
Лицензия PostgreSQL позволяет на его основе создавать различные, в том числе коммерческие, форки. Их известно несколько десятков .
На базе PostgreSQL компанией EnterpriseDB были разработаны другие варианты этой СУБД, являющиеся платными для коммерческого использования — Postgres Plus (состоит целиком только из продуктов с открытыми исходными кодами; плата требуется только при необходимости приобретения коммерческой поддержки продукта) и Postgres Plus Advanced Server (расширение PostgreSQL специальными возможностями для обеспечения совместимости с Oracle Database). В комплекте поставки данных продуктов содержится набор ПО для разработчиков и администраторов баз данных:
- Postgres Studio — аналог phpPgAdmin;
- Postgres Plus Debugger — отладчик для кода на PL/pgSQL, интегрированный с предыдущим пакетом;
- Migration Studio — инструмент для автоматического преобразования баз данных из MySQL/Oracle в PostgreSQL
В 2015 году российская компания Postgres Professional выпустила свою СУБД Postgres Pro, также являющуюся коммерческим форком PostgreSQL. В варианте Standard СУБД распространяется с исходными кодами и представляет собой версию PostgreSQL с патчами для ядра PostgreSQL, которые уже приняты сообществом и внесены в основную ветку, а также с собственными расширениями, разработанными компанией Postgres Professional. Таким образом обеспечивается доступ к полезной функциональности и обновлениям почти за год до выхода нового релиза PostgreSQL. В варианте Enterprise данная СУБД содержит бо́льшее количество дополнений, предназначенных для работы с большими, высокопроизводительными базами данных с повышенными требованиями к надежности.
Числовые типы данных
Ниже приведены числовые типы данных в PostgreSQL:
| Синтаксис типов данных | Пояснение |
|---|---|
| bit(size) | Битовая строка фиксированной длины, где size — длина строки битов. |
| varbit(size) bit varying(size) | Битовая строка переменной длины, где size — длина строки битов. |
| smallint | Эквивалентно int2. 2-байтовое целое число со знаком. |
| int | Эквивалентно int4. 4-байтовое целое число со знаком. |
| integer | Эквивалентно int4. 4-байтовое целое число со знаком. |
| bigint | Большое целочисленное значение, эквивалентное int8. 8-байтовое целое число со знаком. |
| smallserial | Небольшое целочисленное значение с автоинкрементом, эквивалентное serial2. 2-байтовое целое число со знаком, автоинкрементное. |
| serial | Автоинкрементное целочисленное значение, эквивалентное serial4. 4-байтовое целое число со знаком, автоинкрементное. |
| bigserial | Большое автоинкрементное целочисленное значение, эквивалентное serial8. 8-байтовое целое число со знаком, автоинкрементное. |
| numeric(m,d) | Где m — это общее количество цифр, а d — это число после десятичной дроби. |
| double precision | 8 байт, двойная точность, число с плавающей точкой |
| real | 4-байтовое число одинарной точности с плавающей точкой |
| money | Стоимость валюты. |
| bool | Логический логический тип данных — true или false |
| boolean | Логический логический тип данных — true или false |
Изменение таблиц
Последнее обновление: 19.03.2018
Нередко возникает изменить уже имеющуюся таблицу, в частности, добавить или удалить столбцы, изменить тип столбцов и т.д..
То есть потребуется изменить определение таблицы. Для этого применяется выражение ALTER TABLE,
которое имеет следующий формальный синтаксис:
ALTER TABLE название_таблицы
{ ADD название_столбца тип_данных_столбца |
DROP COLUMN название_столбца |
ALTER COLUMN название_столбца параметры_столбца |
ADD определение_ограничения |
DROP имя_ограничения}
Рассмотрим некоторые возможности по изменению таблицы.
Добавление нового столбца
Добавим в таблицу Customers новый столбец Phone:
ALTER TABLE Customers ADD Phone CHARACTER VARYING(20) NULL;
Здесь столбец Phone имеет тип , и для него определен атрибут , то есть
столбец допускает отсутствие значения. Но что если нам надо добавить столбец, который не должен принимать значения NULL? Если в таблице есть
данные, то следующая команда не будет выполнена:
ALTER TABLE Customers ADD Address CHARACTER VARYING(30) NOT NULL;
Поэтому в данном случае решение состоит в установке значения по умолчанию через атрибут :
ALTER TABLE Customers ADD Address CHARACTER VARYING(30) NOT NULL DEFAULT 'Неизвестно';
Удалим столбец Address из таблицы Customers:
ALTER TABLE Customers DROP COLUMN Address;
Изменение типа столбца
Для изменения типа применяется ключевое слово TYPE. Изменим в таблице Customers тип данных у столбца FirstName на (он же ):
ALTER TABLE Customers ALTER COLUMN FirstName TYPE VARCHAR(50);
Изменение ограничений столбца
Для добавления ограничения применяется оператор SET, после которого указывается ограничение.
Например, установим для столбца FirstName ограничение :
ALTER TABLE Customers ALTER COLUMN FirstName SET NOT NULL;
Для удаления ограничения применяется оператор DROP, после которого указывается ограничение.
Например, удалим выше установленное ограничение:
ALTER TABLE Customers ALTER COLUMN FirstName DROP NOT NULL;
Изменение ограничений таблицы
Добавление ограничения CHECK:
ALTER TABLE Customers ADD CHECK (Age > 0);
Добавление первичного ключа :
ALTER TABLE Customers ADD PRIMARY KEY (cust_id);
ALTER TABLE Customers ADD UNIQUE (Email);
При добавлении ограничения каждому из них дается определенное имя. Например, выше добавленное ограничение для CHECK будет называться
. Имена ограничений можно посмотреть в таблице через pgAdmin.
Также мы можем явным образом назначить ограничению при добавлении имя с помощью оператора CONSTRAINT.
ALTER TABLE Customers ADD CONSTRAINT phone_unique UNIQUE (Phone);
В данном случае ограничение будет называться «phone_unique».
Чтобы удалить ограничение, надо знать его имя, которое указывается после выражения . Например, удалим выше добавленное ограничение:
ALTER TABLE Customers DROP CONSTRAINT phone_unique;
Переименование столбца и таблицы
Переименуем столбец Address в City:
ALTER TABLE Customers RENAME COLUMN Address TO City;
Переименуем таблицу Customers в Users:
ALTER TABLE Customers RENAME TO Users;
НазадВперед
Что такое функции в SQL?
Функции – это объекты базы данных, которые используются для автоматизации и упрощения расчетов. Функции пишутся уже с использованием настоящего языка программирования в PostgreSQL — это PL/pgSQL.
PL/pgSQL – язык программирования, который используется в СУБД PostgreSQL для написания функций, триггеров и других управляющих конструкций.
Сразу скажу, что писать функции без базовых знаний SQL практически невозможно, поэтому советую ознакомиться с основами SQL, например, Вам помогут вот эти статьи:
- Основы языка запросов SQL
- Строковые функции SQL
- Как добавить новый столбец в таблицу на SQL?
Теперь давайте поговорим о том, для чего нам нужны эти самые функции. Как и в любом языке программирования есть «встроенные» функции, а также есть возможность писать свои «пользовательские» функции и PL/pgSQL не исключение, если быть точнее, то SQL не исключение, так как PL/pgSQL является расширением языка SQL.
В функциях мы можем легко прописывать условия, как и в других языках программирования, в функцию можно передавать параметры и соответственно она может нам возвращать результат, после выполнения действий с входящими параметрами. Одной из главных особенностей функций является то, что при обращении к базе данных из приложения все действия происходят на сервере, что гораздо быстрей, т.е. эффективней. Если не использовать вызов функций из приложения, то обращаться к базе данных придется несколько раз, что в свою очередь замедляет работу.
Также, например, можно написать функции, которые будут просто упрощать написание запросов на SQL. Другими словами, можно прописать в функции какое-нибудь действие, которое Вы часто используете при написании запросов и просто вызывать ее в самом запросе. Тем самым запрос становится намного короче, проще и быстрей выполняется, а также к запросу добавляется динамика, так как функции могут быть динамические, т.е. возвращать разный результат в зависимости от входящего параметра или изменений в базе.
В функциях в СУБД PostgreSQL можно использовать все операторы SQL такие как: INSERT, DELETE, UPDATE и другие. И Вы теперь представьте, что можно прописать в функции!? а потом просто вызвать ее одной строкой.
Прочие возможности
- Соблюдение принципов ACID
- Соответствие стандартам ANSI SQL-92, SQL-99, SQL:2003, SQL:2011
- Поддержка запросов с , , , , и подзапросов
- Последовательности
- Контроль целостности
- Репликация
- Общие табличные выражения и рекурсивные запросы
- Аналитические функции
- Поддержка Юникода (UTF-8)
- Поддержка регулярных выражений в стиле Perl
- Встроенная поддержка SSL, SELinux и Kerberos
- Протокол разделяемых блокировок
- Подгружаемые расширения, поддерживающие SHA1, MD5, XML
- Расширения для написания сложных выборок, отчётов и т. д. (API открыт)
- Средства для генерации совместимого с другими системами SQL-кода и импорта из других систем
- Автономные блоки на доступных языках, а не только SQL
Write mappings
There are three rules that determine the PostgreSQL type sent for a parameter:
- If the parameter’s is set, it is used.
- If the parameter’s is set, it is used.
- If the parameter’s is set, it is used.
- If none of the above is set, the backend type will be inferred from the CLR value type.
Note that for and , the attribute determines whether to use or .
| NpgsqlDbType | DbType | PostgreSQL type | Accepted .NET types |
|---|---|---|---|
| Boolean | Boolean | boolean | bool |
| Smallint | Int16 | smallint | short |
| Integer | Int32 | integer | int |
| Bigint | Int64 | bigint | long |
| Real | Single | real | float |
| Double | Double | double precision | double |
| Numeric | Decimal, VarNumeric | numeric | decimal |
| Money | Currency | money | decimal |
| Text | String, StringFixedLength, AnsiString, AnsiStringFixedLength | text | string, char[], char |
| Varchar | character varying | string, char[], char | |
| Char | character | string, char[], char | |
| Citext | citext | string, char[], char | |
| Json | json | string, char[], char | |
| Jsonb | jsonb | string, char[], char | |
| Xml | xml | string, char[], char | |
| Point | point | NpgsqlPoint | |
| LSeg | lseg | NpgsqlLSeg | |
| Path | path | NpgsqlPath | |
| Polygon | polygon | NpgsqlPolygon | |
| Line | line | NpgsqlLine | |
| Circle | circle | NpgsqlCircle | |
| Box | box | NpgsqlBox | |
| Bit | bit | BitArray, bool, string | |
| Varbit | bit varying | BitArray, bool, string | |
| Hstore | hstore | IDictionary<string, string> | |
| Uuid | uuid | Guid | |
| Cidr | cidr | ValueTuple<IPAddress, int>, IPAddress, NpgsqlInet | |
| Inet | inet | ValueTuple<IPAddress, int>, IPAddress, NpgsqlInet | |
| MacAddr | macaddr | PhysicalAddress | |
| TsQuery | tsquery | NpgsqlTsQuery | |
| TsVector | tsvector | NpgsqlTsVector | |
| Date | Date | date | DateTime, NpgsqlDate |
| Interval | interval | TimeSpan, NpgsqlTimeSpan | |
| Timestamp | DateTime, DateTime2 | timestamp without time zone | DateTime, NpgsqlDateTime |
| TimestampTz | DateTimeOffset | timestamp with time zone | DateTime, DateTimeOffset, NpgsqlDateTime |
| Time | Time | time without time zone | TimeSpan |
| TimeTz | time with time zone | DateTimeOffset, DateTime, TimeSpan | |
| Bytea | Binary | bytea | byte[], ArraySegment<byte> |
| Oid | oid | uint | |
| Xid | xid | uint | |
| Cid | cid | uint | |
| Oidvector | oidvector | uint[] | |
| Name | name | string, char[], char | |
| InternalChar | (internal) char | byte | |
| Composite | composite types | T | |
| Range | (other NpgsqlDbType) | range types | NpgsqlRange<TElement> | |
| Enum | enum types | TEnum | |
| Array | (other NpgsqlDbType) | array types | Array, IList<T>, IList |
Notes when using Range and Array, bitwise-or NpgsqlDbType.Range or NpgsqlDbType.Array with the child type. For example, to construct the NpgsqlDbType for a , write . To construct the NpgsqlDbType for an , write .
For information about enums, see the Enums and Composites page.
| .NET type | Auto-inferred PostgreSQL type |
|---|---|
| bool | boolean |
| byte | smallint |
| sbyte | smallint |
| short | smallint |
| int | integer |
| long | bigint |
| float | real |
| double | double precision |
| decimal | numeric |
| string | text |
| char[] | text |
| char | text |
| NpgsqlPoint | point |
| NpgsqlLSeg | lseg |
| NpgsqlPath | path |
| NpgsqlPolygon | polygon |
| NpgsqlLine | line |
| NpgsqlCircle | circle |
| NpgsqlBox | box |
| BitArray | bit varying |
| Guid | uuid |
| IPAddress | inet |
| NpgsqlInet | inet |
| PhysicalAddress | macaddr |
| NpgsqlTsQuery | tsquery |
| NpgsqlTsVector | tsvector |
| NpgsqlDate | date |
| NpgsqlDateTime | timestamp without time zone |
| DateTime | timestamp without time zone |
| DateTimeOffset | timestamp with time zone |
| TimeSpan | interval |
| byte[] | bytea |
| Custom composite type | composite types |
| NpgsqlRange<TElement> | range types |
| Enum types | enum types |
| Array types | array types |
Типы данных
Пнд, 12/15/2008 — 10:33 — admin
| Документация по PostgreSQL 8.4devel | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Fast Forward | Next |
Chapter 8. Типы данных
- Table of Contents
- 8.1. Числовые типы
-
- 8.1.1.
- 8.1.2.
- 8.1.3.
- 8.1.4.
- 8.2. Денежные типы
- 8.3. Символьные типы
- 8.4. Двоичные типы данных
- 8.5. Типы дата/времени
-
- 8.5.1.
- 8.5.2.
- 8.5.3.
- 8.5.4.
- 8.5.5.
- 8.5.6.
- 8.6. Логический тип
- 8.7. Перечисления
-
- 8.7.1.
- 8.7.2.
- 8.7.3.
- 8.7.4.
- 8.8. Геометрические типы
-
- 8.8.1.
- 8.8.2.
- 8.8.3.
- 8.8.4.
- 8.8.5.
- 8.8.6.
- 8.9. Типы сетевых адресов
-
- 8.9.1.
- 8.9.2.
- 8.9.3.
- 8.9.4.
- 8.10. Типы битовых строк
- 8.11. Типы текстового поиска
-
- 8.11.1.
- 8.11.2.
- 8.12. Тип UUID
- 8.13. Тип XML
-
- 8.13.1.
- 8.13.2.
- 8.13.3.
- 8.14. Arrays
-
- 8.14.1.
- 8.14.2.
- 8.14.3.
- 8.14.4.
- 8.14.5.
- 8.14.6.
- 8.15. Composite Types
-
- 8.15.1.
- 8.15.2.
- 8.15.3.
- 8.15.4.
- 8.15.5.
- 8.16. Типы идентификаторов объектов
- 8.17. Псевдо-типы
PostgreSQL имеет богатый список доступных
пользователю встроенных типов. Кроме того, с помощью команды
CREATE TYPE
пользователи могут добавлять новые типы данных.
показывает все встроенные типы данных
общего назначения. Большая часть альтернативных имён, перечисленных
в колонке «Псевдонимы», используется в
PostgreSQL по историческим причинам.
Кроме того, доступны, но не указаны, некоторые типы данных,
которые используются для внутренних нужд PostgreSQL
или которые устарели.
Table 8-1. Типы данных
| Имя | Псевдонимы | Описание |
|---|---|---|
| bigint | int8 | знаковое восьмибайтное целое число |
| bigserial | serial8 | восьмибайтное целое число с автоинкрементом |
| bit [ (n) ] | битовая строка с фиксированной длиной | |
| bit varying [ (n) ] | varbit | битовая строка с переменной длиной |
| boolean | bool | логическое значение (истина/ложь) |
| box | четырёхугольник на плоскости | |
| bytea | двоичные данные («массив байт») | |
| character varying [ (n) ] | varchar [ (n) ] | строка с переменной длиной |
| character [ (n) ] | char [ (n) ] | строка с фиксированной длиной |
| cidr | адрес сети IPv4 или IPv6 | |
| circle | круг на плоскости | |
| date | календарная дата (год, месяц, день) | |
| double precision | float8 | число с плавающей точкой двойной точности |
| inet | адрес узла IPv4 или IPv6 | |
| integer | int, int4 | знаковое четырёхбайтовое целое |
| interval [ fields ] [ (p) ] | промежуток времени | |
| line | бесконечная линия на плоскости | |
| lseg | сегмент линии на плоскости | |
| macaddr | MAC адрес | |
| money | денежное значение (в валюте) | |
| numeric [ (p, s) ] |
decimal [ (p, s) ] |
точное числовое значение с выбраной точностью |
| path | геометрический путь на плоскости (ломаная) | |
| point | геометрическая точка на плоскости | |
| polygon | закрытый геометрический путь на плоскости (полигон) | |
| real | float4 | число с плавающей точкой одинарной точности |
| smallint | int2 | знаковое двухбайтное целое число |
| serial | serial4 | четырёхбайтное целое число с автоинкрементом |
| text | строка символов перменной длины | |
| time [ (p) ] | время дня | |
| time [ (p) ] with time zone | timetz | время дня, включая часовой пояс |
| timestamp [ (p) ] | дата и время | |
| timestamp [ (p) ] with time zone | timestamptz | дата и время, включая часовой пояс |
| tsquery | запрос текстового поиска | |
| tsvector | документ текстового поиска | |
| txid_snapshot | снимок ID транзакции уровня пользователя | |
| uuid | универсальный уникальный идентификатор | |
| xml | данные XML |
Каждый тип данных имеет внешнее представление, определяемое
с помощью функций ввода и вывода. Многие из встроенных типов
имеют понятные внешние форматы. Однако, некоторые типы являются
либо уникальными в PostgreSQL, такие
как геометрические, либо имеют несколько возможных форматов, такие
как типы даты и времени. Некоторые функции ввода и вывода являются
неинвертирумыми. К ним относятся такие функции, результат вывода
которых может привести к потере точности по сравнению с введённым
первоначальным значением.
| Prev | Home | Next |
| WITH Queries | Up | Числовые типы |
fetched
Поддержка стандартов, возможности, особенности
PostgreSQL базируется на языке SQL и поддерживает многие из возможностей стандарта SQL:2011.
В PostgreSQL версии 9.5.3 есть следующие параметры:
| Максимальный размер базы данных | Нет ограничений |
| Максимальный размер таблицы | 32 Тбайт |
| Максимальный размер записи | 1,6 Тбайт |
| Максимальный размер поля | 1 Гбайт |
| Максимум записей в таблице | Нет ограничений |
| Максимум полей в записи | 250—1600, в зависимости от типов полей |
| Максимум индексов в таблице | Нет ограничений |
Сильными сторонами PostgreSQL считаются:
- высокопроизводительные и надёжные механизмы транзакций и репликации;
- расширяемая система встроенных языков программирования: в стандартной поставке поддерживаются PL/pgSQL, PL/Perl, PL/Python и PL/Tcl; дополнительно можно использовать PL/Java, PL/PHP, PL/Py, PL/R, PL/Ruby, PL/Scheme, PL/sh и PL/V8, а также имеется поддержка загрузки C-совместимых модулей;
- наследование;
- легкая расширяемость.
Read mappings
The following shows the mappings used when reading values.
- The default type is returned when using , and similar methods.
- You can read as other types by calling .
- Provider-specific types are returne by .
| PostgreSQL type | Default .NET type | Provider-specific type | Other .NET types |
|---|---|---|---|
| boolean | bool | ||
| smallint | short | byte, sbyte, int, long, float, double, decimal | |
| integer | int | byte, short, long, float, double, decimal | |
| bigint | long | long, byte, short, int, float, double, decimal | |
| real | float | double | |
| double precision | double | ||
| numeric | decimal | byte, short, int, long, float, double | |
| money | decimal | ||
| text | string | char[] | |
| character varying | string | char[] | |
| character | string | char[] | |
| citext | string | char[] | |
| json | string | char[] | |
| jsonb | string | char[] | |
| xml | string | char[] | |
| point | NpgsqlPoint | ||
| lseg | NpgsqlLSeg | ||
| path | NpgsqlPath | ||
| polygon | NpgsqlPolygon | ||
| line | NpgsqlLine | ||
| circle | NpgsqlCircle | ||
| box | NpgsqlBox | ||
| bit(1) | bool | BitArray | |
| bit(n) | BitArray | ||
| bit varying | BitArray | ||
| hstore | Dictionary<string, string> | ||
| uuid | Guid | ||
| cidr | (IPAddress, int) | NpgsqlInet | |
| inet | IPAddress | (IPAddress, int) | NpgsqlInet |
| macaddr | PhysicalAddress | ||
| tsquery | NpgsqlTsQuery | ||
| tsvector | NpgsqlTsVector | ||
| date | DateTime | NpgsqlDate | |
| interval | TimeSpan | NpgsqlTimeSpan | |
| timestamp without time zone | DateTime (Unspecified) | NpgsqlDateTime | |
| timestamp with time zone | DateTime (Local) | NpgsqlDateTime | DateTimeOffset |
| time without time zone | TimeSpan | ||
| time with time zone | DateTimeOffset | DateTimeOffset, DateTime, TimeSpan | |
| bytea | byte[] | ||
| oid | uint | ||
| xid | uint | ||
| cid | uint | ||
| oidvector | uint[] | ||
| name | string | char[] | |
| (internal) char | char | byte, short, int, long | |
| geometry (PostGIS) | PostgisGeometry | ||
| record | object[] | ||
| composite types | T | ||
| range subtypes | NpgsqlRange<TElement> | ||
| enum types | TEnum | ||
| array types | Array (of element type) |
The Default .NET type column specifies the data type will return.
will return a value of a data type specified in the Provider-specific type column, or the Default .NET type if there is no specialization.
Finally, the third column specifies other CLR types which Npgsql supports for the PostgreSQL data type. These can be retrieved by calling , , etc. or via .
Как отобразить список всех баз данных сервера PostgreSQL?
Для данной цели существует специальная команда в консольной утилите:
# \l
Результат:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+------------+-----------+-------------+-------------+---------------------------
sampledb | sampleuser | UTF8 | uk_UA.UTF-8 | uk_UA.UTF-8 | =Tc/sampleuser
| | | | | sampleuser=CTc/sampleuser
postgres | postgres | UTF8 | uk_UA.UTF-8 | uk_UA.UTF-8 |
template0 | postgres | UTF8 | uk_UA.UTF-8 | uk_UA.UTF-8 | =c/postgres
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | uk_UA.UTF-8 | uk_UA.UTF-8 | postgres=CTc/postgres
| | | | | pgsql=CTc/postgres
| | | | | =c/postgres
Работа с данными и полями таблиц
Удаление одинаковых строк
Если так получилось, что в таблице нет первичного ключа (primary key), то наверняка среди записей найдутся дубликаты. Если для такой таблицы, особенно большого размера, необходимо поставить ограничения (constraint) для проверки целостности, то удалим следующие элементы:
- дублирующиеся строки,
- ситуации, когда одна или более колонок дублируются (если эти колонки предполагается использовать в качестве первичного ключа).
Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).
Удалить все дубликаты поможет следующий запрос:
Уникальное для каждой записи поле по умолчанию скрыто, но оно есть в каждой таблице.
Последний запрос требователен к ресурсам, поэтому будьте аккуратны при его выполнении на рабочем проекте.
Вебинар «Производительность 1С на PostgreSQL в Яндекс.Облаке»
27 августа в 12:00 в 12:00, онлайн, беcплатно
tproger.ru
События и курсы на tproger.ru
Теперь рассмотрим случай, когда повторяются значения полей.
Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:
Если данные важны, то сначала нужно найти записи с дубликатами:
Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение на другое.
Общая форма запроса на удаление описанных выше записей выглядит следующим образом:
Безопасное изменение типа поля
Может возникнуть вопрос о включении в этот список такой задачи. Ведь в PostgreSQL изменить тип поля очень просто с помощью команды . Давайте для примера снова рассмотрим таблицу с покупателями.
Для поля используется строковый тип данных . Это ошибка, так как в этом поле предполагается хранить идентификаторы покупателей, которые имеют целочисленный формат . Использование неоправданно. Попробуем исправить это недоразумение с помощью команды :
Но в результате выполнения получим ошибку:
Это значит, что нельзя просто так взять и изменить тип поля при наличии данных в таблице. Так как использовался тип , СУБД не может определить принадлежность значения к . Хотя данные соответствуют именно этому типу. Для того, чтобы уточнить этот момент, в сообщении об ошибке предлагается использовать выражение , чтобы корректно преобразовать наши данные в :
В результате всё прошло без ошибок:
Обратите внимание, что при использовании кроме конкретного выражения возможно использование функций, других полей и операторов. Например, преобразуем поле обратно в , но с преобразованием формата данных:
Например, преобразуем поле обратно в , но с преобразованием формата данных:
В результате таблица примет следующий вид:
Поиск «потерянных» значений
Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.
Рассмотрим два варианта поиска.
Первый способ
Выполним следующий запрос, чтобы найти начало интервала с «потерянным» значением:
В результате получим значения: , и .
Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:
В результате видим следующий результат: , и .
Второй способ
Получаем имя последовательности, связанной с :
И находим все пропущенные идентификаторы:
Подсчёт количества строк в таблице
Количество строк вычисляется стандартной функцией , но её можно использовать с дополнительными условиями.
Общее количество строк в таблице:
Количество строк при условии, что указанное поле не содержит :
Количество уникальных строк по указанному полю:
Использование транзакций
Транзакция объединяет последовательность действий в одну операцию. Её особенность в том, что при ошибке в выполнении транзакции ни один из результатов действий не сохранится в базе данных.
Начнём транзакцию с помощью команды .
Для того, чтобы откатить все операции, расположенные после , используем команду .
А чтобы применить — команду .
Просмотр и завершение исполняемых запросов
Для того, чтобы получить информацию о запросах, выполним следующую команду:
Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):
Для того, чтобы прекратить работу запроса, выполним:
Создание функций на PL/pgSQL
Теперь давайте будем учиться писать эти самые функции. И для начала ниже представлен общий синтаксис написания функции в PL/pgSQL:
CREATE OR REPLACE FUNCTION название функции (типы передаваемых данных через запятую)
RETURNS тип возвращаемого значения AS
$BODY$
DECLARE
Объявление переменных
BEGIN
Тело программы
RETURN возвращаемый результат;
END;
$BODY$
LANGUAGE язык, на котором написана функция (например, SQL или plpgsql) VOLATILE
Тип возвращаемого значения может быть разный, например, numeric, integer, text или, например void это тип, который не возвращает значение, а функция просто отрабатывает (например, добавляет новые строки).
Сейчас давайте напишем простенькую функцию, пока без использования plpgsql. Допустим, у нас есть две таблицы с одинаковой строуктурой: test и test1:
Test1
| id | name | flag | znach |
| 1 | mike | 1 | 10 |
| 2 | peter | 15 |
и Test2
| id | number | name2 | tarif |
| 1 | 111 | mike1 | 2 |
| 2 | 222 | peter1 | 3 |
Например, мы часто используем в запросах объединение этих двух таблиц, для того чтобы подтянуть к таблице test1, столбец number из таблицы test2. Для упрощения всего этого давайте напишем простенькую функцию:
CREATE OR REPLACE FUNCTION "work".test_number(numeric)
RETURNS numeric AS
$BODY$
SELECT "number"
FROM "work"."test2"
WHERE id = $1;
$BODY$
LANGUAGE 'sql' VOLATILE
Где,
- test_number — название функции;
- numeric — тип входящего параметра (он у нас один, но их может быть много);
- SELECT «number» FROM «work».»test2″ WHERE id = $1 – запрос, т.е. тело функции. $1 передаваемый параметр;
- LANGUAGE ‘sql’ VOLATILE – язык, на котором написана функция.
Теперь давайте вызовем нашу функцию в запросе, это проще простого:
SELECT *, "work".test_number(id) AS Number FROM import.test1
где, «work».test_number(id) – это и есть вызов нашей функции. Мы передали ему параметр id, т.е. целую колонку, результат будет таким:
| id | name | flag | znach | number |
| 1 | mike | 1 | 10 | 111 |
| 2 | peter | 15 | 222 |
Если нужно кого-то конкретного подтянуть, можно написать вот так, т.е. передать параметр 1 (id=1).
SELECT *, "work".test_number(1) AS Number FROM import.test1 WHERE id = 1
Таким образом, у нас выведется одна строка:
| id | name | flag | znach | number |
| 1 | mike | 1 | 10 | 111 |
Здесь мы с Вами обошлись без всякого рода объединений, согласитесь так намного проще, запрос короче и выполняется быстрей.
Теперь попробуем написать функцию уже с использованием языка PL/pgSQL. Допустим, мы хотим знать, кто в нашей таблице соответствует тому или иному признаку. Например, кто из наших сотрудников работает. Определять будем по признаку flag (1 – работает; 0 – не работает). При этом нам не удобно вспоминать чему соответствует этот признак, например 1, поэтому давайте напишем функцию, чтобы видеть надпись, напротив того или иного сотрудника. Пишем функцию:
CREATE OR REPLACE FUNCTION "work".test_if(numeric)
RETURNS text AS
$BODY$
DECLARE
val ALIAS FOR $1;
val1 integer;
val2 text;
BEGIN
val1 :=1;
IF val1 = val THEN
val2 := 'Он работает';
ELSE
val2 := 'Не работает';
END IF;
RETURN val2;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
Здесь мы уже используем объявление переменных, условие и возвращение значения.
Вызываем функцию.
SELECT *, "work".test_if(flag) AS Status FROM import.test1
Получаем результат:
| id | name | flag | znach | status |
| 1 | mike | 1 | 10 | Он работает |
| 2 | peter | 15 | Не работает |
Как Вы уже, наверное, поняли, что возможности функций практически не ограничены, на языке PL/pgSQL можно написать очень серьезные функции, с трудными расчетами и так далее. В данной заметке мы с Вами рассмотрели только основы написания, а дальше зависит только о Вас. Удачи!
Нравится1Не нравится
Заключение
В большинстве случаев JSONB это, вероятно, как раз то, что вы ищите, когда планируете использовать нереляционный тип данных. Для Hstore и JSON можно также найти хорошее применение хоть и в более редких случаях. JSONB не всегда вписывается в модель данных. В случае если вы можете нормализовать схему, то у вас будет преимущество, но если в схеме, большое количество опциональных столбцов (например, с данными о событиях) или одна схема сильно отличается от другой, то JSONB подходит гораздо лучше.
Резюмируя, алгоритм выбора решения:
JSONB — В большинстве случаев
JSON — Если вы обрабатываете логи, вам не часто приходится запрашивать данные или не нужно использовать их как что-то большее чем для задач логирования.
Hstore — отлично работает с текстовыми данными на основе представления ключ-значение, но в целом JSONB также отлично справляется с этой задачей.
