![]() That’s it! You now know how to list databases and tables in PostgreSQL. Get a List Of Tables ProgrammaticallyĪs you can see above, we get detailed information about the tables. Apart from the base tables, there are multiple views are also present in the database which we hide in the result using the WHERE clause. Here, the details of all tables reside in the information_schema database. tables WHERE table_catalog= 'pagila' AND table_type= 'BASE TABLE' AND table_schema NOT IN ( 'pg_catalog', 'information_schema') Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Note that, you must be connected to the database in order to list the tables.Īlternatively, you can use another way to get the list of tables using the programmatic way. This write-up demonstrated different methods to list all the tables in PostgreSQL.\dt Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) List All Tables Of Database The “\dt” or “\dt ” commands will be executed from the SQL Shell psql, however, you can run the pg_catalog or information_schema from any interface of your choice, such as pgAdmin or psql. In Postgres, an SQL command: “\dt”, and built-in schemas: pg_catalog and information_schema, are used to list all the tables of a database. This way, users can get the list of all relations using the information schema. Let’s run the below-given command to get the list of all tables: SELECT * FROM information_schema.tables PostgreSQL List All Tables Using information_schema The output shows that this time we get only user-defined tables. ![]() To retrieve only user-defined tables, execute the below query: SELECT * Step #4: List User-Defined Tables Using pg_catalog The output shows that the above query fetched/listed all the tables, including system tables. Let’s run the below command to list all the tables using the pg_catalog schema: SELECT * Right-click on the selected database and select the Query tool:Ĭlicking on the “Query Tool” will open the query tool where you can execute any query of your choice. Let’s open the pgAdmin and provide the superuser password as shown below:Ĭlicking on the OK button will log you into the pgAdmin. Follow the below-listed steps to show/list all the tables using pg_catalog schema: Postgres provides a schema named pg_catalog that is used to list all the available tables of a database. PostgreSQL List All Tables Using pg_catalog This way, you can list all the relations using the psql tool. Step #5: List All Tables With Detailed InformationĮxecute the below command to get detailed information about each table, such as the table’s size, persistence, etc. Once you are connected to the desired database, you can run the below command to list all the tables/relations of that particular database: \dt Ĭongratulations! You have successfully listed all the relations/tables of the desired database using SQL SHELL. The output indicates that we are successfully connected to the “example” database. Suppose we want to access the “example” database for that purpose, we will run the following command: \c example Step #3: Establish a Connection With the Desired Database The output shows the list of all the available databases. The above snippet indicates that we are successfully connected to the default database, i.e., “postgres”.Įxecuting the below-mentioned command will show all the available databases: \l Hit the “ENTER” button after specifying the appropriate password consequently, the following interface will appear: Here are stepwise instructions to list all the tables of a particular database using SQL SHELL:įirstly, open the SQL SHELL and provide all the necessary details, such as port number, user name, and super user password, as shown in the following snippet: PostgreSQL List All Tables Using SQL SHELL(psql) ![]() In this post, you will learn how to list all the available tables using SQL SHELL(psql) or pgAdmin. In addition to this, Postgres facilitates its user with some built-in schemas, such as pg_catalog and information_schema that are used to list all the tables. Postgres offers another useful command named “\dt ” that provides some extra/detailed information about the tables, such as table size, access method, etc. ![]() PostgreSQL provides a “\dt” command to list all the available tables of a database.
0 Comments
Leave a Reply. |