Меню

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



15 полезных команд PostgreSQL

Обложка: 15 полезных команд PostgreSQL

В сети много руководств по 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 по умолчанию скрыто, но оно есть в каждой таблице.

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

Usetech , Удалённо , По итогам собеседования

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

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

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

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

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

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

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

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

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

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

Это значит, что нельзя просто так взять и изменить тип поля при наличии данных в таблице. Так как использовался тип varchar , СУБД не может определить принадлежность значения к integer . Хотя данные соответствуют именно этому типу. Для того, чтобы уточнить этот момент, в сообщении об ошибке предлагается использовать выражение USING , чтобы корректно преобразовать наши данные в integer :

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

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

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

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

Поиск «потерянных» значений

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Анализ крови на уровень мочевой кислоты

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

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

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

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

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

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

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

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

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

Источник

Запрос данных PostgreSQL с помощью SELECT

PostgreSQL – это открытая система управления базами данных (СУБД), основанная на языке запросов SQL, которая часто используется для хранения данных веб-сайтов и других приложений.

В данном руководстве рассматривается процесс создания запросов данных PostgreSQL.

Примечание: Предполагается, что СУБД PostgreSQL предварительно установлена на сервер. В руководстве используется система Ubuntu 12.04, однако инструкции подойдут и для более современных дистрибутивов Linux.

Вход в PostgreSQL

Для работы можно загрузить тестовую БД.

Откройте сессию стандартного пользователя PostgreSQL:

sudo su — postgres

Чтобы получить файл тестовой БД, введите:

Распакуйте архив и перейдите в полученный каталог:

tar xzvf world-1.0.tar.gz
cd dbsamples-0.1/world

createdb -T template0 worlddb

Добавьте в новую БД файл .sql:

Войдите в новое окружение:

Просмотр данных в PostgreSQL

Для начала нужно ознакомиться с тестовой БД. Чтобы просмотреть список таблиц, введите:

\d+
List of relations
Schema | Name | Type | Owner | Size | Description
——-+——————+——-+———-+———+————-
public | city | table | postgres | 264 kB |
public | country | table | postgres | 48 kB |
public | countrylanguage | table | postgres | 56 kB |
(3 rows)

Эта БД содержит три таблицы. Чтобы просмотреть столбцы одной из таблиц (например, city), выполните команду:

\d city
Table «public.city»
Column | Type | Modifiers
————+—————+————
id | integer | not null
name | text | not null
countrycode | character(3) | not null
district | text | not null
population | integer | not null
Indexes:
«city_pkey» PRIMARY KEY, btree (id)
Referenced by:
TABLE «country» CONSTRAINT «country_capital_fkey» FOREIGN KEY (capital) REFERENCES city(id)

На экране представлены данные каждого столбца таблицы, а также взаимосвязи данной таблицы с другими наборами данных.

Запрос данных при помощи select

Для запроса информации в PostgreSQL используется оператор select. Базовый синтаксис выглядит так:

SELECT columns_to_return FROM table_name;

К примеру, если запросить столбцы таблицы country (команда \d country), на экране появится объёмный вывод. Его можно отфильтровать, запросив только необходимые вам данные. Для примера запросите название страны и континент, на котором она находится.

SELECT name,continent FROM country;
name | continent
———————-+—————
Afghanistan | Asia
Netherlands | Europe
Netherlands Antilles | North America
Albania | Europe
Algeria | Africa
American Samoa | Oceania
Andorra | Europe
. . .

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

SELECT * FROM city;
id| name | countrycode | district | population
—+————————————+————-+——————————-+————
1 | Kabul | AFG | Kabol | 1780000
2 | Qandahar | AFG | Qandahar | 237500
3 | Herat | AFG | Herat | 186800
4 | Mazar-e-Sharif | AFG | Balkh | 127800
5 | Amsterdam | NLD | Noord-Holland | 731200
6 | Rotterdam | NLD | Zuid-Holland | 593321
7 | Haag | NLD | Zuid-Holland | 440900
. . .

Определение порядка записей

