Меню

Как определить существует ли таблица в текущем пути search_path с PLPGSQL



Как определить, существует ли таблица в текущем пути search_path с PLPGSQL?

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

Однако current_setting(‘search_path’) возвращает ТЕКСТ, содержащий «$user»,public по умолчанию, что не очень полезно.

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

3 ответа

Быстрая и грязная

В Postgres 9.4 + используйте

Возвращает NULL, если идентификатор не найден в пути поиска.
В Postgres 9.3 и старше используйте cast to regclass :

Этот вызывает исключение , если объект не найден!

Если найден ‘foo’ , вы вернетесь к oid , выводится как текстовое представление, так что это просто имя таблицы, при необходимости, двойное кавычку.

Если объект не найден, вы можете быть sure , он не существует нигде в пути поиска.
Если найдено два недостатка :

Поиск включает неявные схемы search_path , а именно (в настоящее время) pg_catalog и pg_temp . Но вы можете исключить временные и системные таблицы для своей цели. (?)

Приведение в regclass работает для всех объектов в системном каталоге pg_class : индексы , vies, последовательности и т. д. Не только таблицы . Кажется, вы ищете исключительно обычный стол. Однако у вас, вероятно, будут проблемы с другими объектами с тем же именем. Подробности:

Медленный и уверенный

Мы вернемся к вашему запросу, но не используем current_setting(‘search_path’) , который возвращает исходную настройку , Используйте специальную системную информационную функцию current_schemas() . По документации:

current_schemas(boolean) name[]
имена схем в пути поиска, необязательно включая неявные схемы

«$user» в пути поиска разрешен разумно. Если не существует схемы с именем SESSION_USER , схема не возвращается для начала. Кроме того, в зависимости от того, что вы хотите точно, вы можете дополнительно выводить неявные схемы ( pg_catalog и, возможно, pg_temp ) — но я предполагаю, что вы не хотите, чтобы это было для этого случая, поэтому используйте:

Скрипт SQL , демонстрирующий все, кроме last DO .
SQL Fiddle (JDBC) имеет проблемы с DO , содержащие символы окончания.

Вы можете преобразовать значение конфигурации в массив и заменить $user на текущее имя пользователя. Затем массив можно использовать в условии где:

Источник

📑 Шпаргалка по основным командам PostgreSQL

Вся работа с PostgreSQL осуществляется под пользователем postgres.

Работать с PosgreSQL можно как в интерактивном режиме, так и из командной строки. Программа — psql.

Основные команды PostgreSQL в интерактивном режиме:

  • \connect db_name – подключиться к базе с именем db_name
  • \du – список пользователей
  • \dp (или \z) – список таблиц, представлений, последовательностей, прав доступа к ним
  • \di – индексы
  • \ds – последовательности
  • \dt – список таблиц
  • \dt+ — список всех таблиц с описанием
  • \dt *s* — список всех таблиц, содержащих s в имени
  • \dv – представления
  • \dS – системные таблицы
  • \d+ – описание таблицы
  • \o – пересылка результатов запроса в файл
  • \l – список баз данных
  • \i – читать входящие данные из файла
  • \e – открывает текущее содержимое буфера запроса в редакторе (если иное не указано в окружении переменной EDITOR, то будет использоваться по умолчанию vi)
  • \d “table_name” – описание таблицы
  • \i запуск команды из внешнего файла, например \i /my/directory/my.sql
  • \pset – команда настройки параметров форматирования
  • \echo – выводит сообщение
  • \set – устанавливает значение переменной среды. Без параметров выводит список текущих переменных (\unset – удаляет).
  • \? – справочник psql
  • \help – справочник SQL
  • \q (или Ctrl+D) – выход с программы
Читайте также:  Южная Корея займет 3 е место в группе с кф 3 4 в БК Бетсити

Работа с PostgreSQL из командной строки:

  • -c (или –command) – запуск команды SQL без выхода в интерактивный режим
  • -f file.sql — выполнение команд из файла file.sql
  • -l (или –list) – выводит список доступных баз данных
  • -U (или –username) – указываем имя пользователя (например postgres)
  • -W (или –password) – приглашение на ввод пароля
  • -d dbname — подключение к БД dbname
  • -h – имя хоста (сервера)
  • -s – пошаговый режим, то есть, нужно будет подтверждать все команды
  • –S – однострочный режим, то есть, переход на новую строку будет выполнять запрос (избавляет от ; в конце конструкции SQL)
  • -V – версия PostgreSQL без входа в интерактивный режим

