When working with databases, there are a lot of special little tools, which can make your work a lot easier and save you a lot of time. And one of these tools in PostgreSQL is the PostgreSQL Sequence.
The PostgreSQL Sequence
The sequence is a special type of data created to generate unique numeric identifiers in the PostgreSQL database. Most often used for the creation of artificial primary keys, sequences are similar but not identical to AUTO_INCREMENT in MySQL. The sequence objects (also known as sequence generators or simply sequences) are single-row tables created via a command from the command line: CREATE SEQUENCE. The sequence objects are most often used for the creation of unique identifiers between the rows in the table. The 'sequence' function offers a simple and safe multi-user method for extracting sequence values from sequence objects.
Sequence objects (also known as sequence generators or simply sequences) are single-row tables created via a command from the command line: CREATE SEQUENCE. Sequence objects are most often used for the creation of unique identifiers between the rows in the table. The 'sequence' function offers a simple and safe multi-user method for extracting sequence values from sequence objects. The sequences are most often used via the serial pseudotype. This is a special data type that encodes information, indicates how the column values will be generated by consulting the sequence, creates new sequence objects, and sets default column values. Because the sequence always creates non-NULL values, it adds a NOT NULL constraint to the columns.
Create a sequence in PostgreSQL
Sequences in PostgreSQL are very easily created via the command line with the “CREATE SEQUENCE” command. You can either execute it via the PostgreSQL command line or via the phpPgAdmin tool included in all our web hosting plans.
An example of how to Create a Sequence in phpPgAdmin
An example of how to Create Sequence in PostgreSQL
CREATE SEQUENCE phonebook_id_seq;CREATE TABLE phonebook(id INTEGER DEFAULT NEXTVAL('phonebook_id_seq'), phone VARCHAR(32), firstname VARCHAR(32), lastname VARCHAR(32));
INSERT INTO phonebook(phone, firstname, lastname) VALUES('+1 123 456 7890', 'Easy', 'Peasy');
INSERT INTO phonebook(phone, firstname, lastname) VALUES('+1 124 457 7891', 'Lemon', 'Squeeze');
Deleting PostgreSQL sequences
To delete a PostgreSQL sequence, you can use the same command as that for deleting tables - DROP. Before removing a sequence, make sure that no other tables or functions are using it, since they will be affected if the sequence is removed.
How to check if there are other tables or functions, depending on the sequence in PostgreSQL
SELECT p.relname, a.adsrc FROM pg_class p JOIN pg_attrdef a ON (p.relfilenode = a.adrelid) WHERE a.adsrc ~ '"phonebook_id_seq'";An example of how to Delete Sequence in PostgreSQL
ALTER TABLE phonebook ALTER COLUMN id SET DEFAULT NULL;DROP SEQUENCE phonebook_id_seq;
An example of output of Select in PostgreSQL
SELECT * FROM phonebook;id | phone | firstname | lastname
----+------------------+-----------+----------
1 | +1 123 456 7890 | Easy | Peasy
2 | +1 124 457 7891 | Lemon | Squeeze
(2 rows)
Using sequences
In PostgreSQL there are several special functions, which are specifically designed to be used with sequences. Here is a list of the most commonly used commands.
nextval(' sequence_name ') - this command will increment the value of the specified sequence and return the new value as an integer
currval(' sequence_name ') - this command will return the last returned value from the "nextval" command. If the nextval still hasn't been used, no value will be returned
setval(' sequence_name ', n) - the "setval" command will set the current value of the sequence to n.
Resources: