Как установить erwin на windows 10

Подсказки SQL

SQL — это пример языка, в котором одну задачу можно решить десятью различными способами. Разные эффективно. Приведенные здесь примеры демонстрируют, как эффективно использовать возможности PostgreSQL.

Контент

Массив документов типа jsonb

Автор: Павел Стегуле

В Postgres мы можем работать с полями любого типа данных, которые сами по себе не являются полями. Обозначение индекса в квадратных скобках используется для доступа. Начиная с PostgreSQL 14, мы также можем использовать эту нотацию для адресации значения в документе jsonb. Однако тут и начинается шизофреническая ситуация — нам нужно разделить индексы полей и индексы (позиционирование) в документе. К сожалению, Postgres этого сделать не может (ни на уровне парсера, ни внутри — массивы полей не поддерживаются). Но мы можем помочь, вставив составное значение. Этот трюк также используется, когда мы хотим работать с массивом полей. Напрямую это невозможно, но можно работать с массивом составных значений, содержащих массив:

Передача значения от клиента к серверу

Автор: Даниэль Верите

Переменная PGOPTION также может использоваться для переменных клиента:

или с экранированными значениями:

Запись всех команд из приложения

Автор: Павел Стегуле

Установите системную переменную PGOPTIONS — принудительный вход для всех команд pg_dump(u):

Очистка только системных таблиц

Автор: Павел Стегуле

Настройка элементов DETAIL и HINT для исключения PLPython

Автор: Pavel Stěhule PLPython не позволяет устанавливать все элементы, которые можно установить для исключений PostgreSQL. Однако элементы DETAIL и HINT могут быть установлены:

Псевдоним psql

Автор: Павел Стегуле

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

Примечание. То же самое можно сделать, установивсистемные переменные PGDATABASE .

Настройки кодирования вывода командной строки

Создание красиво отформатированного XML-документа

Автор: Павел Стехуле (используя ресурсы из Интернета — шаблон xslt)

Расширение xml2 содержит поддержку преобразований xslt — функция xslt_process . С помощью этой функции сгенерированный XML-документ можно легко переформатировать:

Журналирование связи между клиентом и Postgres

Автор: Павел Стегуле

Экспертом мониторинга протокола PostgreSQL является pgShark.

Быстрая замена индекса в ограничении первичного ключа

Автор: Петр Новак

Если вам нужно быстро переиндексировать индексы первичных ключей больших таблиц на рабочем сервере, вам может помочь следующая процедура:

Ограничение с внешним ключом может быть проверено на следующем шаге.

Параметризация сценария оболочки, содержащего команду DO

Автор: Павел Стегуле

Команда DO не поддерживает параметры, однако некоторые параметры можно задать с помощью переменных psql:

Проверить, работает ли подключенный сервер как подчиненный

Сокращение временных рядов с использованием аналитических функций

Автор: Павел Стегуле

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

Сумма векторов — (одномерные массивы)

Автор: Эрвин Брандштеттер

PostgreSQL не имеет специальных функций для полигональных операций, таких как векторы. Однако благодаря латеральному соединению (PostgreSQL 9.3) реализовать базовые операции не составляет труда. Сумма векторов может быть реализована с помощью следующего запроса:

При желании можно использовать предложение WITH ORDINALITY (PostgreSQL 9.4):

Определение размера задержки (смещения)между репликой и реплицируемым сервером (ведомым и главным)

Основой является использование функции pg_xlog_location_diff(loc1, loc2) . loc содержит дескриптор (статус) журнала транзакций: ‘0/0000000’ — абсолютный старт, pg_current_xlog_location(), pg_last_xlog_receive_location() или pg_last_xlog_replay_location():

Расширение поля с добавлением индекса

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

Автор: Павел Стехуле (PostgreSQL 9.1)

Автор: Том Лейн (PostgreSQL 9.3)

В версии 9.4 можно использовать предложение WITH ORDINALITY:

.

Ограничить только одним значением NULL в столбце