PostgreSQL позволяет определить порядок вывода запрошенных данных; для этого используется конструкция ORDER BY, идущая после оператора SELECT. Базовый синтаксис выглядит так:

SELECT columns FROM table ORDER BY column_names [ ASC | DESC ];

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

SELECT name,continent FROM country ORDER BY continent;
name | continent
————————-+—————
Algeria | Africa
Western Sahara | Africa
Madagascar | Africa
Uganda | Africa
Malawi | Africa
Mali | Africa
Morocco | Africa
Côte d\u0092Ivoire | Africa
. . .

По умолчанию конструкция ORDER BY позволяет упорядочить вывод данных в порядке возрастания.

Чтобы изменить порядок вывода, нужно добавить в конструкцию ORDER BY оператор DESC. Например:

SELECT name,continent FROM country ORDER BY continent DESC;
name | continent
————————-+—————
Paraguay | South America
Bolivia | South America
Brazil | South America
Falkland Islands | South America
Argentina | South America
Venezuela | South America
Guyana | South America
Chile | South America
. . .

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

К примеру, можно отсортировать вывод по континенту, а затем по названию страны, и в результате получить алфавитный список стран для каждого континента:

SELECT name,continent FROM country ORDER BY continent,name;
name | continent
————————-+—————
Angola | Africa
Burundi | Africa
Benin | Africa
Burkina Faso | Africa
Botswana | Africa
Central African Republic | Africa
Côte d\u0092Ivoire | Africa
Cameroon | Africa
Congo | Africa
. . .

Фильтрация записей PostgreSQL

PostgreSQL предоставляет механизмы для более детальной фильтрации вывода.

Один из таких механизмов – предложение where, которое позволяет задавать условия поиска.

К примеру, можно запросить все города Соединённых Штатов, добавив в конец команды конструкцию WHERE countrycode = ‘USA’.

SELECT name FROM city WHERE countrycode = ‘USA’;
name
————————-
New York
Los Angeles
Chicago
Houston
Philadelphia
Phoenix
San Diego
Dallas
San Antonio
. . .

Примечание: Условия поиска для предложения WHERE нужно помещать в одинарные кавычки.

Реляционный оператор like делает поиск ещё более гибким. Этот оператор использует шаблоны:

  • “_” – поиск совпадения по одному символу.
  • “%” – поиск 0 или больше значений.
  • and и or – объединение параметров фильтрации.

Для примера попробуйте запросить названия городов США, которые начинаются на N.

SELECT name FROM city WHERE countrycode = ‘USA’ AND name LIKE ‘N%’;
name
———————
New York
Nashville-Davidson
New Orleans
Newark
Norfolk
Newport News
Naperville
New Haven
North Las Vegas
Norwalk
New Bedford
Norman
(12 rows)

Этот вывод также можно получить в алфавитном порядке:

SELECT name FROM city WHERE countrycode = ‘USA’ AND name LIKE ‘N%’ ORDER BY name;
name
———————
Naperville
Nashville-Davidson
Newark
New Bedford
New Haven
New Orleans
Newport News
New York
Norfolk
Norman
North Las Vegas
Norwalk
(12 rows)

Читайте также:  Календарь труда и отдыха на 2020 год

Продвинутое использование select

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

SELECT country.name AS country,city.name AS capital,continent FROM country JOIN city ON country.capital = city.id ORDER BY continent,country;
country | capital | continent
————————+————————————+—————
Algeria | Alger | Africa
Angola | Luanda | Africa
Benin | Porto-Novo | Africa
Botswana | Gaborone | Africa
Burkina Faso | Ouagadougou | Africa
Burundi | Bujumbura | Africa
Cameroon | Yaoundé | Africa
Cape Verde | Praia | Africa
Central African Republic| Bangui | Africa
Chad | N´Djaména | Africa
. . .

Этот запрос состоит из нескольких частей.

В конце этого запроса находится знакомый оператор ORDER BY continent,country, который сортирует вывод по континенту, а затем по названию страны.

Чтобы понять остальные компоненты запроса, нужно ознакомиться с объединением таблиц.

Соединенные таблицы

