Custom Sequence In Postgres

February 26, 2017

I almost got frustrated this weekend after trying to mass update a column in Postgres with sequential numbers for each row. The table (basic_matrices) has over ten thousand rows and I just generated a migration that adds a position column to the table which defaults to zero (0).

The goal is to have this column populated with sequential numbers ranging from 1, 2, 3,… to the number of rows in the table.

Here is how I solved that problem:

CREATE SEQUENCE basic_matrices_position_seq
ALTER SEQUENCE basic_matrices_position_seq RESTART WITH 1

UPDATE basic_matrices bm
SET position = nextval(‘basic_matrices_position_seq’)
FROM (SELECT id FROM basic_matrices ORDER BY id) sub

DROP SEQUENCE basic_matrices_position_seq

First, I created a named sequence in postgres.

Next, I reset the sequence to start counting from 1.

After, which I updated the each row position by using the nextval() postgres function which takes in a sequence name as argument.

Finally, I dropped the sequence because I want to be in charge of updating the position column and not postgres.

If you are wondering why I needed the subquery, that is because I wanted to update the position column based on the result set of the sub query.

Hope this helps someone. Thanks for stopping by.

Damian Simon Peter

Hi, I am Damian Simon Peter a software developer based in Waterloo, Ontario. I have spent the past years working for early-stage startups, building backend and frontend applications primarily using Ruby and JavaScript. I own a System76 Galago Pro running Pop!_OS and absolutely love it.