To connect to the database from the terminal screen;
psql -d database_name -U user_name -W Psql -d dvdrental -U postgres -W
To connect to the database on another machine;
psql -h host -d database_name -U user_name -W
When you connect to a database, you can change the connection to a new database under a user specified by the user. The previous link is closed. If you omit the user parameter, it is assumed to be the current user.
\c db_name user_name
\c dvdrental
To list the database names;
\l
To list the table names;
\dt
To list all the schemas of the connected database;
\dn
To list the views of the current database;
\dv
To list all users and assignment roles;
\du
To learn the version of PostgreSQL;
SELECT version();
To run the previous command again;
\g
To see the command history;
\s
To save the command history to a file;
\s file_name
To run psql commands from a file;
\i file_name
To see all available psql commands;
\?
You can use the \h command to get help with a particular PostgreSQL statement. For example, if you want to get detailed information about the ALTER TABLE statement, you can use the following command.
\h ALTER TABLE
To open the query execution time;
\timing
When you run the same command again, the timing is turned off.
To edit the command in the editor;
\e
After issuing the command, psql opens the text editor defined by your EDITOR environment variable and places the last command you entered in psql into the editor. After typing and saving the command in the editor and closing the editor, psql will execute the command and return the result.
psql supports some types of output formats and allows you to customize how the output is formatted on the fly.
\a command
Switches from aligned column output to unaligned column output.
To format the output in HTML format;
\H
To exit psql;
\q
Alternatively, the following commands can be used to output the query result as HTML.
– In Linux, a file with .html extension is created.
touch test.html
The following commands are run sequentially in psql.
\H
The file path is provided, where the result will be saved.
\o /data/postgresql/test.html
The query whose result is to be saved is executed.
SELECT rental_id, MIN(amount) FROM payment GROUP BY rental_id ORDER BY rental_id DESC;
The html extension file can be copied to the Windows environment as described in chapter 9.