v1.0.0 Summary Chapter 7: Modeling tips and tricks 7. Introduction 7.12. Creating a serial from integer type

7.12. Creating a serial from integer type

As seen in the previous section, pgModeler is capable of creating a sequence object from a serial column, but sometimes you'll need to do the inverse operation: create a serial column from an integer one. The tool also implements this feature by selecting the integer column, right-clicking it, and then select Convert to serial. The result of this operation can be seen in the image below.

This feature is quite handy when you have imported a database using the reverse engineering tool and need to change the auto-increment columns of all tables. The only limitation of this feature is that the default value of the columns must be a call to the function nextval('my_sequence'::regclass) or the column has a sequence object assigned to it. If that is not the case pgModeler will raise an error and refuse to do the conversion. In the case of converting to serial a column that has a sequence attached to it, this latter object will not be automatically removed. It is up to the user to remove the object.

This conversion will create a different serial type according to the integer type assigned to the column. If the column is an integer or int4 the serial type will be assigned. For smallint or int2 the smallserial is used. Lastly, for bigint or int8 the replacement data type will be bigserial.

Jun 27, 2023 at 08:20