From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Rosinger <mrosinger@megavoice.com>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: unexpected results when attempting to define and use column with
 data type SERIAL
In-reply-to: 
 <DBBPR04MB79001BF0ED7403DAC63273C7B9499@DBBPR04MB7900.eurprd04.prod.outlook.com>
References: 
 <DBBPR04MB79001BF0ED7403DAC63273C7B9499@DBBPR04MB7900.eurprd04.prod.outlook.com>
Comments: In-reply-to Michael Rosinger <mrosinger@megavoice.com>
	message dated "Thu, 15 Sep 2022 14:43:26 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3321872.1663256955.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 15 Sep 2022 11:49:15 -0400
Message-ID: <3321873.1663256955@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3321873.1663256955%40sss.pgh.pa.us>
Precedence: bulk

Michael Rosinger <mrosinger@megavoice.com> writes:
>   1.  Description of issue:               I have had some problems with =
the data type SERIAL while working in the environment described above. The=
re 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 PgAdmi=
n, 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 r=
ecognize 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-SER=
IAL

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 specifyin=
g the PK column as SERIAL, the CREATE statement worked, but afterwards whe=
n reviewing the table properties in PgAdmin, the data type showed as INTEG=
ER 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 define=
d as "1 to 2147483647", yet when adding rows to the table and specifying t=
he value for the PK (SERIAL) column as either 0 (zero) or any negative num=
ber, the INSERT was accepted and the defined range limitation was NOT enfo=
rced. In this instance I want the DB to enforce that the value must be a p=
ositive 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 de=
scribed 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