AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Postgresql commands see tables1/2/2024 We can only expect to retrieve the user-defined table “educba,” which we created. The output of the above query will be as shown below. WHERE schemaname != 'information_schema' AND We should arrange our query in the following manner: SELECT * FROM pg_catalog.pg_tables For this, we will have to apply the constraint in the WHERE clause specifying that the tables should not belong to pg_catalog or information_schema schemas, as these two schemas only have internal system tables in them. What if we only want to retrieve user-defined tables, not system tables. It will be a long list displaying all the tables that are system tables and user-defined tables. The above query will produce an output similar to the one shown below. When querying a table located in a different database or schema than the one we are currently connected to, we need to explicitly specify the schema name followed by a dot (.) and the table name to retrieve the list of tables. All table information is stored in the pg_tables table of the pg_catalog schema. 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. So we can see now that our table has been added successfully. We can check if the table was added by entering the command: \dt or \dtables Let’s create a new table and check if we can retrieve it. Since there are no tables present in our newly created database, the system is stating that it did not find any relations. Metacommand to list out the user-created or user-defined tables. 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. Now we will learn how to list the tables using psql metacommands. Listing Out Tables using psql metacommands Now, fire command \c educational_platform Now to switch between databases 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. Let’s verify if our database has been created by entering the \l command. For this, we will fire the following command. For example, let us create a database named educational_platform. You can use the command createdb and the database name to create a new database. The default databases are Postgres, template0, and template1, while I created the demo database previously. It will list out all the schemas and databases, and the output will be like this. The command to list all databases and schemas. The next step is to check all the databases present in your current database server. The output will be somewhat like the one shown below. Then open the psql shell by typing the command psqlĪnd then click on enter. For this, you will first need to open the terminal, log in to PostgreSQL with your username and command sudo su – postgres We will see how we can use both of these methods one by one. 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. However, in PostgreSQL, there is no such query. 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.
0 Comments
Read More
Leave a Reply. |