Psql
Jump to navigation
Jump to search
1) Connect to PostgreSQL database The following command connects to a database under a specific user. After pressing Enter PostgreSQL will ask for the password of the user. psql -d database -U user -W Code language: SQL (Structured Query Language) (sql) For example, to connect to dvdrental database under postgres user, you use the following command: C:\Program Files\PostgreSQL\9.5\bin>psql -d dvdrental -U postgres -W Password for user postgres: dvdrental=# Code language: SQL (Structured Query Language) (sql) If you want to connect to a database that resides on another host, you add the -h option as follows: psql -h host -d database -U user -W Code language: SQL (Structured Query Language) (sql) In case you want to use SSL mode for the connection, just specify it as shown in the following command: psql -U user -h host "dbname=db sslmode=require" Code language: SQL (Structured Query Language) (sql) 2) Switch connection to a new database Once you are connected to a database, you can switch the connection to a new database under a user specified by user. The previous connection will be closed. If you omit the user parameter, the current user is assumed. \c dbname username The following command connects to dvdrental database under postgres user: postgres=# \c dvdrental You are now connected to database "dvdrental" as user "postgres". dvdrental=# Code language: PHP (php) 3) List available databases To list all databases in the current PostgreSQL database server, you use \l command: \l Code language: SQL (Structured Query Language) (sql) 4) List available tables To list all tables in the current database, you use \dt command: \dt Code language: SQL (Structured Query Language) (sql) Note that this command shows the only table in the currently connected database. 5) Describe a table To describe a table such as a column, type, modifiers of columns, etc., you use the following command: \d table_name Code language: SQL (Structured Query Language) (sql) 6) List available schema To list all schemas of the currently connected database, you use the \dn command. \dn Code language: SQL (Structured Query Language) (sql) 7) List available functions To list available functions in the current database, you use the \df command. \df Code language: SQL (Structured Query Language) (sql) 8) List available views To list available views in the current database, you use the \dv command. \dv Code language: SQL (Structured Query Language) (sql) 9) List users and their roles To list all users and their assign roles, you use \du command: \du Code language: SQL (Structured Query Language) (sql) 10) Execute the previous command To retrieve the current version of PostgreSQL server, you use the version() function as follows: SELECT version(); Code language: SQL (Structured Query Language) (sql) Now, you want to save time typing the previous command again, you can use \g command to execute the previous command: \g Code language: SQL (Structured Query Language) (sql) psql executes the previous command again, which is the SELECT statement,. 11) Command history To display command history, you use the \s command. \s Code language: SQL (Structured Query Language) (sql) If you want to save the command history to a file, you need to specify the file name followed the \s command as follows: \s filename Code language: SQL (Structured Query Language) (sql) 12) Execute psql commands from a file In case you want to execute psql commands from a file, you use \i command as follows: \i filename Code language: SQL (Structured Query Language) (sql) 13) Get help on psql commands To know all available psql commands, you use the \? command. \? Code language: SQL (Structured Query Language) (sql) To get help on specific PostgreSQL statement, you use the \h command. For example, if you want to know detailed information on ALTER TABLE statement, you use the following command: \h ALTER TABLE Code language: SQL (Structured Query Language) (sql) 14) Turn on query execution time To turn on query execution time, you use the \timing command. dvdrental=# \timing Timing is on. dvdrental=# select count(*) from film; count ------- 1000 (1 row) Time: 1.495 ms dvdrental=# Code language: SQL (Structured Query Language) (sql) You use the same command \timing to turn it off. dvdrental=# \timing Timing is off. dvdrental=#