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)
example:
id | ai
---+---
0 | 0
0 | 1
0 | 2
1 | 0
1 | 1
1 | 2
1 | 3
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.
