Please note the following commands:
\listor\l: list all databases\c <db name>: connect to a certain database\dt: list all tables in the current database using yoursearch_path\dt *.: list all tables in the current database regardless yoursearch_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.
Answer from Frank Heikens on Stack ExchangePlease note the following commands:
\listor\l: list all databases\c <db name>: connect to a certain database\dt: list all tables in the current database using yoursearch_path\dt *.: list all tables in the current database regardless yoursearch_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:
SELECT datname FROM pg_database
WHERE datistemplate = false;
This lists tables in the current database
SELECT table_schema,table_name
FROM information_schema.tables
ORDER BY table_schema,table_name;
database - How to show tables in PostgreSQL? - Stack Overflow
postgresql - How to list databases in terminal in PostgresSQL? - Stack Overflow
postgresql - Databases in psql Don't Show up in PgAdmin4 - Stack Overflow
New database on PostgreSQL server not showing up after CREATE database [database];
Videos
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
Thanks to @Michał Sznurawa for pointing me in the right direction - from the Mac terminal, using psql -l, rather than psql \l does the trick.
This lists all databases:
SELECT datname FROM pg_database;
These list all databases in detail:
\list
\l
These list all databases in more detail:
\list+
\l+
Unlike in pgAdmin3, with pgAdmin4,
here you have to manually connect to a running postgres server and
you already have your specific database (DB) created.
So to set the stage, make sure you have the postgres server is running, and that you have created that DB already too.
Notice (in the image) that I CREATE database XYZ and GRANT all privileges to default user postgres. (Note; to work properly with pgAdmin4, you have to create a user called postgres in order to be able to connect with and log in to pgAdmin4.)
Then here are some quick steps to follow:
- When within pgAdmin4, right-click the Servers option and select create.
Note:
- In the image you'll see (1) next to "Servers" because I have done this process already. Kindly ignore that).
- Select "server group" if you have many servers that you want to better manage. For most basic use cases, go ahead and select "server" (like I did).
- For either option you select above, you'll get a pop-box to complete the "connection process". I selected "server" which is appropriate for your use case (see image below).
Note:
- "name" field is required
- As you can see already, enter a name (I went with "postgres" since it's what I was used to by default in pgAdmin3, but you can enter any name).
- Notice the "connect now" checkbox is checked by default so as soon as the process is successful, your DB should display in the sidemenu. (This is a key to confirm that you entered the right info). But you can always uncheck this, to connect later.
- Now, click connection tab and you see the image below.
The key fields to fill here, to keep it simple, are host name/address and password. Remember to save after entering your info.
Note:
- If on connecting to local machine, localhost or http://127.0.0.1 should do. (I did "localhost")
- If connecting to a DB instance in the cloud e.g. AWS, enter the endpoint in the host space.
Here's more from AWS - A lot of the other fields have the default settings used when installing postgres and pgAdmin.
If you followed the steps above properly, then you should see something like this after you save.
Here's a good guide from the pgAdmin documentation
In case you created your database as template CREATE DATABASE ... IS_TEMPLATE =true, then the database is considered as "system object" and is not shown in the list if PgAdmin4 option "Show system objects?" is set to false.
Try menu File -> Preferences, in the tree find Browser -> Display, and the option is at the bottom.
Another possible way is to remove the template option:
ALTER DATABASE xxx IS_TEMPLATE = false
and then you will see your database without changing PgAdmin preferences.