In-order sequence generation

by

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:

More Related Contents:

  1. Why does PostgreSQL perform sequential scan on indexed column?
  2. How to reset sequence in postgres and fill id column with new data?
  3. How to reset postgres’ primary key sequence when it falls out of sync?
  4. PostgreSQL LIKE query performance variations
  5. Calculating and saving space in PostgreSQL
  6. Passing user id to PostgreSQL triggers
  7. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  8. I forgot the password I entered during postgres installation
  9. how to emulate “insert ignore” and “on duplicate key update” (sql merge) with postgresql?
  10. Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
  11. How to generate the “create table” sql statement for an existing table in postgreSQL
  12. Postgres password authentication fails
  13. Generate series of dates – using date type as input
  14. Copy a few of the columns of a csv file into a table
  15. Postgres query to check a string is a number
  16. ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user
  17. How do you create a random string that’s suitable for a session ID in PostgreSQL?
  18. Joining Results from Two Separate Databases
  19. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  20. Update timestamp when row is updated in PostgreSQL
  21. Postgresql query array of objects in JSONB field
  22. PostgreSQL next value of the sequences?
  23. Multicolumn index on 3 fields with heterogenous data types
  24. Select from a table variable
  25. PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values
  26. input file appears to be a text format dump. Please use psql
  27. Hashing a String to a Numeric Value in PostgreSQL
  28. Spring Docker container cannot access Postgres Docker container
  29. Format specifier for integer variables in format() for EXECUTE?
  30. PostgreSQL composite primary key

Leave a Comment