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