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

> 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