13

I have a database in Postgresql, which was migrated from SQL Server (only data).
On SQL Server, a table from this database has these columns:

measure_id
datum
measure

where measure_id is auto-incremental primary key, datum is datetime and measure is float.
After migration in Postrgresql, measure_id is column of type bigint.

How can I change this column (measure_id) to bigserial and assign it as primary key, now that my table is full of data?

Improve this question
19

Create a sequence and use it as the default value for the column:

create sequence measures_measure_id_seq
   owned by measures.measure_id;

alter table measures
   alter column measure_id set default nextval('measures_measure_id_seq');

commit;

That essentially what serial does.

See the manual for details:
http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL

Improve this answer
5
  • Ok, thanks. Can you just drop a note what does owned by measures.measure_id do? – zetah Oct 9 '14 at 6:06
  • @zetah: Quote from the manual (the link I added): "the sequence is marked as "owned by" the column, so that it will be dropped if the column or table is dropped" – a_horse_with_no_name Oct 9 '14 at 6:11
  • 2
    Ah, all right it's just like a decoration. I mentioned that table already has data, but newly created sequence wont respect already present data and will start counting from 1, which will error on first new insert for unique constraint. So if anyone else is reading this it's more correct to use CREATE SEQUENCE seq START WITH x where x is last row (SELECT MAX(measure_id)+1 FROM measures in my case) – zetah Oct 9 '14 at 6:23
  • 1
    @zetah Thanks for this update. How would you write a query in that case? I tried CREATE SEQUENCE seq START WITH (SELECT MAX(measure_id)+1 FROM measures); but this does not seem to be the correct way. – inquisitive Mar 1 '16 at 11:46
  • What you're trying to do is : select setval('sequenceX', (select max(table_id)+1 from table), false) Found here : stackoverflow.com/questions/2022559/… – HydrUra Apr 26 '19 at 9:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.