Re: removing "serial" from table definitions.

[Johannes Paul <treeclimate@xxxxxxxxx>]

From what I know, serial is just used to setup a table but it is then converted to int in the table. Therefore, you probably cannot remove it since it is not there any more anyway.

To setup table with int instead of serial, you could use this:

CREATE SEQUENCE table_name_id_seq;

CREATE TABLE table_name (
    id integer NOT NULL DEFAULT nextval('table_name_id_seq')
);

ALTER SEQUENCE table_name_id_seq
OWNED BY table_name.id;

instead of

CREATE TABLE table_name(
    id SERIAL
);

as explained on

https://www.postgresqltutorial.com/postgresql-serial/

Am Do., 24. Juni 2021 um 14:33 Uhr schrieb Marc Mamin <M.Mamin@xxxxxxxxxxxx>:

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.

 

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.

 

best regards,

Marc Mamin