CODEWITHSUNDEEP
sqlpostgresql
1252748
1252748

Reputation: 15372

Alter data type of a column to serial

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!

Upvotes: 60

Views: 84962

Answers (4)

Vasilii Suricov
Vasilii Suricov

Reputation: 954

Full answer to copy-paste is:

CREATE SEQUENCE "address_new_id_seq";
SELECT setval('address_new_id_seq', (SELECT MAX("new_id") FROM "address"));    
ALTER TABLE "address"
   ALTER COLUMN "new_id" SET DEFAULT nextval('address_new_id_seq'),
   ALTER COLUMN "new_id" SET NOT NULL;

p.s. no need MAX(id) + 1, nextval is already next

Upvotes: 2

Sergey Novopoltsev
Sergey Novopoltsev

Reputation: 511

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.

Upvotes: 40

Lucas
Lucas

Reputation: 2627

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;

Upvotes: 54

leonbloy
leonbloy

Reputation: 75906

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

Upvotes: 28

Related Questions