From the psql command line interface,
First, choose your database
\c database_name
Then, this shows all tables in the current schema:
\dt
Programmatically (or from the psql interface too, of course):
SELECT * FROM pg_catalog.pg_tables;
The system tables live in the pg_catalog database.
From the psql command line interface,
First, choose your database
\c database_name
Then, this shows all tables in the current schema:
\dt
Programmatically (or from the psql interface too, of course):
SELECT * FROM pg_catalog.pg_tables;
The system tables live in the pg_catalog database.
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:
\?list all the commands\llist databases\conninfodisplay information about current connection\c [DBNAME]connect to new database, e.g.,\c template1\dtlist tables of the public schema\dt <schema-name>.*list tables of certain schema, e.g.,\dt public.*\dt *.*list tables of all schemas- Then you can run SQL statements, e.g.,
SELECT * FROM my_table;(Note: a statement must be terminated with semicolon;) \qquit psql
What about this query (based on the definition of information_schema.tables given in the manual)?
SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE';
Column Type Description table_typecharacter_dataType of the table: BASE TABLEfor a persistent base table (the normal table type),VIEWfor a view,FOREIGNfor a foreign table, orLOCAL TEMPORARYfor a temporary table
If you want list of database
SELECT datname FROM pg_database WHERE datistemplate = false;
If you want list of tables from current pg installation of all databases
SELECT table_schema,table_name FROM information_schema.tables
ORDER BY table_schema,table_name;
This will list all tables the current user has access to, not only those that are owned by the current user:
select *
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema')
and table_schema not like 'pg_toast%'
(I'm not entirely sure the not like 'pg_toast%' is actually needed though.)
I you really need the owner information, you probably need to use pg_class and related tables.
Edit: this is the query that includes the owner information:
select nsp.nspname as object_schema,
cls.relname as object_name,
rol.rolname as owner,
case cls.relkind
when 'r' then 'TABLE'
when 'm' then 'MATERIALIZED_VIEW'
when 'i' then 'INDEX'
when 'S' then 'SEQUENCE'
when 'v' then 'VIEW'
when 'c' then 'TYPE'
else cls.relkind::text
end as object_type
from pg_class cls
join pg_roles rol on rol.oid = cls.relowner
join pg_namespace nsp on nsp.oid = cls.relnamespace
where nsp.nspname not in ('information_schema', 'pg_catalog')
and nsp.nspname not like 'pg_toast%'
and rol.rolname = current_user --- remove this if you want to see all objects
order by nsp.nspname, cls.relname;
The short answer to the question would be:
SELECT *
FROM pg_tables t
WHERE t.tableowner = current_user;
Hello, I'm running a psql command inside of my postgres container. When running the \dt to show my database tables it's always giving me "Did not find any relations". Im pretty new in psql commands, am I missing something?