removing "serial" from table definitions.

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@intershop.de

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

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 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

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-SERIA
L

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.

I've probably found the origin of our problem:

https://www.postgresql.org/docs/9.3/release-8-1.html
=> Add proper dependencies for arguments of sequence functions (Tom)

But I won't be able to check that in the next few days..

best regards,
marc Mamin

Marc Mamin <M.Mamin@intershop.de> writes:

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' ?

No. Where the docs say "these are equivalent", they mean that very
literally.

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..

This likely has to do with the search_path settings being different in the
sessions inspecting the two DBs. I do not think it is related to
serial-ness at all, it's just the normal behavior of regclass_out for the
OID constant that's the argument of nextval().

regards, tom lane