Вопрос: Показать таблицы в PostgreSQL


Что эквивалентно show tables(из MySQL) в PostgreSQL?


1154


источник


Ответы:


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

\dt

Программно (или из psqlинтерфейс тоже, конечно):

SELECT * FROM pg_catalog.pg_tables;

Системные таблицы хранятся в базе данных pg_catalog.


1691



Вход как суперпользователь:

sudo -u postgres psql

Вы можете перечислить все базы данных и пользователей по \lcommand (список других команд \?).

Теперь, если вы хотите видеть другие базы данных, вы можете изменить пользователя / базу данных \cкоманда вроде \c template1, \c postgres postgresи использовать \d, \dtили \dSвидеть таблицы / представления / и т. д.


126



(Для полноты)

Вы также можете запросить (SQL-стандарт) информационная схема :

SELECT
    table_schema || '.' || table_name
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
AND
    table_schema NOT IN ('pg_catalog', 'information_schema');

91



  1. First login as postgres user:

    sudo su - postgres

  2. connect to the required db: psql -d databaseName

  3. \dt would return the list of all table in the database you're connected to.


38



Running psql with the -E flag will echo the query used internally to implement \dt and similar:

sudo -u postgres psql -E

postgres=# \dt       
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name", 
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
    AND n.nspname <> 'pg_catalog'
    AND n.nspname <> 'information_schema'
    AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;        
**************************

29



If you only want to see the list of tables you've created, you may only say:

\dt

But we also have PATTERN which will help you customize which tables to show. To show all including pg_catalog Schema, you can add *.

\dt *

If you do: \?

\dt[S+] [PATTERN] list tables


21



First Connect with the Database using following command

\c database_name

And you will see this message You are now connected to database database_name and them run the following command

SELECT * FROM table_name;

In database_name and table_name just update with your database and table name


16



You can use PostgreSQL's interactive terminal Psql to show tables in PostgreSQL.

1. Start Psql

Usually you can run the following command to enter into psql:

psql DBNAME USERNAME

For example, psql template1 postgres

One situation you might have is: suppose you login as root, and you don't remember the database name. You can just enter first into Psql by running:

sudo -u postgres psql

In some systems, sudo command is not available, you can instead run either command below:

psql -U postgres
psql --username=postgres

2. Show tables

Now in Psql you could run commands such as:

  1. \? list all the commands
  2. \l list databases
  3. \conninfo display information about current connection
  4. \c [DBNAME] connect to new database, e.g., \c template1
  5. \dt list tables
  6. \q quit psql

13



If you are using pgAdmin4 in PostgreSQL, you can use this to show the tables in your database:

select * from information_schema.tables where table_schema='public';

10



use only see a tables => \dt

if want to see schema tables =>\dt+

if you want to see specific schema tables =>\dt schema_name.*


9



Note that \dt alone will list tables in the public schema of the database you're using. I like to keep my tables in separate schemas, so the accepted answer didn't work for me.

To list all tables within a specific schema, I needed to:

1) Connect to the desired database:

psql mydb

2) Specify the schema name I want to see tables for after the \dt command, like this:

\dt myschema.*

This shows me the results I'm interested in:

               List of relations
 Schema   |       Name      | Type  |  Owner   
----------+-----------------+-------+----------
 myschema | users           | table | postgres
 myschema | activity        | table | postgres
 myschema | roles           | table | postgres

8