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 into the table. By default, the generated sequence will be named [table]_[column]_seq
. For instance, converting the serial column id
of the table 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-clicking it, and selecting Convert to sequence
. The result is displayed in the image below. Note that besides 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.