Alter data type of a column to serial

Question

In pgsql, is there a way to have a table of several values, and choose one of them (say, other_id), find out what its highest value is and make every new entry that is put in the table increment from that value.

I suppose this was just too easy to have had a chance of working..

ALTER TABLE address ALTER COLUMN new_id TYPE SERIAL  ____________________________________  ERROR:  type "serial" does not exist 

Thanks much for any insight!

Answer 1

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; 

Answer 2

A quick glance at the docs tells you that

The data types smallserial, serial and bigserial are not true types but merely a notational convenience for creating unique identifier columns

If you want to make an existing (integer) column to work as a "serial", just create the sequence by hand (the name is arbitrary), set its current value to the maximum (or bigger) of your current address.new_id value, at set it as default value for your address.new_id column.

To set the value of your sequence see here.

SELECT setval('address_new_id_seq', 10000);

This is just an example, use your own sequence name (arbitrary, you create it), and a number greater than the maximum current value of your column.

---

Update: as pointed out by Lucas' answer (which should be the acccepted one) you should also specify to which column the sequence "belongs to" by using CREATE/ALTER SEQUENCE ... OWNED BY ...