Skip site navigation (1) Skip section navigation (2)

Re: Understanding sequence function

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Understanding sequence function
Date: 2012-08-03 14:32:33
Message-ID: jvgne6$hm1$1@dough.gmane.org (view raw or flat)
Thread:
Lists: pgsql-novice
James David Smith, 03.08.2012 15:59:
> DROP TABLE IF EXISTS test;
> DROP SEQUENCE IF EXISTS serial;
> CREATE TABLE test(
> id INTEGER,
> date_time TIMESTAMP);
> INSERT INTO test (id, date_time)
> VALUES
> ('1', '2012-07-12 10:00:00'),
> ('2', '2012-07-12 10:00:01'),
> ('3', '2012-07-12 10:00:02'),
> ('4', '2012-07-12 10:00:03'),
> ('5', '2012-07-12 10:00:04'),
> ('6', '2012-07-12 10:00:05');
> CREATE SEQUENCE serial start 1;
> SELECT id, date_time, nextval('serial') as serial
> FROM test
> ORDER BY date_time DESC;
> _______________
> The result of the select query is below. What I don't understand is why isn't the sequence going from 1-6? It seems to have used it the wrong way around. I guess it gets the data, does the serial, and then does the order. I don't want it to do this. Is there some way to get the sequence to generate itself while respecting the order of the select statement?
> id       |       date_time                       |      serial
> ------------------------------------------------------------
> 6        | 2012-07-12 10:00:05           |       6
> 5        | 2012-07-12 10:00:04           |       5
> 4        | 2012-07-12 10:00:03           |       4
> 3        | 2012-07-12 10:00:02           |       3
> 2        | 2012-07-12 10:00:01           |       2
> 1        | 2012-07-12 10:00:00           |       1
> Thanks
> James

My assumption is, that the rows are first retrieved from the table (including the "generation" of the sequence numbers using nextval()
That order is not specified.

Then, once those rows are retrieved, they are sorted. As it happens just the other way round in which they were retrieved.
If you do some updates/deletes/inserts into the table you _could_ wind up with something like this:

id       |       date_time               |      serial
------------------------------------------------------------
6        | 2012-07-12 10:00:05           |       3
5        | 2012-07-12 10:00:04           |       5
4        | 2012-07-12 10:00:03           |       6
3        | 2012-07-12 10:00:02           |       1
2        | 2012-07-12 10:00:01           |       4
1        | 2012-07-12 10:00:00           |       2

If you need to have a (guaranteed) consecutive numbering in your result set, use row_number():

SELECT id, date_time, row_number() over (order by date_time ASC) as serial
FROM test
ORDER BY date_time DESC;




In response to

Responses

pgsql-novice by date

Next:From: James David SmithDate: 2012-08-03 15:38:31
Subject: Re: Understanding sequence function
Previous:From: Tom LaneDate: 2012-08-03 14:28:41
Subject: Re: Understanding sequence function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group