Предложение JOIN позволяет запрашивать данные из нескольких связанных таблиц одновременно.

Примечание: Таблицы считаются связанными, если они имеют столбец, который относится к одним и тем же данным.

К примеру, в тестовой БД таблицы country и city связаны, поскольку совместно используют некоторые данные. Чтобы увидеть, что таблица country ссылается на таблицу city, введите:

\d country
. . .
. . .
Foreign-key constraints:
«country_capital_fkey» FOREIGN KEY (capital) REFERENCES city(id)
. . .
. . .

Этот вывод говорит о том, что столбец capital таблицы country ссылается на столбец id таблицы city. По сути, можно воспринимать эти две таблицы как одну большую таблицу.

В предложенном выше запросе есть часть:

FROM country JOIN city ON country.capital = city.id

Это выражение значит, что Postgres вернёт данные из обеих таблиц. Предложение JOIN здесь является стандартным оператором соединения (также известен как inner join).

INNER JOIN выводит данные, присутствующие в обеих таблицах. Например, если соединить таблицы, которые не относятся друг к другу как внешние ключи, программа вернёт ошибку, поскольку таблицы содержат не связанные между собой данные. Для такого вывода стандартное предложение join не подходит.

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

В данном случае запрашиваются записи с общими значениями в обеих таблицах.

Заключение

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

Источник

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

PostgreSQL – это открытая система управления базами данных (СУБД), основанная на языке запросов SQL, которая часто используется для хранения данных веб-сайтов и других приложений.

В данном руководстве рассматривается процесс создания запросов данных PostgreSQL.

Примечание: Предполагается, что СУБД PostgreSQL предварительно установлена на сервер. В руководстве используется система Ubuntu 12.04, однако инструкции подойдут и для более современных дистрибутивов Linux.

Вход в PostgreSQL

Для работы можно загрузить тестовую БД.

Откройте сессию стандартного пользователя PostgreSQL:

sudo su — postgres

Чтобы получить файл тестовой БД, введите:

Распакуйте архив и перейдите в полученный каталог:

tar xzvf world-1.0.tar.gz
cd dbsamples-0.1/world

createdb -T template0 worlddb

Добавьте в новую БД файл .sql:

Войдите в новое окружение:

Просмотр данных в PostgreSQL

Для начала нужно ознакомиться с тестовой БД. Чтобы просмотреть список таблиц, введите:

\d+
List of relations
Schema | Name | Type | Owner | Size | Description
——-+——————+——-+———-+———+————-
public | city | table | postgres | 264 kB |
public | country | table | postgres | 48 kB |
public | countrylanguage | table | postgres | 56 kB |
(3 rows)

Эта БД содержит три таблицы. Чтобы просмотреть столбцы одной из таблиц (например, city), выполните команду:

\d city
Table «public.city»
Column | Type | Modifiers
————+—————+————
id | integer | not null
name | text | not null
countrycode | character(3) | not null
district | text | not null
population | integer | not null
Indexes:
«city_pkey» PRIMARY KEY, btree (id)
Referenced by:
TABLE «country» CONSTRAINT «country_capital_fkey» FOREIGN KEY (capital) REFERENCES city(id)

На экране представлены данные каждого столбца таблицы, а также взаимосвязи данной таблицы с другими наборами данных.

Запрос данных при помощи select

Для запроса информации в PostgreSQL используется оператор select. Базовый синтаксис выглядит так:

SELECT columns_to_return FROM table_name;

К примеру, если запросить столбцы таблицы country (команда \d country), на экране появится объёмный вывод. Его можно отфильтровать, запросив только необходимые вам данные. Для примера запросите название страны и континент, на котором она находится.

SELECT name,continent FROM country;
name | continent
———————-+—————
Afghanistan | Asia
Netherlands | Europe
Netherlands Antilles | North America
Albania | Europe
Algeria | Africa
American Samoa | Oceania
Andorra | Europe
. . .

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

