PostgreSQL Tutorial | psql usage-Alibaba Cloud Developer Community

2019-03-22 1088

+ Follow to continue viewing

this article provides a series of common psql commands to help you quickly and efficiently obtain data from the database.

Connect to PostgreSQL database

1. Use psql to connect to the database and press enter to prompt you to enter a password.

[postgres@localhost ~]$ psql -dtest -Upostgres -W Password for user postgres: psql (10.7) Type "help" for help. test=# \q

2. If you want to connect to a remote host, you can use the-h option.

[postgres@localhost ~]$ psql -h10.110.9.85 -dtest -Upostgres -W
Password for user postgres: 
psql (10.7)
Type "help" for help.


3. After connecting to the database, you can switch to a new user and the original connection is automatically disconnected. If you ignore the username parameter, the current user is used.

[postgres@localhost data]$ psql
psql (10.7)
Type "help" for help.

postgres=# \c test
You are now connected to database "test" as user "postgres".

4. View all database names

postgres=# \l

5. List the tables in the current database

postgres=# \dt

6. View the table description

postgres=# \d table_name

7. View all schemas in the current database

postgres=# \dn

  1. view all functions in the current database


9. View all views in the current database

postgres=# \dv

10. View all roles in the current database

postgres=# \du

11. Run the previous command to query the current database version

postgres =# SELECT version(); Use \g to run the previous command, that is, SELECT version();postgres =# \g

12. View all psql commands

postgres=# \?

13. View the usage of specific commands. For example, if you want to know the details of ALTER TABLE commands, you can run the following command:

postgres=# \h ALTER TABLE

14. Enable the display command execution time

test=# \timing
Timing is on.
test=# select version();
PostgreSQL 10.7

Time: 83.978 ms

15. Use the system editor to edit commands

test=# select count(*) from baby;


16.psql supports the output of formatted commands, or allows you to customize the output of columns without borders.


output HTML files


17. Output the execution result to the file

test=#\o /tmp/test.outtest=# select version();

18. Execute the SQL script

postgres=# \i sql.sql
 PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 201506
23 (Red Hat 4.8.5-36), 64-bit
(1 row)


SQL relational database database PostgreSQL C LANGUAGE data security/privacy protection data modeling Linux
developer Community> database > article
Selected, One-Stop Store for Enterprise Applications
Support various scenarios to meet companies' needs at different stages of development

Start Building Today with a Free Trial to 50+ Products

Learn and experience the power of Alibaba Cloud.

Sign Up Now