![]() Show Tables in All the Schemas in PostgreSQL The following command returns all the tables in the public schema. Since we can have different schemas holding different databases in PostgreSQL, we can specify the schema we want in our query, and all the tables in that schema will be returned to us. Show Tables in a Specific Schema in PostgreSQL The command returns one row since we only have one table in the database. The \dt command is used in PostgreSQL to describe all tables and is used as shown below. Use \dt Command to Show Tables in PostgreSQL The connection shifts from postgres, the currently connected database, to the employee we want to use. To select the database we want to use, employee, use the following command. On the column labeled Name, we can see three databases employee, postgres, template0, and template1. Template1 | postgres | UTF8 | English_Kenya.1252 | English_Kenya.1252 | =c/postgres Template0 | postgres | UTF8 | English_Kenya.1252 | English_Kenya.1252 | =c/postgres Postgres | postgres | UTF8 | English_Kenya.1252 | English_Kenya.1252 | Name | Owner | Encoding | Collate | Ctype | Access privileges We might have created multiple databases, and we should use the following command to list all the available databases. >psql -U postgresĪ password is prompted, and we should enter the password that we specified during the installation process and press enter. You can install the PostgreSQL database and use the following command to log in to your database. In the MySQL database, the common command you will encounter is SHOW TABLES, but in PostgreSQL, this command is not understood by the database management system. You learned how to compile a list of PostgreSQL databases and tables using the psql - How To Create Lists and Tables If the database is empty, the output shall look like this:įor information on the size of tables and descriptions, use \dt . Public | wizard_ir_model_menu_create | table | odoo Public | web_editor_converter_test_sub | table | odoo Public | base_import_tests_models_char | table | odoo Public | base_import_mapping | table | odoo Public | base_import_import | table | odoo The output will include the number of tables, the name of each table and its scheme, type and owner: Once the database is switched, use the \dt meta command to display a list of all database tables: For example, to connect to a database named “odoo”, you must type: A user logged in as a psql terminal shall be able to connect to the database. To get a list of all tables of a particular database, first you need to connect to it using the meta-command \c or \connect. In contrast to the \l meta command, the above query will only show the names of databases: To obtain a list of all databases without access to the psql shell, use the -c switch as shown below:Īnother way to create a list of databases is to use the following SQL statement: The database size shall be shown only if the current user can connect to it. If you want information about database sizes, default tablespaces, and descriptions, use \l or \list . The first two are templates, which are used when creating new databases. The PostgreSQL server has three default databases: template0, template1 and postgres. Template1 | postgres | C.UTF-8 | C.UTF-8 | =c/postgres Template0 | postgres | C.UTF-8 | C.UTF-8 | =c/postgres Postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | The output shall include the number of databases, the name of each database, its owner, encryption and access privileges: The sudo command allows you to run the commands as another user.įrom the psql terminal, run the meta-command \l or \list to display a list of all databases: ![]() To access the psql terminal as a “postgres” user, run it: By default, this user can connect to the local PostgreSQL server without a password. When the PostgreSQL package is installed, an administrative user named “postgres” is created. To access the psql terminal on behalf of the user you are logged in to, just enter psql. ![]() Depending on the server configuration, the user may need to enter his password to connect to the psql terminal. You can connect to the PostgreSQL server using PSQL command like any system user. All meta-commands start with a backslash without quotes and are also known as backslash commands. These commands are useful for scripting and administering the command line. When using psql, you can also use its meta-commands. PostgreSQL comes with an interactive tool, psql, that allows you to connect to a server and execute queries to it. When administering PostgreSQL database servers, one of the most common tasks you will probably perform is enumerating databases and their tables.
0 Comments
Leave a Reply. |