From: Joe Conway [mailto:mail@xxxxxxxxxxxxx] >Sent: Donnerstag, 24. Juni 2021 14:47 >To: Marc Mamin <M.Mamin@xxxxxxxxxxxx>; pgsql-general <pgsql-general@xxxxxxxxxxxxxxxxxxxx> >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 the 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 one side, 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
