No. Since there is no natural order of rows in a database table, all you have to work with is the values in your table.
Well, there are the Postgres specific system columns cmin
and ctid
you could abuse to some degree.
The tuple ID (ctid
) contains the file block number and position in the block for the row. So this represents the current physical ordering on disk. Later additions will have a bigger ctid
, normally. Your SELECT statement could look like this
SELECT *, ctid -- save ctid from last row in last_ctid
FROM tbl
WHERE ctid > last_ctid
ORDER BY ctid
ctid
has the data type tid
. Example: '(0,9)'::tid
However it is not stable as long-term identifier, since VACUUM
or any concurrent UPDATE
or some other operations can change the physical location of a tuple at any time. For the duration of a transaction it is stable, though. And if you are just inserting and nothing else, it should work locally for your purpose.
I would add a timestamp column with default now()
in addition to the serial
column …
I would also let a column default populate your id
column (a serial
or IDENTITY
column). That retrieves the number from the sequence at a later stage than explicitly fetching and then inserting it, thereby minimizing (but not eliminating) the window for a race condition – the chance that a lower id
would be inserted at a later time. Detailed instructions:
- Auto increment table column