Примеры:

psql -U postgres -d dbname -c «CREATE TABLE my(some_id serial PRIMARY KEY, some_text text);» — выполнение команды в базе dbname.

psql -d dbname -H -c «SELECT * FROM my» -o my.html — вывод результата запроса в html-файл.

Утилиты (программы) PosgreSQL:

  • createdb и dropdb – создание и удаление базы данных (соответственно)
  • createuser и dropuser – создание и пользователя (соответственно)
  • pg_ctl – программа предназначенная для решения общих задач управления (запуск, останов, настройка параметров и т.д.)
  • postmaster – многопользовательский серверный модуль PostgreSQL (настройка уровней отладки, портов, каталогов данных)
  • initdb – создание новых кластеров PostgreSQL
  • initlocation – программа для создания каталогов для вторичного хранения баз данных
  • vacuumdb – физическое и аналитическое сопровождение БД
  • pg_dump – архивация и восстановление данных
  • pg_dumpall – резервное копирование всего кластера PostgreSQL
  • pg_restore – восстановление БД из архивов (.tar, .tar.gz)

Примеры создания резервных копий:

Создание бекапа базы mydb, в сжатом виде

Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments

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

Создание резервной копии с сжатием в gz

Список наиболее часто используемых опций:

  • -h host — хост, если не указан то используется localhost или значение из переменной окружения PGHOST.
  • -p port — порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.
  • -u — пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.
  • -a, —data-only — дамп только данных, по-умолчанию сохраняются данные и схема.
  • -b — включать в дамп большие объекты (blog’и).
  • -s, —schema-only — дамп только схемы.
  • -C, —create — добавляет команду для создания БД.
  • -c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).
  • -O — не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).
  • -F, —format — выходной формат дампа, custom, tar, или plain text.
  • -t, —table=TABLE — указываем определенную таблицу для дампа.
  • -v, —verbose — вывод подробной информации.
  • -D, —attribute-inserts — дамп используя команду INSERT с списком имен свойств.

Бекап всех баз данных используя команду pg_dumpall.

Восстановление таблиц из резервных копий (бэкапов):

psql — восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
pg_restore — восстановление сжатых бекапов (tar);

Восстановление всего бекапа с игнорированием ошибок

Восстановление всего бекапа с остановкой на первой ошибке

Для восстановления из tar-арихива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восстановить

Восстановление резервной копии БД, сжатой gz

Источник

Postgresql как проверить наличие таблицы

Вся работа с PostgreSQL осуществляется под пользователем postgres.

Работать с PosgreSQL можно как в интерактивном режиме, так и из командной строки. Программа — psql.

Основные команды PostgreSQL в интерактивном режиме:

  • \connect db_name – подключиться к базе с именем db_name
  • \du – список пользователей
  • \dp (или \z) – список таблиц, представлений, последовательностей, прав доступа к ним
  • \di – индексы
  • \ds – последовательности
  • \dt – список таблиц
  • \dt+ — список всех таблиц с описанием
  • \dt *s* — список всех таблиц, содержащих s в имени
  • \dv – представления
  • \dS – системные таблицы
  • \d+ – описание таблицы
  • \o – пересылка результатов запроса в файл
  • \l – список баз данных
  • \i – читать входящие данные из файла
  • \e – открывает текущее содержимое буфера запроса в редакторе (если иное не указано в окружении переменной EDITOR, то будет использоваться по умолчанию vi)
  • \d “table_name” – описание таблицы
  • \i запуск команды из внешнего файла, например \i /my/directory/my.sql
  • \pset – команда настройки параметров форматирования
  • \echo – выводит сообщение
  • \set – устанавливает значение переменной среды. Без параметров выводит список текущих переменных (\unset – удаляет).
  • \? – справочник psql
  • \help – справочник SQL
  • \q (или Ctrl+D) – выход с программы

