Creating a sequence on an existing table

Question

How can I create a sequence on a table so that it goes from 0 -> Max value? I've tried using the following SQL code, but it does not insert any values into the table that I am using:

CREATE SEQUENCE rid_seq; ALTER TABLE test ADD COLUMN rid INTEGER; ALTER TABLE test ALTER COLUMN rid SET DEFAULT nextval('rid_seq'); 

The table I am trying to insert the sequence in is the output from another query. I can't figure out if it makes more sense to add the sequence during this initial query, or to add the sequence to the table after the query is performed.

Answer 1

Set the default value when you add the new column:

create sequence rid_seq; alter table test add column rid integer default nextval('rid_seq'); 

Altering the default value for existing columns does not change existing data because the database has no way of knowing which values should be changed; there is no "this column has the default value" flag on column values, there's just the default value (originally NULL since you didn't specify anything else) and the current value (also NULL) but way to tell the difference between "NULL because it is the default" and "NULL because it was explicitly set to NULL". So, when you do it in two steps:

1. Add column.
2. Change default value.

PostgreSQL won't apply the default value to the column you just added. However, if you add the column and supply the default value at the same time then PostgreSQL does know which rows have the default value (all of them) so it can supply values as the column is added.

By the way, you probably want a NOT NULL on that column too:

create sequence rid_seq; alter table test add column rid integer not null default nextval('rid_seq'); 

And, as a_horse_with_no_name notes, if you only intend to use rid_seq for your test.rid column then you might want to set its owner column to test.rid so that the sequence will be dropped if the column is removed:

alter sequence rid_seq owned by test.rid; 

Answer 2

In PostgreSQL:

UPDATE your_table SET your_column = nextval('your_sequence') WHERE your_column IS NULL;