Ограничение UNIQUE обеспечивает уникальные значения в заданных столбцах, над которыми оно объявлено. Однако NULL не принимается как значение, которое было бы сравнимо с любым другим значением, и поэтому ограничение UNIQUE вообще не касается значений NULL. Что иногда нам не подходит. Уникальность значения NULL легко обеспечить с помощью условного функционального индекса (условный — чтобы сделать индекс небольшим, функциональный — NULL будет игнорироваться:

Преобразовать байт в большой двоичный объект

Сохраняет строку байтов в объекте lo.

Только один столбец из выбранной группы столбцов может быть NULL

Автор: Даниэль Верите

Как легко узнать, сколько значений NULL?

Настройка Application_name для одноцелевых скриптов с использованием psql

Автор: Эрик Рийкерс

Application_name — это атрибут соединения, который мы можем прикрепить к записям журнала, чтобы определить источник оператора SQL. Есть еще варианты

Получение N уникальных наиболее похожих значений с помощью KNNsearch и модуля pg_tgrm

Использование пользовательского приведения при переносе Oracle наPostgreSQL

Автор: Pavel Stěhule Oracle не поддерживает логический тип данных, который обычно заменяется типом varchar(1) или числом(1). Проблемы возникают при смене JDBC-драйвера, потому что нет возможности хранить значения true или false в varchar шириной в один символ. В данной базе данных преобразование boolean->varchar по умолчанию можно заменить пользовательской функцией преобразования (UPDATE должно выполняться под пользователем с правами суперпользователя) и, таким образом, решить проблему без необходимости изменения таблиц — любое изменение типа в таблице более чистое решение:

Создание JSON

Обрезка значений в столбце с типом поля

Этот трюк можно использовать в другом месте — вместо отсутствующего предложения LATERAL — в противном случае этот трюк зависит от реализации — это зависит от порядка оценки SRF и аналитических функций. Начиная с версии 9.3 вместо этого используйте предложение LATERAL.

.

Простая обработка результатов запроса в Bash

Автор: Павел Стегуле

Даже в Bash результат SQL-запроса может быть обработан относительно легко:

Аналогичное решение, предложенное Хьюбертом Депешем Любачевски:

Вставка комментария в оператор SQL

Автор: Томас Келлерер

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

Выйти из системы для всех зарегистрированных пользователей

Автор: Павел Стегуле

От имени пользователя postgres или другого суперпользователя выполните запрос:

Поиск дыр во временной последовательности с помощью оконных функций

Функция задержка может быть применена к любой упорядоченной последовательности:

Преобразование BLOB в байты

Получение первой разблокированной записи из таблицы

При реализации очереди с большим количеством потребителей может быть полезен следующий прием:

Неблокирующая параллельная обработка может быть реализована с использованием блокировок.очереди.

Функция для кодирования URL-адреса

Автор: Марти Раудсепп

Настройка emacs для подсветки синтаксиса при редактировании функции PL/pgSQL, активированной из консоли

Автор: Питер Эйзентраут

в файл .profile

в файл .emacs

Поиск по префиксу с использованием полнотекстового поиска с конфигурацией на чешском языке

Этот блок на самом деле не уловка, а предупреждение — я был удивлен поведением:

при поиске префикса

Я ожидал, что это правда, т.е. «Stě» является префиксом «Stěhule». Немного покопавшись, чтобы выяснить, была ли это ошибка PostgreSQL, я обнаружил, что это не ошибка PostgreSQL, а поведение TSearch, пытающегося применить то же преобразование и к префиксам. В этом случае «Сотня» преобразуется в «сотню», и естественно ничего не находится. Поэтому я рекомендую использовать не конфигурацию cs , а конфигурацию simple :

для префиксного поиска.

Удаление дубликатов с помощью оконных функций

Оконные функции позволяют нам очень эффективно удалять повторяющиеся функции:

Оптимизированная версия для PostgreSQL с использованием ctid:

Преобразование байта в текст

Если нам нужно преобразовать содержимое типа byte в соответствующее текстовое значение (при условии, что содержимое byte соответствует непосредственно кодам символов), можно использовать функцию:

Динамическое изменение элемента записи

В PL/pgSQL невозможно (легко) изменить элемент составного типа, если элемент определяется только во время выполнения функции. Есть несколько решений, доступных в C, которые очень быстры. Однако в некоторых случаях они могут быть недоступны или не могут быть использованы. В этом случае можно использовать следующую функцию:

Автор: Павел Стегуле

Эрвин Брандштеттер предложил еще лучшее решение, более простое и быстрое:

и он смог ускорить эту функцию:

Легко определить возрастс даты рождения

С помощью функции возраст легко определить возраст по дате рождения:

Идентификация главного/подчиненного сервера

Для получения статуса сервера можно использовать следующую функцию:

Автор: Саймон Риггс

Определить md5 содержимое таблицы

Функция md5 может использоваться для сравнения двух значений. В psql для этого случая можно использовать системную переменную PAGER :

.

Автор: Роберт Трит

LIKE против списка значений

PostgreSQL (как и SQL) не позволяет указывать более одной строки шаблона для оператора (I)LIKE. Итак

не является синтаксически правильным. Требуемый тест можно написать с помощью полей (или решить с помощью регулярного выражения):

Принудительно использовать уникальное имя курсора в PL/pgSQL

Обычно использование курсора PL/pgSQL создает курсор SQL с тем же именем, что и курсор PL/pgSQL. Такое поведение может быть нежелательным в некоторых случаях. Решение состоит в том, чтобы присвоить значение NULL курсору PL/pgSQL. В этом случае открывается SQL-курсор с уникальным именем:

.

Перебрать переменную типа RECORD в триггере

Эта функция требует мин. версия 8.4 — Том Лейн

примечание Этот метод требует 1 динамического SQL-запроса для каждого элемента записи — гораздо эффективнее использовать функции из библиотеки PL Toolbox или модуля contrib Hstore

.

Показать исходный код функции

Если вы знаете oid функции, очень просто получить ее генерирующую команду DDL:

Список файлов в пакете rpm

Хотя этот пункт не имеет отношения к PostgreSQL, я считаю, что он полезен для любого хакера:

Операция UPSERT с использованием триггера

Источник: http://database-programmer.blogspot.com/2009/06/approaches-to-upsert.html PostgreSQL не поддерживает оператор MERGE, который сочетает в себе функции операторов INSERT и UPDATE. Есть несколько решений — представленырешение основано на использовании BEFORE TRIGGER. Внутри триггера мы проверяем, является ли это существующей строкой, и если это так, мы делаем UPDATE и возвращаем NULL, что предотвращает запись, иначе мы возвращаем исходное значение, которое написано:

Округление времени до ближайшего пятиминутного деления

Автор: Павел Стегуле

функции to_string и to_array

Популярные функции array_to_string и string_to_array не поддерживают поля, содержащие NULL или они молча игнорируют значение NULL. Новые функции to_string и to_array аналогичны вышеперечисленным функциям, позволяя заменить NULL на определенную строку / определенную строку на значение NULL:

Тихая установка в MS Windows (не проверено)

Принудительная печать символов в функции преобразования to_char

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

Автор: Милен А. Радев

Защита от нежелательного преобразования char(n) в текст

Практически все функции для строковых операций имеют параметры текстового типа. PostgreSQL обеспечивает автоматическое преобразование из char(n) в varchar. В некоторых случаях такое поведение приводит к проблемам, особенно в старых приложениях, которые использовали тип данных char(n) (четкая рекомендация — избегать типа char(n)). Одним из примеров такой проблемы является перенос устаревших приложений и сравнение значений char(n) из n символов:

Эта конструкция в PostgreSQL не работает — substr имеет текст в качестве параметра и возвращает текст, поэтому ‘ab’ = ‘ab’ не применяется (что применимо к типу char(n)). К счастью, PostgreSQL допускает перегрузку функций, поэтому мы можем зарегистрировать пользовательскую функцию substr, где нет прямого преобразования из char(n) в текст:

Предложение Тома Лейна немного эффективнее:

Что немного грязнеетем не менее 100% рабочий трюк.

Создать часовой пояс домена

Еще один хороший трюк, представленный Дэвидом Э. Уилиром. PostgreSQL не поддерживает какой-либо тип данных для хранения часового пояса и не содержит никаких функций, которые мы могли бы использовать для проверки того, является ли введенный текст часовым поясом или нет. Однако можно использовать оператор AT:

.

Введите пароль из командной строки для psql

psql не имеет переключателя для ввода пароля. Поэтому пользователи относительно часто спрашивают, как ввести пароль неинтерактивно. Один из вариантов — установить системную переменную PGPASSWORD:

.

Преимущество этого подхода, как и цель, состоит в том, чтобы скрыть или не указывать пароль в листинге процесса.

Ускорить вывод списка таблиц

Если ваша БД содержит большое количество таблиц — от тысяч до десятков тысяч, вы оцените следующий прием для версий 8.3 и 8.4:

Автором является Том Лейн, и в версии 8.5 по умолчанию будет установлена ​​более высокая стоимость функции pg_table_is_visible.

Массив полей

Хотя PostgreSQL поддерживает n-мерные массивы, он не поддерживает массивы массивов и, следовательно, не поддерживает функции кумулятивного агрегирования для массивов произвольных типов — результатом будет массив массивов, а эта структура не поддерживается. Эту отсутствующую функцию можно обойти, инкапсулировав поля ввода в составной тип (поддерживаются поля составных значений):

Отправлено на форум pgsql-general Сэмом Мэйсоном.

Остерегайтесь повторных вызовов функций, используемых при разработке записей

PostgreSQL позволяет разрабатывать конструкции типа записи (..).*. Внимание! Если источником разрабатываемого значения является функция, то функция вызывается столько раз, сколько раз было развёртывание элемента. Это связано с тем, что разработка подразумевает неявную замену подстановочных знаков для отдельных элементов функции. т.е. для функции foo:

затем SELECT (foo()).* автоматически заменяется на SELECT (foo()).a, (foo()).b , вызываяненужный вызов функции foo. В SQL это может не быть проблемой — будут происходить множественные замены. В plpgsql это невозможно, поэтому это может привести к ненужному замедлению работы.