Как вывести список баз данных и таблиц PostgreSQL с помощью psql
При администрировании серверов баз данных PostgreSQL одной из наиболее распространенных задач, которые вы, вероятно, будете выполнять, будет перечисление баз данных и их таблиц.
Листинг баз данных
При установке пакета PostgreSQL создается административный пользователь с именем «postgres». По умолчанию этот пользователь может подключаться к локальному серверу PostgreSQL без пароля.
Чтобы получить доступ к терминалу psql от имени пользователя postgres, запустите:
В терминале psql выполните мета-команду l или list вывести список всех баз данных:
Вывод будет включать количество баз данных, имя каждой базы данных, ее владельца, кодировку и права доступа:
На сервере PostgreSQL по умолчанию созданы три базы данных: template0, template1 и postgres. Первые два — это шаблоны, которые используются при создании новых баз данных.
Другой способ составить список баз данных — использовать следующий оператор SQL:
В отличие от мета-команды l приведенный выше запрос покажет только имена баз данных:
Листинговые таблицы
Например, чтобы подключиться к базе данных с именем «odoo», введите:
После переключения базы данных используйте мета-команду dt вывести список всех таблиц базы данных:
Вывод будет включать количество таблиц, имя каждой таблицы, ее схему, тип и владельца:
Если база данных пуста, вывод будет выглядеть так:
Выводы
Не стесняйтесь оставлять комментарии, если у вас есть вопросы.
Как вывести список баз данных и таблиц PostgreSQL с помощью psql
Главное меню » Базы данных » База данных PostgreSQL » Как вывести список баз данных и таблиц PostgreSQL с помощью psql
PostgreSQL поставляется с интерактивным инструментом, psqlкоторый позволяет вам подключаться к серверу и выполнять запросы к нему. При использовании psqlвы также можете воспользоваться его мета-командами. Эти команды полезны для сценариев и администрирования командной строки. Все мета-команды начинаются с обратного слэша без кавычек и также известны как команды обратного слэша.
В этой статье объясняется, как использовать базы данных и таблицы на сервере PostgreSQL psql.
Листинг баз данных
Вы можете подключиться к серверу PostgreSQL, используя psqlкоманду как любой системный пользователь. В зависимости от конфигурации сервера пользователю может потребоваться ввести свой пароль для подключения к терминалу psql. Чтобы получить доступ к терминалу psql от имени пользователя, в который вы вошли, просто введите psql.
Когда пакет PostgreSQL установлен, создается административный пользователь с именем «postgres». По умолчанию этот пользователь может подключиться к локальному серверу PostgreSQL без пароля.
Чтобы получить доступ к psqlтерминалу как пользователь «postgres», запустите:
Из терминала psql выполните мета-команду \l или \list, чтобы вывести список всех баз данных:
Вывод будет включать количество баз данных, имя каждой базы данных, ее владельца, кодирование и привилегии доступа:
Сервер PostgreSQL имеет три базы данных, созданные по умолчанию: template0, template1 и postgres. Первые два – это шаблоны, которые используются при создании новых баз данных.
Если вы хотите получить информацию о размерах баз данных, табличных пространствах по умолчанию и описаниях, используйте \l+ или \list+. Размер базы данных будет показан, только если текущий пользователь может подключиться к нему.
Другой способ составить список баз данных – использовать следующий оператор SQL:
В отличие от мета-команды \l, приведенный выше запрос покажет только имена баз данных:
Таблицы списков
Чтобы получить список всех таблиц конкретной базы данных сначала необходимо подключиться к нему с помощью мета-команды \c или \connect. Пользователь, вошедший в систему как терминал psql, должен иметь возможность подключаться к базе данных.
Например, чтобы подключиться к базе данных с именем «odoo», вы должны набрать:
Как только база данных переключена, используйте мета-команду \dt для вывода списка всех таблиц базы данных:
Вывод будет включать количество таблиц, имя каждой таблицы и ее схему, тип и владельца:
Если база данных пуста, вывод будет выглядеть так:
Для получения информации о размерах таблиц и описаний используйте \dt+.
Заключение
Вы узнали, как составлять список баз данных и таблиц PostgreSQL с помощью команды psql.
Не стесняйтесь оставлять комментарии, если у вас есть какие-либо вопросы.
Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.
15 полезных команд PostgreSQL
Авторизуйтесь
15 полезных команд PostgreSQL
В сети много руководств по PostgreSQL, которые описывают основные команды. Но при погружении глубже в работу возникают такие практические вопросы, для которых требуются продвинутые команды.
Такие команды, или сниппеты, редко описаны в документации. Рассмотрим несколько на примерах, полезных как для разработчиков, так и для администраторов баз данных.
Получение информации о базе данных
Размер базы данных
Чтобы получить физический размер файлов (хранилища) базы данных, используем следующий запрос:
current_database() — функция, которая возвращает имя текущей базы данных. Вместо неё можно ввести имя текстом:
Для того, чтобы получить информацию в человекочитаемом виде, используем функцию pg_size_pretty :
Перечень таблиц
Иногда требуется получить перечень таблиц базы данных. Для этого используем следующий запрос:
information_schema — стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д. Представления таблиц содержат информацию обо всех таблицах баз данных.
Запрос, описанный ниже, выберет все таблицы из указанной схемы текущей базы данных:
В последнем условии IN можно указать имя определенной схемы.
Размер таблицы
По аналогии с получением размера базы данных размер данных таблицы можно вычислить с помощью соответствующей функции:
Функция pg_relation_size возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.
Имя самой большой таблицы
Для того, чтобы вывести список таблиц текущей базы данных, отсортированный по размеру таблицы, выполним следующий запрос:
Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT :
relname — имя таблицы, индекса, представления и т.п.
relpages — размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
pg_class — системная таблица, которая содержит информацию о связях таблиц базы данных.
Перечень подключенных пользователей
Чтобы узнать имя, IP и используемый порт подключенных пользователей, выполним следующий запрос:
Активность пользователя
Чтобы узнать активность соединения конкретного пользователя, используем следующий запрос:
Работа с данными и полями таблиц
Удаление одинаковых строк
Если так получилось, что в таблице нет первичного ключа (primary key), то наверняка среди записей найдутся дубликаты. Если для такой таблицы, особенно большого размера, необходимо поставить ограничения (constraint) для проверки целостности, то удалим следующие элементы:
Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).
Удалить все дубликаты поможет следующий запрос:
Уникальное для каждой записи поле ctid по умолчанию скрыто, но оно есть в каждой таблице.
Последний запрос требователен к ресурсам, поэтому будьте аккуратны при его выполнении на рабочем проекте.
Теперь рассмотрим случай, когда повторяются значения полей.
Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:
Если данные важны, то сначала нужно найти записи с дубликатами:
Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение customer_id на другое.
Общая форма запроса на удаление описанных выше записей выглядит следующим образом:
Безопасное изменение типа поля
Но в результате выполнения получим ошибку:
ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.
В результате всё прошло без ошибок:
Обратите внимание, что при использовании USING кроме конкретного выражения возможно использование функций, других полей и операторов.
В результате таблица примет следующий вид:
Поиск «потерянных» значений
Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.
Рассмотрим два варианта поиска.
Первый способ
Выполним следующий запрос, чтобы найти начало интервала с «потерянным» значением:
Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:
Второй способ
Получаем имя последовательности, связанной с customer_id :
И находим все пропущенные идентификаторы:
Подсчёт количества строк в таблице
Общее количество строк в таблице:
Количество строк при условии, что указанное поле не содержит NULL :
Количество уникальных строк по указанному полю:
Использование транзакций
Транзакция объединяет последовательность действий в одну операцию. Её особенность в том, что при ошибке в выполнении транзакции ни один из результатов действий не сохранится в базе данных.
Просмотр и завершение исполняемых запросов
Для того, чтобы получить информацию о запросах, выполним следующую команду:
Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):
Для того, чтобы прекратить работу запроса, выполним:
Работа с конфигурацией
Поиск и изменение расположения экземпляра кластера
Возможна ситуация, когда на одной операционной системе настроено несколько экземпляров PostgreSQL, которые «сидят» на различных портах. В этом случае поиск пути к физическому размещению каждого экземпляра — достаточно нервная задача. Для того, чтобы получить эту информацию, выполним следующий запрос для любой базы данных интересующего кластера:
Изменим расположение на другое с помощью команды:
Но для того, чтобы изменения вступили в силу, требуется перезагрузка.
Получение перечня доступных типов данных
Получим перечень доступных типов данных с помощью команды:
typname — имя типа данных.
typlen — размер типа данных.
Изменение настроек СУБД без перезагрузки
Но, к сожалению, она применима не ко всем параметрам. В некоторых случаях для применения настроек перезагрузка обязательна.
Мы рассмотрели команды, которые помогут упростить работу разработчикам и администраторам баз данных, использующим PostgreSQL. Но это далеко не все возможные приёмы. Если вы сталкивались с интересными задачами, напишите о них в комментариях. Поделимся полезным опытом!
PostgreSQL
Введение
PostgreSQL (произносится «Пост-Грэс-Кью-Эл») — свободная объектно-реляционная система управления базами данных (СУБД).
Установка
После установки
После установки проверьте версию установленного PostgreSQL
postgres (PostgreSQL) 9.2.24
Расположение файлов с настройками, например, postgresql.conf можно получить выполнив
В этом примере директория, которая содержит настройки это
Полезно изучить её содержание
Подключение к БД
Чтобы получить доступ к базам данных можно воспользоваться стандартной консолью psql.
Если Вы хотите подключиться из bash в Windows не забудьте добавить местоположение psql.exe (у меня это C:\Program Files\PostgreSQL\12\bin) в PATH
Как это делается описано в статье PATH
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1)) Type «help» for help.
Если БД на локальном хосте
Получить данные о подключении
You are connected to database «postgres» as user «postgres» via socket in «/var/run/postgresql» at port «5432».
Список существующих БД
Список уже существующих на сервере баз данных можно получить командой
Обратите внимание на столбцы Encoding, Collate и Ctype. Знание кодировок может Вам пригодиться в будущем.
Создать базу данных
Создадим базу данных heihei_ru_db с кодировкой utf8
CREATE DATABASE «heihei_ru_db» WITH OWNER «postgres» ENCODING ‘UTF8’;
Создадим базу данных heihei с кодировкой utf8 и укажем значения для Collate, Ctype и Template
CREATE DATABASE «heihei» WITH OWNER «postgres» ENCODING
‘UTF8’ LC_COLLATE = ‘C’ LC_CTYPE = ‘C’ TEMPLATE = template0;
Проверим, что получилось
То получу сразу четыре доступные кодировки
C
C.UTF-8
en_US.utf8
POSIX
Но создать БД, например, с en_US.utf8 у меня не получается
CREATE DATABASE «heihei» WITH OWNER «postgres» ENCODING ‘UTF8’ LC_COLLATE = ‘en_US.utf8’ LC_CTYPE = ‘en_US.utf8’ TEMPLATE = template0;
ERROR: invalid locale name: «en_US.UTF-8»
UPD: Выполнил такую же команду в Ubuntu и база успешно создана
CREATE DATABASE «new_db» WITH OWNER «postgres» ENCODING ‘UTF8’ LC_COLLATE = ‘en_US.UTF-8’ LC_CTYPE = ‘en_US.UTF-8’ TEMPLATE = template0;
Удалить базу данных
Чтобы удалить базу данных воспользуйтесь командой
DROP DATABASE db_name;
Проверить кодировки
Проверить кодировку сервера можно командой
Проверить кодировку клиента
Когда вы находитесь в интерактивном режиме PostgreSQL в консоле появляется префикс
Где db это название текущей базы данных
Показать адрес текущей директории
Вернуться в PostgreSQL можно выполнив
Зайти в БД
Чтобы начать работу с базой данных нужно знать её имя, например пусть Вам нужна база даныых, которую назвали просто HeiHei_ru_DB
You are now connected to database «HeiHei_ru_DB» as user «postgres».
Если вы работаете в Linux и база данных находится на том же хосте можно выполнить
Чтобы посмотреть список таблиц введите
List of relations
Schema | Name | Type | Owner
———+———+——-+———-
public | person | table | postgres
(1 row)
Чтобы посмотреть всю таблицу person уже можно использовать стандартный
SELECT * FROM person;
Запуск скрипта из файла
Сперва проверим, что с переменными окружения всё впорядке.
Для этого введём в консоль psql.exe нажмём Enter и проверим что bash не жалуется на неизвестную команду.
Пишем скрипт script.sql
CREATE TABLE person ( id int, first_name VARCHAR(50), last_name VARCHAR(50), gender VARCHAR(5), date_of_birth DATE )
Применим этот скрипт к базе данных HeiHei_ru_DB
Password for user postgres:
CREATE TABLE
Саме время сделать что-то более близкое к реальному скрипту
На поля таблицы нужно ввести некоторые ограничения и добавить им свойств.
CREATE TABLE booking_sites ( id BIGSERIAL NOT NULL PRIMARY KEY, company_name VARCHAR(50) NOT NULL, origin_country VARCHAR(50) NOT NULL, age VARCHAR(3) NOT NULL, date_of_birth DATE NOT NULL, website_url VARCHAR(50) );
Password for user postgres:
CREATE TABLE
Содержание базы данных
В предыдущем параграфе мы создали в базе данных heihei таблицу booking_sites
Убедимся в том, что скрипт отработал удачно
Перейдём в базу данных heihei и проверим создалась ли таблица
You are now connected to database «heihei» as user «postgres».
Описание таблицы
Если таблица создана какое-то время назад. Вы могли уже забыть, какие конкретно столбцы она содержит.
Для описания таблицы используется команда \d
Пользователи
Получить список пользователей
Изменение таблицы
Когда нужно обновить название столбца таблицы используем команду ALTER
Предположим полю website_url не хватает 50 символов. Увеличим длину до 60.
ALTER TABLE booking_sites ALTER column website_url TYPE VARCHAR( 60);
Проверим изменилась ли таблица
Чтобы файл открылся желательно положить его в папку
Показать таблицы в PostgreSQL
что эквивалентно show tables (из MySQL) в PostgreSQL?
18 ответов
С psql интерфейс командной строки, это показывает все таблицы в текущей схеме:
программно (или с psql интерфейс, конечно, тоже):
системные таблицы в базе данных pg_catalog.
войдите в систему как суперпользователь:
вы можете перечислить все базы данных и пользователей по \l command, (список других команд по \? ).
(для полноты картины)
вы также можете запросить (SQL-standard) информация-схемы:
первый вход в систему как пользователь postgres:
\dt возвращает список всех таблиц в базе данных, к которой вы подключены.
запуск psql с флагом-E будет повторять запрос, используемый внутри для реализации \ДТ и подобные:
если вы хотите увидеть только список таблиц, которые вы создали, вы можете только сказать:
вы можете использовать интерактивный терминал psql PostgreSQL для отображения таблиц в PostgreSQL.
1. Запустить Psql
обычно вы можете запустить следующую команду для входа в psql:
например, psql template1 postgres
одна из возможных ситуаций: предположим, вы входите в систему как root, и вы не помните имя базы данных. Вы можете просто ввести сначала в Psql, запустив:
в некоторых системах, sudo команда недоступна, вместо этого вы можете запустить любую команду ниже:
2. Показать таблицы
теперь в Psql вы можете запускать такие команды, как:
сначала подключитесь к базе данных, используя следующую команду
и вы увидите это сообщение, Теперь вы подключены к database database_name и выполните следующую команду
в database_name и table_name просто обновите свою базу данных и имя таблицы
используйте только таблицы
Если хотите увидеть таблицы схемы
Если вы хотите увидеть конкретные таблицы-схемы
Если вы используете Pgadmin4 в PostgreSQL, вы можете использовать это, чтобы показать таблицы в вашей базе данных:
отметим, что список таблиц общественные схема базы данных, которую вы используете. Мне нравится держать свои таблицы в отдельных схемах, поэтому принятый ответ не сработал для меня.
перечислить все таблицы в рамках определенной схемы мне нужно:
1) Подключение к нужной базе данных:
Это показывает мне результаты, которые меня интересуют:
чистки рядов, \d tablename покажет подробную информацию о данной таблице, что-то вроде show columns from tablename в MySQL, но с немного большей информацией.