Introduction to PostgreSQL List Tables
Whenever you work on the database, it is very common to retrieve the tables and their structure present in your database as it gives you a general idea of what are all the entities that are being stored in your database. Whenever you create a new database in PostgreSQL, it contains many default tables, which are called the system tables. These tables are used internally by the database to perform operations and store system variables, operators and functions. Other than this, the system tables also maintain the data about SQL and strategic parameters that decide how any operation will be executed and the main constraints to be taken care of while executing any command. Also, information about which tables are locked currently for updation if any query of retrieval is fired on that table is maintained in system tables.
Other than the system tables, we can create many user-defined tables for our usage. This article will learn various methods that can be used to retrieve the tables present in our database and list them out.
Listing Out Tables in PostgreSQL
In case if you are familiar with MySQL before then, you must be thinking to use the query of SHOW TABLES; to retrieve the list of all the tables in the current database. However, in PostgreSQL, there is no such query. In PostgreSQL, we can retrieve the list of tables by either using \dt command when you are using psql or retrieve the list of tables using the SELECt query from the pg_tables table of pg_catalog schema. We will see how we can use both of these methods one by one. For this, you will first need to open the terminal and login to PostgreSQL with your username with command
sudo su – postgres
and then enter your password. And press enter. Then open the psql shell by typing the command
psql
and then click on enter. The output will be somewhat like shown below.
The next step is to check all the databases present in your current database server. For this, you can either type
\l or \list
the command to list out all databases and schemas. It will list out all the schemas and databases, and the output will be like this.
The three databases, Postgres, template0 and template1, are default databases, while the demo database was created by me before. For creating a new database, you can use the command createdb and the name of the database. For example, let us create a database named educational_platform. For this, we will fire the following command.
createdb educational_platform
Let us check whether our database is created by entering \l command
\l
Now to switch between database and use the database of your choice, you can use the command \c or \connect and the name of the database to which you want to connect. Now, fire command
\c educational_platform
The output will be as follows:
Listing Out Tables using psql metacommands
Now we will learn how to list the tables using psql metacommands. Metacommands are short commands that are compact and easy to use for database administrators as the psql evaluates this and converts it to SQL queries if required and stored in system tables. We can use either
\dt or \dtables
metacommand to list out the user-created and user-defined tables. The output will be as follows –
As there are no tables present in our newly created database its saying that Did not find any relations. Let’s create a new table and check whether it is getting retrieved or not.
CREATE TABLE educba
(id INTEGER PRIMARY KEY,
technologies VARCHAR,
workforce INTEGER,
address VARCHAR);
Now, let us check whether the table is added by entering the command
\dt or \dtables
So we can see now that our table is added successfully.
Listing Out Tables using SELECT query
Now we will learn how we can use the simple SQL query of SELECT to retrieve information about all the tables present in our current database. The information of all the tables is stored in the table named pg_tables of pg_catalog schema. Now, as we are going to fire the query on the table present in some other database or schema than the one we are currently connected to, we will need to specify the name of the schema explicitly, and then dot(.) separated table name to list out the tables. So our query should be like this.
select * from pg_catalog.pg_tables;
The output of the above query will be somewhat like shown below.
It will be a long list displaying all the tables that are system tables and user-defined tables. What if we only want to retrieve user-defined tables and not the system tables. For this, we will have to apply the constraint in the WHERE clause specifying the tables should not belong to pg_catalog or information_schema schemas as these two schemas only have internal system tables present in them. So our query should be arranged in the way, as shown below.
SELECT * FROM pg_catalog.pg_tables
WHERE schemaname != 'information_schema' AND
schemaname != 'pg_catalog';
The output of the above query will be as shown below.
As it can be seen that only the user-defined table “educba” created by us is retrieved, which is the expected output. The pg_tables table displays table name and many properties related to the table, which includes schemaname, tableowner, tablespace, hasindexes, hasrules, hastriggers and rowsecurity. Most of the fields’ answers have the values t and f, which are boolean type parameters specifying whether the table has that property or not. For example, in our table, the column hastriggers have value f, which means that the current table educba does not have any triggers defined on it. Similar is the case for other columns. Besides this, the columns schemaname and tableowner contain the table belongs to which type of schema and who owns this table.
Conclusion
In PostgreSQL, we can list the tables in two ways: using the psql meta-commands of simple SELECT clause query on the table pg_tables of pg_catalog schema. Both these queries result in the same output. The difference is just that the metacommand returns only user-created tables while the SELECT query results in the system and user-defined tables. We need to apply constraints to the SELECT query to list only user-created tables.
Recommended Articles
This is a guide to PostgreSQL List Tables. Here we also discuss the Introduction and listing out tables in postgresql and using a select query. You may also have a look at the following articles to learn more –
