PostgreSQL

Postgresql sequences

Postgresql is a database management system that works on the principle of a client-server model. It is proven to be a very flexible and open-source database system. For this purpose, it is used in many scenarios, applications, etc., like online banking software, as it supports integrated transactions. Postgresql is also in demand for web applications. It includes many features that work together for its sustainability. One of them is a sequence. Today we will discuss the working and creation of sequences in PostgreSQL.

A sequence is a schema-oriented object that the user creates. It often tends to generate the sequence of integers that depends on the specifications. For sequences, we create them by using a special create sequence command. While creating the CREATE SEQUENCE, some special sequence objects are created. They are single-row tables. These objects are used to provide unique numbers for the rows of tables. A sequence in PostgreSQL assures that there can not be any session or any other call to the next inside the same session can have the same number present in the sequence.

Prerequisites

There are some basic requirements to proceed with the implementation process. You need to have PostgreSQL (any version, but try to have the latest one so that you can make use of all the features present in it). We are implementing sequences on Windows 10. So you need to install it by downloading its setup from the internet and configuring PostgreSQL on Windows 10. Once it is installed, it can easily be configured by providing a user password and other essential details. You can implement the queries on PostgreSQL shell(psql) or pgAdmin. We have implemented these examples on pgAdmin.

Syntax

As the syntax is quite big, unlike others, so we need more time in its description. Sequence_name is the name of the new sequence that is created after the sequence clause. The term [if not] is like an if-else statement that checks the presence of a sequence with the same name.

[ AS { SMALLINT | INT } ] is used to specify the data type in the sequence. [increment [by] increment] is used to have the value that is added to the current sequence so that a new sequence is created. The default value is 1.

[MINVALUE minvalue | NO MINVALUE] and [MAXVALUE maxvalue | NO MAXVALUE] show the maximum and minimum values. If there is no value for both, then the default value is selected. [START [with] start] this is for the starting value of the sequence. Whereas the cache is used to determine how many sequences are stored in the memory to make the access faster. The next term is the CYCLE | NO CYCLE that allows you to start the value in the condition when the limit is exceeded.

Now we will share some examples here. We have used pgAdmin for this purpose. You may use the shell of PostgreSQL.

When you open the pgAdmin panel, you need to provide the password to proceed further.

Example 1

The first step is to create a sequence by using a create command. After the sequence keyword, the name of the sequence is written. We provide the starting number, and the number of times we want increment is also mentioned here.

The output area shows the message that the sequence is created. We will use the nextval keyword in the command with the select statement to see the value. The name of the sequence is written as a parameter.

You can see that in the first call, it shows the initial value. If we carry on repeating the next function call, the value will be incremented every time. After the third time, the output will be:

Example 2

If you want to display the values in descending order, or if you want to decrement the initial value, we will use the following syntax and values.

Here the sequence name is used as ‘hi’. The incremented value is negative 1, as we want the output in descending order; that’s why -1 is used here. The value where to start is written with the minvalue, and the point where it will last is written in maxvalue. Starting position will be mentioned after that. And to repeat, we will use the keyword cycle.

For the first time, like the previous illustration, the first value is displayed. as we again run that call, you will see the decremented value.

Example 3

This example includes the use of sequence along with the table columns. The method of generating a sequence with columns is the same as making a table. We have taken 3 attributes here.

Now the next step creates another sequence with the column name of the table store.

Now insert the values by using an insert statement in the sequence named store.

Now we will see the entered values by using the select statement.

In this way, you can create sequences by using the table column.

Enlist all sequences

To list out all the sequences present in the database, we can use a command in a pg_class.

Delete the sequence table

The table of sequence can be deleted through a drop command.

# DROP TABLE store;

Create sequence through PgAdmin GUI in one step

Expand the database, and then expand the schemas; you will find a point named as sequences. On right-clicking, a further drop-down is opened and now select create.

An interface for creating a sequence will be opened; in the ‘definition’ portion, you can add the values to continue.

Conclusion

This article defines PostgreSQL, its works with examples of how to create a simple sequence and a sequence with the column of a table. It is easier to install and implement PostgreSQL on Windows as compared to any other operating system to run all the features and services of this database management system.

About the author

Saeed Raza

Hello geeks! I am here to guide you about your tech-related issues. My expertise revolves around Linux, Databases & Programming. Additionally, I am practicing law in Pakistan. Cheers to all of you.