Re: unexpected results when attempting to define and use column with data type SERIAL

Michael Rosinger <mrosinger(at)megavoice(dot)com> writes:
> 1. Description of issue: I have had some problems with the data type SERIAL while working in the environment described above. There are several different issues but all related to data type SERIAL.
> * When attempting to change a PK column from INTEGER to SERIAL in an existing (populated) table using the table Properties dialog in PgAdmin, the choice of SERIAL could not be entered and the option did not appear in the dropdown list of data types.
> * When attempting to modify the same column's data type using an SQL script with ALTER TABLE ALTER COLUMN, the query failed as it did not recognize the data type "serial".

That would be because SERIAL is indeed not a type. It's a shorthand that
CREATE TABLE recognizes for creating an integer column with a specific
default expression, as explained at

https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

We have not made ALTER TABLE, or any other commands, understand
SERIAL in the same way. It's a bit of a legacy feature, now
that the SQL standard has defined other syntax for the same task.

> * When rebuilding the same table using CREATE TABLE and specifying the PK column as SERIAL, the CREATE statement worked, but afterwards when reviewing the table properties in PgAdmin, the data type showed as INTEGER with a DEFAULT of "nextval('suppliers_supplier_id_seq'::regclass)".

That's exactly what it's supposed to do.

> * According to the documentation (https://www.postgresql.org/docs/14/datatype-numeric.html#DATATYPE-SERIAL) the range for SERIAL is defined as "1 to 2147483647", yet when adding rows to the table and specifying the value for the PK (SERIAL) column as either 0 (zero) or any negative number, the INSERT was accepted and the defined range limitation was NOT enforced. In this instance I want the DB to enforce that the value must be a positive INTEGER, but it failed to do that.

What that's referring to is the default properties of the attached
sequence generator. The underlying column is just integer, so it
will willingly store any integer. If you want an additional
range constraint, you could attach a CHECK constraint to the column.

> 1. I do not believe this is correct behavior for a SERIAL column, but I may be mistaken. I have searched for articles on the symptoms I have described but so far have not found anything that even resembles it.

You seem to not have read the documentation section you cite, or
else it's so poorly written that you didn't absorb what it was
trying to say. Can you suggest a rewording that would make it
clearer?

regards, tom lane