2

I have an existing table with records in it and I've just added a new column ver which I would like to be unique.

create table foo (
  bar text,
  ver integer
);

select * from foo;

bar   ver
---   ---
one   null
two   null
three null

I'm struggling with how to do this for some reason.

I want to do something like:

update foo set ver = ( select generate_series(1, 1000) );

or maybe 

update foo set ver = v from (select generate_series(1, 1000) as v );

...but of course neither of those work. Can anyone point out the obvious for me?

klin
  • 112,967
  • 15
  • 204
  • 232
Andy Jones
  • 1,074
  • 1
  • 10
  • 21
  • 1
    If you want that to be unique for future rows as well, then use a sequence –  Oct 09 '17 at 13:42
  • @a_horse_with_no_name -- no, I don't need (or want) that. I just need to ensure that the existing records become unique. (In the my actual code I'm writing an SQL migration that could be applied to a production database: add the column, fill the column, add a unique index.) – Andy Jones Oct 09 '17 at 13:44

3 Answers3

5

You need a primary key (or a unique column) to individually update rows of a table. In the lack of such a column you can use the hidden column ctid which is unique by definition, example:

update foo f
set ver = rn
from (
    select ctid, row_number() over (order by ctid) as rn
    from foo
    ) s
where f.ctid = s.ctid
klin
  • 112,967
  • 15
  • 204
  • 232
  • Well, that certainly works, but I think it will take me a while to understand why... in fact my real table does have a unique id and I've currently solved the problem with `update foo set ver = id;` -- but I can't believe that that's the only or best solution! – Andy Jones Oct 09 '17 at 14:00
  • You can use `id` instead of `ctid`. The solution gives consecuttive numbers starting from 1. Of course, a simple assigning `id` is the best option if it meets all your requirements. – klin Oct 09 '17 at 15:32
  • Tick. This is the neatest answer and it's very clever. (Does it worry anyone else that we have to resort to this level of cleverness, though?) – Andy Jones Oct 11 '17 at 09:53
1

Use some PL/pgSQL code:

DO
$$DECLARE
   c CURSOR FOR SELECT * FROM foo;
   x foo;
   i integer := 1;
BEGIN
   OPEN c;
   LOOP
      FETCH c INTO x;
      EXIT WHEN NOT FOUND;
      UPDATE foo SET ver = i WHERE CURRENT OF c;
      i := i + 1;
   END LOOP;
END;$$;

Then you can add a unique constraint:

ALTER TABLE foo ADD UNIQUE(ver);
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
0

First, create a sequence.

 create sequence foo_ver;

then add the column with a default value of the next number in the sequence.

 alter table foo add ver integer default nextval('foo_ver') not null;

data is auto-populated, and new records will automatically get a new, unique number.

Optional: create unique index to be sure.

 create unique index on foo(ver);
Joe Love
  • 5,594
  • 2
  • 20
  • 32
  • I have already said that I specifically do not want to do that. Going forward, this column will not be a simple sequence. It will be filled by the application. I just need a way to populate the existing records -- as I said in the comments to the question. – Andy Jones Oct 10 '17 at 07:48
  • Question: Why don't you need new records to also be unique? – Joe Love Oct 10 '17 at 14:32
  • For more information, please re-read my original comment to your answer. ;) – Andy Jones Oct 10 '17 at 16:04
  • 1
    This solution should work for that, as if you provide the ver column during insert, it will not use the sequence. As an added measure, if you want, you can change the default value for ver (or remove it) and drop the sequence if you want. This is just a quick way – Joe Love Oct 10 '17 at 16:11