Search code examples
postgresqldense-rankpostgresql-13

dense_rank with setval on serial column (query in PSQL 13)

I'm trying to insert data in a table named test defined by the following schema:
CREATE TABLE test (s INTEGER, p INTEGER, o INTEGER, id SERIAL NOT NULL)

Then I run this query to set the start value for the column id: SELECT setval('test_id_seq', 12)

Finally I insert with this query: INSERT INTO test SELECT s, p, o, dense_rank() over (order by p) from test;

The problem is that I obtain the following:

 s | p | o | id
---+---+---+----
 4 | 6 | 2 |  1
 5 | 6 | 8 |  1
 4 | 7 | 3 |  2
 5 | 7 | 1 |  2

Instead of:

 s | p | o | id
---+---+---+----
 4 | 6 | 2 | 13
 5 | 6 | 8 | 13
 4 | 7 | 3 | 14
 5 | 7 | 1 | 14

How can I make the dense_rank() function starting at my 'test_id_seq'? I'm using PostgreSQL 13.

Solution

  • As mentioned by @Stefanov.sm

    The solution is to use dense_rank() over (order by p) + currval('test_id_seq') in the insert statement.