Summary Chapter 7: Modeling tips and tricks 7. Introduction 7.11. Creating a sequence from serial type

7.11. Creating a sequence from serial type

According to PostgreSQL documentation serial types are not real data types, they are only a convenience provided to create unique and auto-increment identifiers for columns. Internally, PostgreSQL converts serial data types into sequence objects and changes the column types to the equivalent integer type. For instance, using the serial type the column data type will be changed to integer, for smallserial the smallint takes place and for bigserial the bigint will be used.

Additionally, in this conversion process a new sequence will be created and assigned to the column which causes a new value from the sequence to be assigned to the column every time a new row is inserted to the table. By default, the generated sequence will be named [table]_[column]_seq. For instance, converting the serial column id from table_a in the image below will result in the sequence named table_a_id_seq. In case of a sequence exists with the same name a numeric suffix will be added to the name to avoid conflicts.

In pgModeler, the serial data type replacement can be quickly done by focusing a serial column in any table, right click it and select Convert to sequence. The result is displayed in the image below. Note that beside converting the data type of the column to integer pgModeler also created the sequence using the same naming pattern as PostgreSQL and associated the new sequence to the column so the auto-increment feature can work properly.

Sep 12, 2018 at 14:09