Как сделать представление в postgresql

Обновлено: 04.07.2024

Привет! Скажите, что vehicle может быть типа "автомобиль", "грузовик" или "мотоцикл". У каждого автомобиля есть строка top_speed (в км / ч) и license_plate .

Теперь добавьте виды для каждого типа автомобиля:

Все это прекрасно и модно. Но я попадаю в неудобную ситуацию, когда пытаюсь вставить в эти представления:

Моя проблема в том, что я уже вставляю в представление под названием "автомобиль" . почему я должен указывать это type = 'car' ?

Есть ли способ заставить postgres проверять определение представления, чтобы предоставить значения по умолчанию для пропущенных столбцов в запросах INSERT ?

2 ответа

В PostgreSQL существует очень мощная система правил.

В дополнение к вашему коду:

Postgres может запретить вам вставлять в представление строки, которые не были бы видны в представлении. Синтаксис: WITH CHECK OPTION в конце CREATE VIEW .

Вывод значений столбцов из предложения where представления не поддерживается. Вы можете смоделировать это с помощью триггера instead of insert :

Пытался делать, но никак не получается, может подскажете как делать и что использовать?

Создать представление
drop table if exists Книги; drop table if exists Авторы_книги; CREATE TABLE Книги ( .

Создать представление
Здравствуйте!помогите пожалуйста создать представление "Невыполненные наряды". CREATE VIEW AS.

Создать представление
Есть таблица Object(Object_ID, Object_Num, Object_Type_ID, Object_Container_ID, . ), где хранится.

Создать представление
Имееться 2 функции: 1. DMT_Get_DocumentsEx — экспорт заголовков документов При обработке.

Ниже приведен отрывок из книги о дизайне БД (Начальный номер базы данных ISBN: 0-7645-7490-6):

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

Ниже приводится выдержка из документации PostgreSQL 9.5:

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

Однако в PG представления реализованы с использованием системы правил. Таким образом, возможно (и это мой вопрос) любая фильтрация по представлению переписывается как фильтр внутри представления, что приводит к одному выполнению запроса к базовым таблицам.

Верна ли моя интерпретация, и PG комбинирует предложения WHERE в поле зрения и из поля зрения? Или он запускает их отдельно, один за другим? Какие-нибудь короткие, самодостаточные, правильные (компилируемые) примеры?

Я думаю, что вопрос не правильный, потому что оба источника не говорят об одном и том же. Первый связан с запросом из представления, и ПОСЛЕ применения фильтра SELECT * FROM my_view WHERE my_column = 'blablabla'; :. Второй - об использовании представлений, чтобы сделать вашу модель данных прозрачной для приложения, которое ее использует. Первые источники указывают, что вы должны включить фильтр WHERE my_column = 'blablabla' в определение представления, так как это приведет к лучшему плану выполнения.

Выбор в представлении происходит так же быстро или медленно, как и выполнение основного оператора SQL - вы можете легко проверить это, используя explain analyze .

Оптимизатор Postgres (и оптимизатор для многих других современных СУБД) сможет выдвигать предикаты в представлении в фактическое утверждение представления - при условии, что это простое утверждение (опять же, это можно проверить с помощью explain analyze ).

Чтобы дать вам пример того, что объяснил @a_horse :

Postgres реализует информационную схему, которая состоит из (иногда сложных) представлений, предоставляющих информацию об объектах БД в стандартизированной форме. Это удобно и надежно - и может быть значительно дороже, чем прямой доступ к таблицам каталога Postgres.

Очень простой пример, чтобы получить все видимые столбцы таблицы
. из информационной схемы:

. из системного каталога:

Сравните планы запросов и время выполнения для обоих EXPLAIN ANALYZE .

Первый запрос основан на представлении information_schema.columns , которое объединяет несколько таблиц, в которых мы вообще не нуждаемся.

Второй запрос сканирует только одну таблицу pg_catalog.pg_attribute , следовательно, намного быстрее. (Но для первого запроса все еще требуется всего несколько мс в обычных БД.)

С извинениями мне нужно отозвать свое утверждение о том, что принятый ответ не всегда верен - он утверждает, что представление всегда идентично тому же, что написано в качестве подзапроса. Я думаю, что это неоспоримо, и я думаю, что теперь я знаю, что происходит в моем случае.

Теперь я также думаю, что есть лучший ответ на оригинальный вопрос.

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

Мой ответ будет «нет, если ваш запрос использует оконные функции или что-то еще, что заставляет оптимизатор по-разному относиться к запросу, когда он становится подзапросом, потому что сам процесс создания подзапроса (независимо от того, представлен ли он как представление или нет) может снизить производительность если вы фильтруете с параметрами во время выполнения.

Сложность моей оконной функции не нужна. Объясните план для этого:

гораздо дешевле, чем для этого:

Надеюсь, это будет более конкретным и полезным.

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

Если я добавлю этот фильтр:

План объяснения, который я получаю, выглядит следующим образом:

При этом используется индекс первичного ключа в таблице обслуживания поездов и неуникальный индекс в таблице part_consist. Выполняется за 90мс.

Я создал представление (вставив его здесь, чтобы быть абсолютно ясным, но это буквально запрос в представлении):

Когда я запрашиваю это представление с идентичным фильтром:

Это план объяснения:

Это делает полное сканирование обеих таблиц и занимает 17 секунд.

Пока я не столкнулся с этим, я свободно использовал представления с PostgreSQL (поняв широко распространенные взгляды, выраженные в принятом ответе). Я бы специально избегал использования представлений, если мне нужна предварительная агрегационная фильтрация, для которой я бы использовал функции, возвращающие множество.

Мне также известно, что CTE в PostgreSQL строго оцениваются отдельно, в зависимости от проекта, поэтому я не использую их так же, как с SQL Server, например, где они, кажется, оптимизированы как подзапросы.

Поэтому мой ответ таков: есть случаи, когда представления не работают точно так, как запрос, на котором они основаны, поэтому рекомендуется соблюдать осторожность. Я использую Amazon Aurora на основе PostgreSQL 9.6.6.

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

Постарайтесь писать более развернутые вопросы. Поясните, в чем именно вы видите проблему, как её воспроизвести, что вы хотите получить в результате и т. д.

1 ответ 1

если коротко, то нужно все колоки для всех таблиц из представления указывать отдельно в правиле

Всё ещё ищете ответ? Посмотрите другие вопросы с метками sql postgresql или задайте свой вопрос.

Получение информации о базе данных

Размер базы данных

Чтобы получить физический размер файлов (хранилища) базы данных, используем следующий запрос:

Результат будет представлен как число вида 41809016 .

current_database() — функция, которая возвращает имя текущей базы данных. Вместо неё можно ввести имя текстом:

Для того, чтобы получить информацию в человекочитаемом виде, используем функцию pg_size_pretty :

В результате получим информацию вида 40 Mb .

Перечень таблиц

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

information_schema — стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д. Представления таблиц содержат информацию обо всех таблицах баз данных.

Запрос, описанный ниже, выберет все таблицы из указанной схемы текущей базы данных:

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

Размер таблицы

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

Функция pg_relation_size возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.

Имя самой большой таблицы

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

Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT :

relname — имя таблицы, индекса, представления и т.п.
relpages — размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
pg_class — системная таблица, которая содержит информацию о связях таблиц базы данных.

Перечень подключенных пользователей

Чтобы узнать имя, IP и используемый порт подключенных пользователей, выполним следующий запрос:

Активность пользователя

Чтобы узнать активность соединения конкретного пользователя, используем следующий запрос:

Работа с данными и полями таблиц

Удаление одинаковых строк

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

дублирующиеся строки,
ситуации, когда одна или более колонок дублируются (если эти колонки предполагается использовать в качестве первичного ключа).

Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).

Удалить все дубликаты поможет следующий запрос:

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

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

Теперь рассмотрим случай, когда повторяются значения полей.

Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:

Если данные важны, то сначала нужно найти записи с дубликатами:

Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение customer_id на другое.

Общая форма запроса на удаление описанных выше записей выглядит следующим образом:

Безопасное изменение типа поля

Может возникнуть вопрос о включении в этот список такой задачи. Ведь в PostgreSQL изменить тип поля очень просто с помощью команды ALTER . Давайте для примера снова рассмотрим таблицу с покупателями.

Но в результате выполнения получим ошибку:

ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.

В результате всё прошло без ошибок:

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

Например, преобразуем поле customer_id обратно в varchar , но с преобразованием формата данных:

В результате таблица примет следующий вид:

Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.

Рассмотрим два варианта поиска.

В результате получим значения: 5 , 9 и 11 .

Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:

В результате видим следующий результат: 5 , 9 и 6 .

Второй способ
Получаем имя последовательности, связанной с customer_id :

И находим все пропущенные идентификаторы:

Подсчёт количества строк в таблице

Количество строк вычисляется стандартной функцией count , но её можно использовать с дополнительными условиями.

Общее количество строк в таблице:

Количество строк при условии, что указанное поле не содержит NULL :

Количество уникальных строк по указанному полю:

Использование транзакций

Транзакция объединяет последовательность действий в одну операцию. Её особенность в том, что при ошибке в выполнении транзакции ни один из результатов действий не сохранится в базе данных.

Начнём транзакцию с помощью команды BEGIN .

Для того, чтобы откатить все операции, расположенные после BEGIN , используем команду ROLLBACK .

А чтобы применить — команду COMMIT .

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

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

Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):

Для того, чтобы прекратить работу запроса, выполним:

Работа с конфигурацией

Поиск и изменение расположения экземпляра кластера

Изменим расположение на другое с помощью команды:

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

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

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

typname — имя типа данных.
typlen — размер типа данных.

Изменение настроек СУБД без перезагрузки

Настройки PostgreSQL находятся в специальных файлах вроде postgresql.conf и pg_hba.conf . После изменения этих файлов нужно, чтобы СУБД снова получила настройки. Для этого производится перезагрузка сервера баз данных. Понятно, что приходится это делать, но на продакшн-версии проекта, которым пользуются тысячи пользователей, это очень нежелательно. Поэтому в PostgreSQL есть функция, с помощью которой можно применить изменения без перезагрузки сервера:

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

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

Читайте также: