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
