I am looking for a way to order table entries during insertion rather than fetching. If this is not possible, is there a way to have an auto-increment respective to the primary key? (i.e. if id is primary, ai will increment starting from 0 for every different id inserted)


id | ai
 0 | 0
 0 | 1
 0 | 2
 1 | 0
 1 | 1
 1 | 2
 1 | 3
  • 2
    What exactly do you mean by “order table entries during insertion”? Also, the word for “table entry” is “row”. – Basil Bourque Feb 1 '18 at 7:51
  • 3
    What do you mean by "starting from zero for every inserted Id"? if id is the primary key it will be another one for each row? You need to explain your problem a bit better and maybe add an example – Tom V Feb 1 '18 at 9:25
  • I have added an example demonstrating the behaviour I'm trying to replicate. – Exa Feb 3 '18 at 4:48
  • How is id a primary key in that sample data? – Tom V Feb 3 '18 at 13:15
  • I realised how my question was fairly unintuitive, however one of the answers sufficed. Sorry for the inconvenience. – Exa Feb 4 '18 at 6:47

Short answer: No, and yes, respectively.

SQL databases do store data in some sort of order; whether by allocation, or (when there is a clustered index on a table) by the clustering key.

However, the process of executing a query does not guarantee that the data will be returned in the order in which is was stored. To guarantee the order of your data, you must use the ORDER BY clause in your SELECT statement.

Yes, you can create an auto-incrementing ID value in PostgreSQL. While this would normally be used to create a surrogate key to be used as the primary key, it appears it can be used to merely create a unique column, if you like.

The accepted answer to this question should give you what you need. You either use a sequence, or the SERIAL pseudo-datatype (which creates a sequence for you behind the scenes). Check the docs for your version for more details on sequences.

(I believe SERIAL will most likely start from 1, not 0 - you would probably have to set up the sequence yourself to get a specific starting point)

Note: This answer is for the tagged version of PostgreSQL, 9.4. As of version 10, PostgreSQL now has an identity column mechanism similar to that in Oracle or DB2, per the SQL:2003 & SQL:2008 standard (feature # T174). See the doc for GENERATED ... FOR IDENTITY here.

  • 1
    SERIAL is now supplanted by the identity column feature as of Postgres 10. See this article at 2nd Quadrant. See the doc for GENERATED … FOR IDENTITY. – Basil Bourque Feb 1 '18 at 7:43
  • @BasilBourque I'd initially left this out (the OP specified PostgreSQL 10), but have now added it the info in your comment for completeness. Thank you. – RDFozz Feb 1 '18 at 19:57
  • For more about identity column, see also my lengthy Answer to a similar Question. And the first part of my slideshow on 10 new things in Postgres 10. – Basil Bourque Feb 2 '18 at 0:05
  • I don't think they want a simple auto increment column. – ypercubeᵀᴹ Feb 3 '18 at 12:30

Not the answer you're looking for? Browse other questions tagged or ask your own question.