Thread: removing "serial" from table definitions.

On 6/24/21 8:33 AM, Marc Mamin wrote:
> Hi,
> 
> Is there  a way to change a data type from serial to int?
> 
> I tried with :
> 
>    ALTER TABLE "admin".db_jobs ALTER COLUMN id TYPE int USING id::int;
> 
> But this seems not to change anything, as if Posgres woud consider the 
> statement as a no-op.

serial is not an actual data type -- it is essentially an integer with a 
default and an automatically created sequence. See:

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

> My problem is that "serial" is not exported with pg_dump.
> 
> Creating a db from the dump will hence result into a different table 
> definition (which is equivalent tough)
> 
> We are trying a transfer/migration tool on Azure, that check the table 
> definitions between the source and target before starting the data 
> transfer, and it blocks on that difference.

 From the linked doc above:
--------------
The data types smallserial, serial and bigserial are not true types, but 
merely a notational convenience for creating unique identifier columns 
(similar to the AUTO_INCREMENT property supported by some other 
databases). In the current implementation, specifying:

CREATE TABLE tablename (
     colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
     colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
---------------

I haven't checked, but I am guessing that creating the table using the 
former method results in a dump that looks like the latter? In that 
case, just define the table in the second way to begin with and they 
will match from the migration tools standpoint I should think.

Joe
-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

From: Joe Conway [mailto:mail@joeconway.com] 
>Sent: Donnerstag, 24. Juni 2021 14:47
>To: Marc Mamin <M.Mamin@intershop.de>; pgsql-general <pgsql-general@lists.postgresql.org>
>Subject: Re: removing "serial" from table definitions.
>
>On 6/24/21 8:33 AM, Marc Mamin wrote:
>> Hi,
>> 
>> Is there  a way to change a data type from serial to int?
>> 
>> I tried with :
>> 
>>    ALTER TABLE "admin".db_jobs ALTER COLUMN id TYPE int USING id::int;
>> 
>> But this seems not to change anything, as if Posgres woud consider the 
>> statement as a no-op.
>
>serial is not an actual data type -- it is essentially an integer with a default and an automatically created
sequence.See:
 
>
>https://www.postgresql.org/docs/13/datatype-numeric.html#DATATYPE-SERIAL

Yes, I undersand that serial is just a hint at table creation time, but is there a place in catalog where we can see if
thetable was created using 'serial' ?
 
if yes, I'm looking for a way to remove that.

Another cause for my problem may be in the way how the default value information is stored in pg_attrdef.
The difference we see between the source and target database is that a schema prefix is displayed with the sequence on
oneside, and not on the other..
 
I'm not sure yet if this really come directly from the catalog or from the way how the client read the table
definition,maybe along with some search_path differences
 
Were there any change in this area between PG 9.6 and PG 11 ?

example:

The Default value of column 'id' in table 'db_jobs_history' in database 'oms_db' is different on source and target
servers.
 
It's 'nextval('admin.db_jobs_history_id_seq'::regclass)' on source 
 and 'nextval('db_jobs_history_id_seq'::regclass)' on target.
 
 Thanks
 
 Marc

>serial is not an actual data type -- it is essentially an integer with a default and an automatically created
sequence.See:
 
>>
>>https://www.postgresql.org/docs/13/datatype-numeric.html#DATATYPE-SERIA
>>L
>
>Yes, I undersand that serial is just a hint at table creation time, but is there a place in catalog where we can see
ifthe table was created using 'serial' ?
 
>if yes, I'm looking for a way to remove that.
>
>Another cause for my problem may be in the way how the default value information is stored in pg_attrdef.
>The difference we see between the source and target database is that a schema prefix is displayed with the sequence on
oneside, and not on the other..
 
>I'm not sure yet if this really come directly from the catalog or from the way how the client read the table
definition,maybe along with some search_path differences Were there any change in this area between PG 9.6 and PG 11 ?
 
>
>example:
>
>The Default value of column 'id' in table 'db_jobs_history' in database 'oms_db' is different on source and target
servers.
 
>It's 'nextval('admin.db_jobs_history_id_seq'::regclass)' on source  and 'nextval('db_jobs_history_id_seq'::regclass)'
ontarget.
 

I've probably found the origin of our problem:

https://www.postgresql.org/docs/9.3/release-8-1.html
 => Add proper dependencies for arguments of sequence functions (Tom)
 
But I won't be able to check that in the next few days..

best regards,
marc Mamin

Marc Mamin <M.Mamin@intershop.de> writes:
> Yes, I undersand that serial is just a hint at table creation time, but is there a place in catalog where we can see
ifthe table was created using 'serial' ? 

No.  Where the docs say "these are equivalent", they mean that very
literally.

> The difference we see between the source and target database is that a schema prefix is displayed with the sequence
onone side, and not on the other.. 

This likely has to do with the search_path settings being different in the
sessions inspecting the two DBs.  I do not think it is related to
serial-ness at all, it's just the normal behavior of regclass_out for the
OID constant that's the argument of nextval().

            regards, tom lane