CODEWITHSUNDEEP
sqlpostgresql
djq
djq

Reputation: 15286

Creating a sequence on an existing table

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.

Upvotes: 32

Views: 74686

Answers (3)

Robson Rocha
Robson Rocha

Reputation: 21

In PostgreSQL:

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

Upvotes: 2

mu is too short
mu is too short

Reputation: 434665

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;

Upvotes: 49

Jason 'Bug' Fenter
Jason 'Bug' Fenter

Reputation: 1624

I'm not fluent in postgresql so I'm not familiar with the "CREATE SEQUENCE" statement. I would think, though, that you're adding the column definition correctly. However, adding the column doesn't automatically insert data for existing rows. A DEFAULT constraint is for new rows. Try adding something like this afterwards to populate data on the existing rows.

DECLARE @i Int
SET @i = 0
SET ROWCOUNT 1
WHILE EXISTS (SELECT 1 FROM test WHERE rid IS NULL) BEGIN
   UPDATE test SET rid = @i WHERE rid IS NULL
END
SET ROWCOUNT 0

Upvotes: -3

Related Questions