Работа с PostgreSQL из командной строки:

  • -c (или –command) – запуск команды SQL без выхода в интерактивный режим
  • -f file.sql — выполнение команд из файла file.sql
  • -l (или –list) – выводит список доступных баз данных
  • -U (или –username) – указываем имя пользователя (например postgres)
  • -W (или –password) – приглашение на ввод пароля
  • -d dbname — подключение к БД dbname
  • -h – имя хоста (сервера)
  • -s – пошаговый режим, то есть, нужно будет подтверждать все команды
  • –S – однострочный режим, то есть, переход на новую строку будет выполнять запрос (избавляет от ; в конце конструкции SQL)
  • -V – версия PostgreSQL без входа в интерактивный режим

Примеры:

psql -U postgres -d dbname -c «CREATE TABLE my(some_id serial PRIMARY KEY, some_text text);» — выполнение команды в базе dbname.

psql -d dbname -H -c «SELECT * FROM my» -o my.html — вывод результата запроса в html-файл.

Утилиты (программы) PosgreSQL:

  • createdb и dropdb – создание и удаление базы данных (соответственно)
  • createuser и dropuser – создание и пользователя (соответственно)
  • pg_ctl – программа предназначенная для решения общих задач управления (запуск, останов, настройка параметров и т.д.)
  • postmaster – многопользовательский серверный модуль PostgreSQL (настройка уровней отладки, портов, каталогов данных)
  • initdb – создание новых кластеров PostgreSQL
  • initlocation – программа для создания каталогов для вторичного хранения баз данных
  • vacuumdb – физическое и аналитическое сопровождение БД
  • pg_dump – архивация и восстановление данных
  • pg_dumpall – резервное копирование всего кластера PostgreSQL
  • pg_restore – восстановление БД из архивов (.tar, .tar.gz)

Примеры создания резервных копий:

Создание бекапа базы mydb, в сжатом виде

Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments

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

Создание резервной копии с сжатием в gz

Список наиболее часто используемых опций:

  • -h host — хост, если не указан то используется localhost или значение из переменной окружения PGHOST.
  • -p port — порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.
  • -u — пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.
  • -a, —data-only — дамп только данных, по-умолчанию сохраняются данные и схема.
  • -b — включать в дамп большие объекты (blog’и).
  • -s, —schema-only — дамп только схемы.
  • -C, —create — добавляет команду для создания БД.
  • -c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).
  • -O — не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).
  • -F, —format — выходной формат дампа, custom, tar, или plain text.
  • -t, —table=TABLE — указываем определенную таблицу для дампа.
  • -v, —verbose — вывод подробной информации.
  • -D, —attribute-inserts — дамп используя команду INSERT с списком имен свойств.

Бекап всех баз данных используя команду pg_dumpall.

Восстановление таблиц из резервных копий (бэкапов):

psql — восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
pg_restore — восстановление сжатых бекапов (tar);

Восстановление всего бекапа с игнорированием ошибок

Восстановление всего бекапа с остановкой на первой ошибке

Для восстановления из tar-арихива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восстановить

Восстановление резервной копии БД, сжатой gz

Источник

postgresql — Как я могу проверить, существует ли столбец в таблице, используя оператор SQL

Есть ли в PostgreSQL простая альтернатива этому утверждению, созданному в Oracle?

Затем я проверяю, возвращает ли запрос что-либо, чтобы доказать, что столбец существует.

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

Решение

Другие решения

Принятый ответ правильный, но отсутствует схема и более хороший вывод (True / False):

Это проще (и SQLi-безопасно) с PostgreSQL типы идентификаторов объектов :

Если вы создаете динамический SQL и имя столбца указывается в качестве параметра, вы можете использовать quote_ident() чтобы избежать внедрения SQL:

Работает за столами вне search_path , тоже:

Конечно, заменить YOURTABLENAME а также YOURCOLUMNNAME с правильными значениями. Если строка возвращается, столбец с таким именем существует, в противном случае это не так.

В отличие от Oracle, PostgreSQL поддерживает стандарт ANSI INFORMATION_SCHEMA Просмотры.

Соответствующее стандартное представление Oracle для user_tab_columns: information_schema.columns

Вот аналогичный вариант ответа Эрвина Брандштеттера.
Здесь мы также проверяем схему в случае, если у нас есть похожие таблицы в другой схеме.

Источник