SELECT * FROM city;
id| name | countrycode | district | population
—+————————————+————-+——————————-+————
1 | Kabul | AFG | Kabol | 1780000
2 | Qandahar | AFG | Qandahar | 237500
3 | Herat | AFG | Herat | 186800
4 | Mazar-e-Sharif | AFG | Balkh | 127800
5 | Amsterdam | NLD | Noord-Holland | 731200
6 | Rotterdam | NLD | Zuid-Holland | 593321
7 | Haag | NLD | Zuid-Holland | 440900
. . .

Определение порядка записей

PostgreSQL позволяет определить порядок вывода запрошенных данных; для этого используется конструкция ORDER BY, идущая после оператора SELECT. Базовый синтаксис выглядит так:

SELECT columns FROM table ORDER BY column_names [ ASC | DESC ];

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

SELECT name,continent FROM country ORDER BY continent;
name | continent
————————-+—————
Algeria | Africa
Western Sahara | Africa
Madagascar | Africa
Uganda | Africa
Malawi | Africa
Mali | Africa
Morocco | Africa
Côte d\u0092Ivoire | Africa
. . .

По умолчанию конструкция ORDER BY позволяет упорядочить вывод данных в порядке возрастания.

Чтобы изменить порядок вывода, нужно добавить в конструкцию ORDER BY оператор DESC. Например:

SELECT name,continent FROM country ORDER BY continent DESC;
name | continent
————————-+—————
Paraguay | South America
Bolivia | South America
Brazil | South America
Falkland Islands | South America
Argentina | South America
Venezuela | South America
Guyana | South America
Chile | South America
. . .

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

К примеру, можно отсортировать вывод по континенту, а затем по названию страны, и в результате получить алфавитный список стран для каждого континента:

SELECT name,continent FROM country ORDER BY continent,name;
name | continent
————————-+—————
Angola | Africa
Burundi | Africa
Benin | Africa
Burkina Faso | Africa
Botswana | Africa
Central African Republic | Africa
Côte d\u0092Ivoire | Africa
Cameroon | Africa
Congo | Africa
. . .

Фильтрация записей PostgreSQL

PostgreSQL предоставляет механизмы для более детальной фильтрации вывода.

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

Один из таких механизмов – предложение where, которое позволяет задавать условия поиска.

К примеру, можно запросить все города Соединённых Штатов, добавив в конец команды конструкцию WHERE countrycode = ‘USA’.

SELECT name FROM city WHERE countrycode = ‘USA’;
name
————————-
New York
Los Angeles
Chicago
Houston
Philadelphia
Phoenix
San Diego
Dallas
San Antonio
. . .

Примечание: Условия поиска для предложения WHERE нужно помещать в одинарные кавычки.

Реляционный оператор like делает поиск ещё более гибким. Этот оператор использует шаблоны:

  • “_” – поиск совпадения по одному символу.
  • “%” – поиск 0 или больше значений.
  • and и or – объединение параметров фильтрации.

Для примера попробуйте запросить названия городов США, которые начинаются на N.

SELECT name FROM city WHERE countrycode = ‘USA’ AND name LIKE ‘N%’;
name
———————
New York
Nashville-Davidson
New Orleans
Newark
Norfolk
Newport News
Naperville
New Haven
North Las Vegas
Norwalk
New Bedford
Norman
(12 rows)

Этот вывод также можно получить в алфавитном порядке:

SELECT name FROM city WHERE countrycode = ‘USA’ AND name LIKE ‘N%’ ORDER BY name;
name
———————
Naperville
Nashville-Davidson
Newark
New Bedford
New Haven
New Orleans
Newport News
New York
Norfolk
Norman
North Las Vegas
Norwalk
(12 rows)

Продвинутое использование select

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

SELECT country.name AS country,city.name AS capital,continent FROM country JOIN city ON country.capital = city.id ORDER BY continent,country;
country | capital | continent
————————+————————————+—————
Algeria | Alger | Africa
Angola | Luanda | Africa
Benin | Porto-Novo | Africa
Botswana | Gaborone | Africa
Burkina Faso | Ouagadougou | Africa
Burundi | Bujumbura | Africa
Cameroon | Yaoundé | Africa
Cape Verde | Praia | Africa
Central African Republic| Bangui | Africa
Chad | N´Djaména | Africa
. . .

Этот запрос состоит из нескольких частей.

В конце этого запроса находится знакомый оператор ORDER BY continent,country, который сортирует вывод по континенту, а затем по названию страны.

Чтобы понять остальные компоненты запроса, нужно ознакомиться с объединением таблиц.

Соединенные таблицы

Предложение JOIN позволяет запрашивать данные из нескольких связанных таблиц одновременно.

Примечание: Таблицы считаются связанными, если они имеют столбец, который относится к одним и тем же данным.

К примеру, в тестовой БД таблицы country и city связаны, поскольку совместно используют некоторые данные. Чтобы увидеть, что таблица country ссылается на таблицу city, введите:

\d country
. . .
. . .
Foreign-key constraints:
«country_capital_fkey» FOREIGN KEY (capital) REFERENCES city(id)
. . .
. . .

Этот вывод говорит о том, что столбец capital таблицы country ссылается на столбец id таблицы city. По сути, можно воспринимать эти две таблицы как одну большую таблицу.

В предложенном выше запросе есть часть:

FROM country JOIN city ON country.capital = city.id

Это выражение значит, что Postgres вернёт данные из обеих таблиц. Предложение JOIN здесь является стандартным оператором соединения (также известен как inner join).

INNER JOIN выводит данные, присутствующие в обеих таблицах. Например, если соединить таблицы, которые не относятся друг к другу как внешние ключи, программа вернёт ошибку, поскольку таблицы содержат не связанные между собой данные. Для такого вывода стандартное предложение join не подходит.

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

В данном случае запрашиваются записи с общими значениями в обеих таблицах.

Заключение

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

Источник

Как вывести список баз данных и таблиц PostgreSQL с помощью psql

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

PostgreSQL поставляется с интерактивным инструментом, psqlкоторый позволяет вам подключаться к серверу и выполнять запросы к нему. При использовании psqlвы также можете воспользоваться его мета-командами. Эти команды полезны для сценариев и администрирования командной строки. Все мета-команды начинаются с обратного слэша без кавычек и также известны как команды обратного слэша.

В этой статье объясняется, как использовать базы данных и таблицы на сервере PostgreSQL psql.

Листинг баз данных

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

Когда пакет PostgreSQL установлен, создается административный пользователь с именем «postgres». По умолчанию этот пользователь может подключиться к локальному серверу PostgreSQL без пароля.

Чтобы получить доступ к psqlтерминалу как пользователь «postgres», запустите:

Команда sudo позволяет запускать команды в качестве другого пользователя.

Из терминала psql выполните мета-команду \l или \list, чтобы вывести список всех баз данных:

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

Сервер PostgreSQL имеет три базы данных, созданные по умолчанию: template0, template1 и postgres. Первые два – это шаблоны, которые используются при создании новых баз данных.

Если вы хотите получить информацию о размерах баз данных, табличных пространствах по умолчанию и описаниях, используйте \l+ или \list+. Размер базы данных будет показан, только если текущий пользователь может подключиться к нему.

Чтобы получить список всех баз данных без доступа к оболочке psql, используйте переключатель -c, как показано ниже:

Другой способ составить список баз данных – использовать следующий оператор SQL:

В отличие от мета-команды \l, приведенный выше запрос покажет только имена баз данных:

Таблицы списков

Чтобы получить список всех таблиц конкретной базы данных сначала необходимо подключиться к нему с помощью мета-команды \c или \connect. Пользователь, вошедший в систему как терминал psql, должен иметь возможность подключаться к базе данных.

Например, чтобы подключиться к базе данных с именем «odoo», вы должны набрать:

Как только база данных переключена, используйте мета-команду \dt для вывода списка всех таблиц базы данных:

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

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

Для получения информации о размерах таблиц и описаний используйте \dt+.

Заключение

Вы узнали, как составлять список баз данных и таблиц PostgreSQL с помощью команды psql.

Не стесняйтесь оставлять комментарии, если у вас есть какие-либо вопросы.

Источник