PostgreSQL

PostgreSQL commands and datatypes

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:

Accessing the built-in PostgreSQL help
Figure 1 – Accessing the built-in PostgreSQL help

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:

Getting help about SQL commands
Figure 2 – Getting help about SQL commands

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):

Getting help with psql commands
Figure 3 – Getting help with psql commands

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:

Listing databases and tables
Figure 4 – Listing databases and tables

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:

Numeric data types
Figure 5 – Numeric data types

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.

Figure 6 - Character data types
Figure 6 – Character data types

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.

Date/time data types
Figure 7 – Date/time data types

d) Binary type (see Fig. 8):

You will often use this type to indicate true/false, active/inactive, and enabled/disabled statuses.

Binary data type
Figure 8 – Binary data type

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.

The use of data types, among other things, contributes to data consistency in a table by ensuring that a given field will only accept the type of data it is configured to store.

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!

Gabriel Canepa

Gabriel Canepa is a Linux Foundation Certified System Administrator (LFCS-1500-0576-0100) and web developer from Villa Mercedes, San Luis, Argentina. He works for a worldwide leading consumer product company and takes great pleasure in using FOSS tools to increase productivity in all areas of his daily work. When he's not typing commands or writing code or articles, he enjoys telling bedtime stories with his wife to his two little daughters and playing with them, the great pleasure of his life.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button