Newer versions: (from 8.4 - mentioned in release notes)
TABLE mytablename;
Longer but works on all versions:
SELECT * FROM mytablename;
You may wish to use \x first if it's a wide table, for readability.
For long data:
SELECT * FROM mytable LIMIT 10;
or similar.
For wide data (big rows), in the psql command line client, it's useful to use \x to show the rows in key/value form instead of tabulated, e.g.
\x
SELECT * FROM mytable LIMIT 10;
Note that in all cases the semicolon at the end is important.
Answer from Craig Ringer on Stack OverflowNewer versions: (from 8.4 - mentioned in release notes)
TABLE mytablename;
Longer but works on all versions:
SELECT * FROM mytablename;
You may wish to use \x first if it's a wide table, for readability.
For long data:
SELECT * FROM mytable LIMIT 10;
or similar.
For wide data (big rows), in the psql command line client, it's useful to use \x to show the rows in key/value form instead of tabulated, e.g.
\x
SELECT * FROM mytable LIMIT 10;
Note that in all cases the semicolon at the end is important.
Step 1. Check the display mode is "on" by using
\x
Step 2. Don't forget the ;
I tried for fifteen minutes just because I forgot the semicolon.
You can choose to use uppercase for great format looking.
TABLE users;
And you will get something like
show tables in current database using psql
postgresql - How do I list all databases and tables using psql? - Database Administrators Stack Exchange
database - List all entries in a PostgreSQL datatable - Stack Overflow
database - How to show tables in PostgreSQL? - Stack Overflow
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?
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.
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;
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