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
Date: Thu, 15 Sep 2022 11:49:15 -0400
Message-ID: <3321873.1663256955@sss.pgh.pa.us> (raw)
In-Reply-To: <DBBPR04MB79001BF0ED7403DAC63273C7B9499@DBBPR04MB7900.eurprd04.prod.outlook.com>
References: <DBBPR04MB79001BF0ED7403DAC63273C7B9499@DBBPR04MB7900.eurprd04.prod.outlook.com>
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. 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, mrosinger@megavoice.com, pgsql-bugs@lists.postgresql.org
Subject: Re: unexpected results when attempting to define and use column with data type SERIAL
In-Reply-To: <3321873.1663256955@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox