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