So far, we have explored PostgreSQL, learned how to access its psql shell, created a basic database, and verified user credentials. In this lesson, we will delve into some of the psql commands that we can execute before moving on to SQL.
The psql shell provides a variety of commands that enable us to perform various operations on the database, such as creating tables, inserting data, querying data, and modifying table schema. By using psql commands, we can interact with the database and get useful insights into its structure and contents.
In the upcoming lessons, we will learn how to write SQL queries to retrieve data from the database and perform various operations on it. However, before that, we need to understand how to use psql commands to navigate and manage the database.
So, let’s explore some of the essential psql commands that we can use inside the psql shell.
Principles of Writing Code in SQL
Principles of writing high-quality SQL code:
- Use Consistent Casing – Always use a consistent casing for SQL commands, keywords, and identifiers. SQL commands should be written in uppercase letters, while all other identifiers like table names, column names, and variables should be in lowercase.
- Use Meaningful and Descriptive Names – Use meaningful and descriptive names for tables, columns, and variables that reflect their purpose and content. This makes the code easier to read and understand, especially for others who may work with the code in the future.
- Avoid Using Reserved Keywords – Avoid using SQL reserved keywords as table or column names. If you have to use a reserved keyword, wrap it in quotes or brackets to make it clear that it’s an identifier and not a command.
- Use Whitespace and Indentation – Use whitespace and indentation to make your code more readable and organized. Indent your SQL statements to indicate their relationship and use line breaks to separate different clauses of a statement.
- Comment Your Code – Add comments to your SQL code to explain the purpose and functionality of each statement or block of code. This makes it easier for others to understand and maintain the code in the future.
- Use Parameterized Queries – Always use parameterized queries to avoid SQL injection attacks and to ensure data integrity. Parameterized queries separate the SQL code from the data values, making it harder for malicious users to manipulate the SQL code.
- Test Your Code – Always test your SQL code thoroughly before deploying it to production. Check for syntax errors, data inconsistencies, and performance issues. Use test data that covers all possible scenarios and edge cases.
By following these principles, you can write SQL code that is easy to read, understand, and maintain, and that ensures data integrity and security.
PSQL Commands Usage
PSQL is an interactive interface program for communicating with PostgreSQL, and it has several flags to control the connection.
Some of the essential flags are:
-h– Specifies the host to be contacted either by IP address or by name if the DNS server can recognize it.-U– Specifies the username through which we want to connect.-p– Specifies the port we want to connect through (the default is 5432).
For example, to connect to a PostgreSQL server running on the localhost with the username “username“, we can use the following command:
$ psql -h localhost -U username
Alternatively, we can use an entire text string as a single medium containing the entry parameters to the database, as shown below:
$ psql "dbname=dbname host=127.0.0.1 user=postgres password=password port=5432 sslmode=require"
For instance, we can execute SQL queries, create tables, alter table schema, and manage users and permissions using the psql commands. To get help on the available commands, we can type “help” at the prompt.
Using Exiting PostgreSQL Database
In this series, we will be designing multiple tables for our database. However, to expedite the process and avoid creating some tables and including the data contained within, we will use an existing database that is available for download.
Follow these steps in the Psql shell (not within the Postgres shell):
First, Connect to the Psql shell with:
$ sudo -i -u postgres
Then download the data by running the following curl command:
$ curl -L -O https://share.getcloudapp.com/7KudYo9Y/download
Next, create a new database in Postgres with the following command:
createdb geeksveda
Upload the downloaded data to the database using the following command:
$ pg_restore --no-owner --dbname geeksveda download
Here’s a breakdown of the different components of the command:
- pg_restore is the command used to restore a PostgreSQL database.
--no-owneris an option that tells pg_restore to exclude any ownership-related commands from the backup file. This can be useful if the backup file was created on a different server and you don’t want to import ownership information.--dbname geeksvedaspecifies the name of the database that you want to restore the backup file into.- download is the name of the backup file that you want to restore.
Note: We will discuss the pg_restore command and the createdb command in later lessons.
To connect to the geeksveda database, you can use one of the following commands:
$ psql --dbname geeksveda OR \c geeksveda
Get a List of PostgreSQL Databases
To view a list of all stored databases, you can use the command \l in PostgreSQL, which will display the database name, owner, encoding, collation, character type, and access privileges.
To obtain additional information about each database, you can use the \l+ command, which will show a detailed list of all the databases, including the size and the number of tables and indexes in each database.
Here is an example of the \l command output:
If you want to get more detailed information about the databases, use the "\l+" command:
This output provides additional information about each database, including its size, the number of tables, and the type and owner of each database.
Retrieving PostgreSQL Database Tables
To retrieve a list of tables in a database, you can use the command \dt, which will display a table with the schema, name, type, and owner of each table in the database. For example:
To describe a specific table in more detail, use the command \d followed by the name of the table, which will display the column names, data types, and any constraints or indexes on the table. For example:
To display more information about the tables or users, add a "+" symbol after the command. For example, \dt+ will display additional information about each table, while \dg+ will display more details about each user role.
Get PostgreSQL Help
To display the documentation for the SELECT statement in SQL, we can use the following command:
\h SELECT
This command will provide information on the syntax, usage, and options available for the SELECT statement in SQL. It can be helpful for beginners or those who need a quick reference while working with SQL queries.
Exit Postgres Shell
To exit the Postgres shell (PSQL), use the command \q, which will allow you to quit the shell and return to your command prompt or terminal.
Conclusion
In this post, we have covered a wide range of topics related to PostgreSQL and the psql shell. We have discussed how to create a basic database, verify user credentials, and use psql commands to perform various operations on the database.
Additionally, we have explained the principles of writing high-quality SQL code, which include using consistent casing, meaningful and descriptive names, whitespace and indentation, comments, parameterized queries, and testing.
Furthermore, we have provided guidance on how to connect to an existing database, retrieve a list of databases, and how use essential flags to control the connection in PostgreSQL. By following these tips and best practices, you can become proficient in managing and querying databases using PostgreSQL and the psql shell.
Overall, this post aims to provide a comprehensive guide to working with PostgreSQL and the psql shell, and we hope it has been helpful in expanding your knowledge and skills in this area.
