Order Now

Sequence

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.

PostgreSQL Hosting Table of contents :

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.

Top web hosting with PostgreSQL sequence supportSequence 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

How to create a PostgreSQL sequence via the 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

Database Hosting


Choose a Database
Choose a Hosting Package
 
200 GB Disk Space
1500 GB Monthly Traffic
3 PostgreSQL database
160 MB PostgreSQL Storage
15 MySQL databases
150 MB MySQL Storage

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.

Resources:


1 2 3 4