PostgreSQL Show Tables
Summary: in this tutorial, you will learn how to show tables in PostgreSQL using psql tool and pg_catalog schema.
If you are coming from MySQL, you may want to use the popular SHOW TABLES statement that displays all tables in a specific database.
PostgreSQL does not support the SHOW TABLES statement directly but provides you with an alternative.
Showing tables from PostgreSQL using psql
First, connect to PostgreSQL using the psql tool.
The -U flag stands for the user and -W option requires you to provide the password. In this command, you use the postgres user to log in to the PostgreSQL database server.
Second, enter the password for the user postgres and press the Enter keyboard:
Third, switch to a database e.g. dvdrental :
Note that you can connect to a specific database when you log in to the PostgreSQL database server:
In this command, the -d flag means database. In this command, you connect to the dvdrental database using the postgres user.
Third, use the \dt command from the PostgreSQL command prompt to show tables in the dvdrental database:
To get more information on tables, you can use the \dt+ command. It will add the size and description columns:
Showing tables using pg_catalog schema
Another way to show tables in PostgreSQL is to use the SELECT statement to query data from the PostgreSQL catalog as follows:
In this query, we used a condition in the WHERE clause to filter system tables. If you omit the WHERE clause, you will get many tables including the system tables.
PostgreSQL. 10 продвинутых команд для DBA с примерами
Топ 10 самих популярных команд для управления сервером PostgreSQL для настоящих администраторов баз данных (DBA).
Большинство команд подходят как для консольной утилиты psql , так и для запуска через ваш клиент.
1. Как найти самую большую таблицу в базе данных PostgreSQL?
Результатом будет самая большая таблица (в примере testtable1 ) в страницах. Размер одной страницы равен 8KB (т.е. размер таблицы в примере — 2,3GB)
2. Как узнать размер всей базы данных PostgreSQL?
Результатом будет размер базы данных в байтах:
Если вы хотите получить размер в более читаемом («человеческом») формате — «оберните» результат в функцию pg_size_pretty() :
Ну и сразу логичным будет показать все базы данных в читаемом («человеческом») виде, отсортированные от более больших к меньшим
3. Как узнать размер таблицы в базе данных PostgreSQL?
Результатом будет размер таблицы testtable1, включая индексы. Результат будет отображен сразу в удобном для чтения формате, а не в байтах.
Если вам нужно узнать размер таблицы без индексов, тогда следует выполнить такой запрос:
4. Как узнать текущую версию сервера PostgreSQL?
Результат будет подобным этому:
5. Как выполнить SQL-файл в PostgreSQL?
Для данной цели существует специальная команда в консольной утилите:
Где /path/to/file.sql — это путь к вашему SQL-файлу. Обратите внимание, что он должен лежать в доступной для чтения пользователя postgres директории.
6. Как отобразить список всех баз данных сервера PostgreSQL?
Для данной цели существует специальная команда в консольной утилите:
7. Как отобразить список всех таблиц в базе данных PostgreSQL?
Для данной цели существует специальная команда в консольной утилите что покажет список таблиц в текущей БД.
8. Как показать структуру, индексы и прочие элементы выбранной таблицы в PostgreSQL?
Для данной цели существует специальная команда в консольной утилите:
Где testtable1 — имя таблицы
9. Как отобразить время выполнения запроса в консольной утилите PostgreSQL?
После чего все запросы станут отображаться в консольной утилите со временем выполнения.
Отключаются эти уведомления точно так же, как и включаются — вызовом:
10. Как отобразить все команды консольной утилиты PostgreSQL?
Это наверное самый важный пункт, т.к. любой DBA должен знать как вызвать эту справку ��
How do I list all databases and tables using psql?
I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool.
When I log in with psql —username=postgres , how do I list all databases and tables?
I have tried \d , d and dS+ but nothing is listed. I have created two databases and a few tables with pgAdmin III, so I know they should be listed.
8 Answers 8
Please note the following commands:
- \list or \l : list all databases
- \c <db name> : connect to a certain database
- \dt : list all tables in the current database using your search_path
- \dt *. : list all tables in the current database regardless your search_path
You will never see tables in other databases, these tables aren’t visible. You have to connect to the correct database to see its tables (and other objects).
To switch databases:
\connect database_name or \c database_name
See the manual about psql.
This lists databases:
This lists tables in the current database
In Postgresql these terminal commands list the databases available
Or the command stated more simply:
Those commands print this on the terminal:
These are the available databases.
In PSQL these commands list the tables available
You have to specify a database before you can list the tables in that database.
This brings you to a psql terminal:
Use the command \d meaning show all tables, views, and sequences
Then, to exit the psql terminal, type \q and press enter. Or Ctrl-D does the same thing. These are the tables in that database.
\l is also shorthand for \list . There are quite a few slash commands, which you can list in psql by using \? .
To gain more info on database and table list, You can do :
\l+ to list databases
\d+ to list all tables in current search_path schema in current database.
From pg_Admin you can simply run the following on your current database and it will get all the tables for the specified schema:
This will get you a list of all the permanent tables (generally the tables you’re looking for). You can get just the table names if you change the * wildcard to just the table_name . The public table_schema is the default schema for most databases unless your admin has set up a new schema.
It is possible that you have inserted the tables into a schema that is not in your search path, or the default, ie, public and so the tables will not show up using \dt. If you use a schema called, say, data, you can fix this by running,
alter database <databasename> set search_path=data, public;
Exit and reenter psql and now \dt will show you the tables in schema data too.
I’m surprised nobody’s mentioned this before, but these commands (from the manual here):
command(s) are a great way of learning the internals of PostgreSQL commands!
run psql -E <db_name> and then run (for example):
and the output is then:
So, the -E flag can really give you insight into the internals of PostgreSQL!
Finally, Also, if you already started the psql client (and you don’t want to exit and restart it with -E), you can use \set ECHO_HIDDEN to set the flag on.
Also, and again from the manual cited above, you can do a kind of dry run by setting the value to noexec :
If you set this variable to the value noexec, the queries are just shown but are not actually sent to the server and executed. The default value is off.
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 по умолчанию скрыто, но оно есть в каждой таблице.
Последний запрос требователен к ресурсам, поэтому будьте аккуратны при его выполнении на рабочем проекте.
Теперь рассмотрим случай, когда повторяются значения полей.
Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:
Если данные важны, то сначала нужно найти записи с дубликатами:
Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение 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. Но это далеко не все возможные приёмы. Если вы сталкивались с интересными задачами, напишите о них в комментариях. Поделимся полезным опытом!