This article is part of our Academy Course titled PostgreSQL Database Tutorial.
In this course, we provide a compilation of PostgreSQL tutorials that will help you set up and run your own database management system. We cover a wide range of topics, from installation and configuration, to custom commands and datatypes. With our straightforward tutorials, you will be able to get your own projects up and running in minimum time. Check it out here!
In our previous post (PostgreSQL: Introduction and installation) we explained how to install and use a desktop and a web-based client to query a sample database we created and populated. We also introduced two basic commands to connect to a database (\c followed by the database name) and to quit (\q) the PostgreSQL prompt.
PostgreSQL commands
In this tutorial we will introduce you to other useful PostgreSQL-specific (psql for short from now on) commands. To do so, let’s open the psql prompt by switching to the postgres Linux account and typing psql in the command line.
Getting help
Once in the psql prompt, type help and press Enter. The output should be similar to Fig. 1:
The above figure shows the following tips – make sure you remember because they will come in handy more than once. If you need help with SQL commands, first off type \h to view a list of the available options. Once you have identified the command you need help with, use q to return to the psql prompt and then type \h followed by the SQL command you have chosen. For example, let’s say we chose ALTER USER. To see the help for that command, do
\h ALTER USER
as shown in Fig. 2:
On the other hand, if you get stuck with a database management task, do \? and you will see the available psql commands grouped by categories, as seen in Fig. 3 (some of them are highlighted in yellow – the output is truncated for the sake of space):
As before, type q to exit the help and return to the psql prompt.
Displaying databases and tables
If you find yourself examining a database server you haven’t previously worked with, or if you are not familiar with the structure of a given database, you may want to start off by listing the databases and their respective tables.
To list the databases, simply do \l
To view the tables in the World_db database (which is the one we imported in our previous tutorial), connect to it and type \dt
Keep in mind that you can switch from a given database to another (Alberdi in the following example) with \c Alberdi
The above commands are shown in Fig. 4 below:
With the psql commands above we have learned to how to list databases and switch between one and another, how to list tables, and how to get help if we get stuck along the way.
PostgreSQL data types
As a preparation to creating our own databases and tables from scratch (which we will cover in an upcoming tutorial), we need to know how what are the allowed built-in, general-purpose data types for table fields. The PostgreSQL 9.5 documentation lists the following data types and more:
a) Numeric types (with corresponding storage sizes and ranges) are listed in Fig. 5:
You will often choose a numeric type for fields that will store amounts of items, grade results, etc.
b) Character types (see Fig. 6):
These types are used to store regular (English) text or character strings, typically resulting from user interaction.
c) Date/time types (see Fig. 7):
These data types are used to indicate the date and or time when an event has been recorded in the database. If you require to store the time zone, there’s a dedicated type for that as well.
d) Binary type (see Fig. 8):
You will often use this type to indicate true/false, active/inactive, and enabled/disabled statuses.
Knowing the allowed ranges for each data type is essential to choosing the right type for fields. It is also critical as far as disk usage is concerned, as a 2-byte integer (smallint) will occupy less space than a 4-byte one (integer). As a rule of thumb, only use a “larger” data type if and only if a smaller type is not likely to scale well with the expected use and growth of the database in terms of record numbers.
Also, the length of character fields must be taken into account while planning -for example- a web application that will gather data through forms or other types of input. While in certain cases users should not be allowed to enter text of indefinite length, you should plan ahead so that they can still enter all that is necessary. Although form validation and sanitization are out of the scope of this tutorial, you definitely will want to make sure that your application does not present security holes and is not abuse-prone.
Enumerated types
Besides the general purpose data types, PostgreSQL allows us to create our own data types in the form of static, ordered set of values (for example, the months of the year, or the days of the week), similarly to the enum type supported in several programming languages. We will see the benefit of enumerated types when we create our first database and start inserting data into it.
Summary
Now that you have learned how to use basic psql commands and have reviewed the most used data types, we are better prepared to dive deeper into PostgreSQL database administration. Stay tuned for the next tutorial!
