53

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!

leonbloy
  • 73,180
  • 20
  • 142
  • 190
1252748
  • 14,597
  • 32
  • 109
  • 229
  • 1
    Maybe you could explain a bit more about why you need to add a new serial identifier to a table that presumably already has a primary key? Do you want to replace the existing primary key with the serial identifier? What's the background of all this? – Craig Ringer May 10 '13 at 03:50
  • @CraigRinger In my last question, I was having trouble importing a db. I decided that since my system needs some significant upgrades, to deal with that during business hours tomorrow. There are only a few tables that I need access to. So I brought a table over, but the sequence which controlled an incrementing column within that table is what was causing the problem in the transfer. I wasn't having luck altering the column to be a primary key, so I thought I could make it a "serial" starting with the table's current highest value. Would it be easier to make a new col, and make it the PrimKey? – 1252748 May 10 '13 at 04:06
  • Best to link to any prior questions that're required for relevant context. How'd you "transfer" it? "wasn't having any luck" in what sense? Exact commands, exact error messages etc. – Craig Ringer May 10 '13 at 04:08
  • @CraigRinger This was an attempt to add a new column that could serve as a primary key: `ALTER TABLE address ADD PRIMARY KEY (primary_id);`. Which resulted in the error: `ERROR: column "primary_id" named in key does not exist` – 1252748 May 10 '13 at 04:10
  • So there was no existing unique key data on the original table that you could add a primary key constraint to, and you're trying to add a [*surrogate primary key*](http://en.wikipedia.org/wiki/Surrogate_key) instead? – Craig Ringer May 10 '13 at 04:13
  • Oh, I didn't connect you to the previous question you asked; I see enough people here that new names quickly escape me. You're the guy with PostgreSQL 8.1. Others: context is at http://stackoverflow.com/questions/16473630/syntax-error-when-trying-to-import-database-from-two-postgresql-databases . Sounds like you just need to fix the SQL dump so it loads correctly into 8.1, not mess around with all these weird workarounds. Fix the dump so that when you load the table the primary key is created correctly. Simply removing the `OWNED BY` clause should be fine in this case. – Craig Ringer May 10 '13 at 04:16

3 Answers3

53

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;
mu is too short
  • 426,620
  • 70
  • 833
  • 800
Lucas
  • 2,587
  • 1
  • 18
  • 17
  • i should have mentioned that this is a quick fix until I can upgrade my (just found out how) ancient version of postgre. When I try to do `OWNED BY` I get a syntax error.. – 1252748 May 10 '13 at 04:08
  • @thomas, so what is your postgres version? – Lucas May 10 '13 at 04:48
32

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.

Steven
  • 1,733
  • 2
  • 16
  • 30
Sergey Novopoltsev
  • 431
  • 4
  • 4
25

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

leonbloy
  • 73,180
  • 20
  • 142
  • 190
  • okay. What does it mean to "set a sequence's current value"? I'm looking into sequences now, but they are new to me. Do you mean to say that through a sequence I can create my own "notational convenience for unique identifier columns"? – 1252748 May 10 '13 at 03:39
  • A column of serial (pseudo)type is just an integer column that has as default value the value of some sequence (and increments it). I addded the statement to set the value. – leonbloy May 10 '13 at 03:46