Alter data type of a column to serial

Look into postgresql documentation of datatype serial. Serial is only short hand.

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

This happened because you may use the serial data type only when you are creating a new table or adding a new column to a table. If you'll try to ALTER an existing table using this data type you'll get an error. Because serial is not a true data type, but merely an abbreviation or alias for a longer query.

In case you would like to achieve the same effect, as you are expecting from using serial data type when you are altering existing table you may do this:

CREATE SEQUENCE my_serial AS integer START 1 OWNED BY address.new_id;

ALTER TABLE address ALTER COLUMN new_id SET DEFAULT nextval('my_serial');

1. The first line of the query creates your own sequence called my_serial. The OWNED BY statement connects the newly created sequence with the exact column of your table. In your case the table is address and the column is new_id. The START statement defines what value this sequence should start from.

2. The second line alters your table with the new default value, which will be determined by the previously created sequence.

It will give you the same result as you were expecting from using serial.